Selecting a Range 
Author Message
 Selecting a Range

Hello all,

I was wondering how I could select a range based upon 2 different values in
column A.  What I want to do is copy this range and paste it into another
sheet.

Example:
Column A will have a bunch of values but lets say this.

Customer
A
B
C
D
E
Total

Customer
A
B
C
Total

What I need help with is find each range "Customer to Total" and copy it to
another WS in excel.  Any help is appreciated.



Thu, 24 Nov 2005 18:15:30 GMT  
 Selecting a Range
Ok so I have gotten how to select the range.

 Sub test1()
   ActiveSheet.Range("A:A").Select
   With Selection
     Set rng = Range(.Find("Customer:"), .Find("TOTAL"))
     Range(rng, rng.Offset(0, 15)).Select
   End With
 End Sub

How can I get this to loop through a sheet and copy this range a new
worksheet?  Any help would be GREATLY appreciated.

TIA.
Dave

Quote:
> Hello all,

> I was wondering how I could select a range based upon 2 different values
in
> column A.  What I want to do is copy this range and paste it into another
> sheet.

> Example:
> Column A will have a bunch of values but lets say this.

> Customer
> A
> B
> C
> D
> E
> Total

> Customer
> A
> B
> C
> Total

> What I need help with is find each range "Customer to Total" and copy it
to
> another WS in excel.  Any help is appreciated.



Thu, 24 Nov 2005 22:50:06 GMT  
 Selecting a Range
One way:

Option Explicit
Sub testme01()

    Dim TopCell As Range
    Dim BotCell As Range
    Dim searchRng As Range
    Dim destCell As Range
    Dim okToContinue As Boolean
    Dim firstTopCellAddr As String

    Dim wks As Worksheet
    Dim otherWks As Worksheet

    Set wks = Worksheets("sheet1")
    Set otherWks = Worksheets("sheet2")
    otherWks.UsedRange.Clear

    With wks
        Set searchRng = .Range("a:a")
        Set BotCell = .Cells(.Rows.Count, "A")
        okToContinue = True
        Do
            If okToContinue = False Then
                Exit Do
            End If
            With searchRng
                Set TopCell = .Find(what:="Customer", after:=BotCell, _
                                LookIn:=xlValues, lookat:=xlWhole, _
                                MatchCase:=False)

                If TopCell Is Nothing Then
                    okToContinue = False
                Else
                    If firstTopCellAddr = "" Then
                        firstTopCellAddr = TopCell.Address
                    Else
                        If TopCell.Address = firstTopCellAddr Then
                            okToContinue = False
                        End If
                    End If
                End If

                If okToContinue Then
                    Set BotCell = .Find(what:="Total", after:=TopCell, _
                                LookIn:=xlValues, lookat:=xlWhole, _
                                MatchCase:=False)

                    If BotCell Is Nothing Then
                        okToContinue = False
                    Else
                        If BotCell.Row < TopCell.Row Then
                            okToContinue = False
                        Else
                            With otherWks
                                Set destCell = _
                                       .Cells(.Rows.Count, "A").End(xlUp)
                                If IsEmpty(destCell) Then
                                    'do nothing
                                Else
                                    Set destCell = destCell.Offset(1, 0)
                                End If
                            End With
                            .Range(TopCell, BotCell).Resize(, 16).Copy _
                                Destination:=destCell
                        End If
                    End If
                End If
            End With
        Loop
    End With

End Sub

Quote:

> Hello all,

> I was wondering how I could select a range based upon 2 different values in
> column A.  What I want to do is copy this range and paste it into another
> sheet.

> Example:
> Column A will have a bunch of values but lets say this.

> Customer
> A
> B
> C
> D
> E
> Total

> Customer
> A
> B
> C
> Total

> What I need help with is find each range "Customer to Total" and copy it to
> another WS in excel.  Any help is appreciated.

--

Dave Peterson



Fri, 25 Nov 2005 03:53:50 GMT  
 Selecting a Range
Dave, thanks bud, but I am kinda unclear on this portion

  Set wks = Worksheets("sheet1")
    Set otherWks = Worksheets("sheet2")

What exactly does this do, as of now I am getting a subscript out of range
error.  I do not know exactly what the sheet name is.  The sheet is added
after I do a txt import.  The imported sheet will have about 100 invoices on
it and I was trying to select each range from "Customer" to "total" then
copy this range to an indivudual sheet for each company.  Can you help,
thanks for all your help as well.

Quote:
> One way:

> Option Explicit
> Sub testme01()

>     Dim TopCell As Range
>     Dim BotCell As Range
>     Dim searchRng As Range
>     Dim destCell As Range
>     Dim okToContinue As Boolean
>     Dim firstTopCellAddr As String

>     Dim wks As Worksheet
>     Dim otherWks As Worksheet

>     Set wks = Worksheets("sheet1")
>     Set otherWks = Worksheets("sheet2")
>     otherWks.UsedRange.Clear

>     With wks
>         Set searchRng = .Range("a:a")
>         Set BotCell = .Cells(.Rows.Count, "A")
>         okToContinue = True
>         Do
>             If okToContinue = False Then
>                 Exit Do
>             End If
>             With searchRng
>                 Set TopCell = .Find(what:="Customer", after:=BotCell, _
>                                 LookIn:=xlValues, lookat:=xlWhole, _
>                                 MatchCase:=False)

>                 If TopCell Is Nothing Then
>                     okToContinue = False
>                 Else
>                     If firstTopCellAddr = "" Then
>                         firstTopCellAddr = TopCell.Address
>                     Else
>                         If TopCell.Address = firstTopCellAddr Then
>                             okToContinue = False
>                         End If
>                     End If
>                 End If

>                 If okToContinue Then
>                     Set BotCell = .Find(what:="Total", after:=TopCell, _
>                                 LookIn:=xlValues, lookat:=xlWhole, _
>                                 MatchCase:=False)

>                     If BotCell Is Nothing Then
>                         okToContinue = False
>                     Else
>                         If BotCell.Row < TopCell.Row Then
>                             okToContinue = False
>                         Else
>                             With otherWks
>                                 Set destCell = _
>                                        .Cells(.Rows.Count, "A").End(xlUp)
>                                 If IsEmpty(destCell) Then
>                                     'do nothing
>                                 Else
>                                     Set destCell = destCell.Offset(1, 0)
>                                 End If
>                             End With
>                             .Range(TopCell, BotCell).Resize(, 16).Copy _
>                                 Destination:=destCell
>                         End If
>                     End If
>                 End If
>             End With
>         Loop
>     End With

> End Sub


> > Hello all,

> > I was wondering how I could select a range based upon 2 different values
in
> > column A.  What I want to do is copy this range and paste it into
another
> > sheet.

> > Example:
> > Column A will have a bunch of values but lets say this.

> > Customer
> > A
> > B
> > C
> > D
> > E
> > Total

> > Customer
> > A
> > B
> > C
> > Total

> > What I need help with is find each range "Customer to Total" and copy it
to
> > another WS in excel.  Any help is appreciated.

> --

> Dave Peterson




Fri, 25 Nov 2005 05:24:55 GMT  
 Selecting a Range
I figured that you had two worksheets--the original with the raw data and a
second that received the data.

Wks ("sheet1") represented the raw data worksheet and otherwks ("sheet2")
represented the worksheet that received the data.  I figured you'd change sheet1
and sheet2 to match your workbook.

But I'm still confused, though.

You import a text file.  How do you import it?  Via a macro or by File|open?  

Is the data on that worksheet associated with one customer or multiple
customers?  

If it's multiple customers, how do you know how to separate the data?

How do you determine which sheet gets each portion?

Or does each group just go to a brand new worksheet?

Quote:

> Dave, thanks bud, but I am kinda unclear on this portion

>   Set wks = Worksheets("sheet1")
>     Set otherWks = Worksheets("sheet2")

> What exactly does this do, as of now I am getting a subscript out of range
> error.  I do not know exactly what the sheet name is.  The sheet is added
> after I do a txt import.  The imported sheet will have about 100 invoices on
> it and I was trying to select each range from "Customer" to "total" then
> copy this range to an indivudual sheet for each company.  Can you help,
> thanks for all your help as well.


> > One way:

> > Option Explicit
> > Sub testme01()

> >     Dim TopCell As Range
> >     Dim BotCell As Range
> >     Dim searchRng As Range
> >     Dim destCell As Range
> >     Dim okToContinue As Boolean
> >     Dim firstTopCellAddr As String

> >     Dim wks As Worksheet
> >     Dim otherWks As Worksheet

> >     Set wks = Worksheets("sheet1")
> >     Set otherWks = Worksheets("sheet2")
> >     otherWks.UsedRange.Clear

> >     With wks
> >         Set searchRng = .Range("a:a")
> >         Set BotCell = .Cells(.Rows.Count, "A")
> >         okToContinue = True
> >         Do
> >             If okToContinue = False Then
> >                 Exit Do
> >             End If
> >             With searchRng
> >                 Set TopCell = .Find(what:="Customer", after:=BotCell, _
> >                                 LookIn:=xlValues, lookat:=xlWhole, _
> >                                 MatchCase:=False)

> >                 If TopCell Is Nothing Then
> >                     okToContinue = False
> >                 Else
> >                     If firstTopCellAddr = "" Then
> >                         firstTopCellAddr = TopCell.Address
> >                     Else
> >                         If TopCell.Address = firstTopCellAddr Then
> >                             okToContinue = False
> >                         End If
> >                     End If
> >                 End If

> >                 If okToContinue Then
> >                     Set BotCell = .Find(what:="Total", after:=TopCell, _
> >                                 LookIn:=xlValues, lookat:=xlWhole, _
> >                                 MatchCase:=False)

> >                     If BotCell Is Nothing Then
> >                         okToContinue = False
> >                     Else
> >                         If BotCell.Row < TopCell.Row Then
> >                             okToContinue = False
> >                         Else
> >                             With otherWks
> >                                 Set destCell = _
> >                                        .Cells(.Rows.Count, "A").End(xlUp)
> >                                 If IsEmpty(destCell) Then
> >                                     'do nothing
> >                                 Else
> >                                     Set destCell = destCell.Offset(1, 0)
> >                                 End If
> >                             End With
> >                             .Range(TopCell, BotCell).Resize(, 16).Copy _
> >                                 Destination:=destCell
> >                         End If
> >                     End If
> >                 End If
> >             End With
> >         Loop
> >     End With

> > End Sub


> > > Hello all,

> > > I was wondering how I could select a range based upon 2 different values
> in
> > > column A.  What I want to do is copy this range and paste it into
> another
> > > sheet.

> > > Example:
> > > Column A will have a bunch of values but lets say this.

> > > Customer
> > > A
> > > B
> > > C
> > > D
> > > E
> > > Total

> > > Customer
> > > A
> > > B
> > > C
> > > Total

> > > What I need help with is find each range "Customer to Total" and copy it
> to
> > > another WS in excel.  Any help is appreciated.

> > --

> > Dave Peterson


--

Dave Peterson



Sat, 26 Nov 2005 01:39:16 GMT  
 Selecting a Range
In a private message, Dave said that he just wants a new worksheet for each
customer grouping.

Dave, I think that this minor modification of the original suggestion works ok.
(Watch out for Customer: vs. customer.  (I wasn't sure it should/should not
include the colon.)

The biggest change was how the destcell got assigned:
Instead of a bunch of looking for the last row of a specific worksheet and
pasting there, I just add a new worksheet and use A1.

Set destCell = Worksheets.Add.Range("a1")

(I dumped some of the variables that didn't matter, too.)

And in your code (in the private email), you had .pastevalues and
.pasteformats.  I'm sure you'll change that to match, too.

Option Explicit
Sub testme01()

    Dim TopCell As Range
    Dim BotCell As Range
    Dim searchRng As Range
    Dim destCell As Range
    Dim okToContinue As Boolean
    Dim firstTopCellAddr As String

    Dim wks As Worksheet

    Set wks = Worksheets("sheet1")

    With wks
        Set searchRng = .Range("a:a")
        Set BotCell = .Cells(.Rows.Count, "A")
        okToContinue = True
        Do
            If okToContinue = False Then
                Exit Do
            End If
            With searchRng
                Set TopCell = .Find(what:="Customer:", after:=BotCell, _
                                LookIn:=xlValues, lookat:=xlWhole, _
                                MatchCase:=False, searchdirection:=xlNext)

                If TopCell Is Nothing Then
                    okToContinue = False
                Else
                    If firstTopCellAddr = "" Then
                        firstTopCellAddr = TopCell.Address
                    Else
                        If TopCell.Address = firstTopCellAddr Then
                            okToContinue = False
                        End If
                    End If
                End If

                If okToContinue Then
                    Set BotCell = .Find(what:="Total", after:=TopCell, _
                                LookIn:=xlValues, lookat:=xlWhole, _
                                MatchCase:=False)

                    If BotCell Is Nothing Then
                        okToContinue = False
                    Else
                        If BotCell.Row < TopCell.Row Then
                            okToContinue = False
                        Else
                            Set destCell = Worksheets.Add.Range("a1")
                            .Range(TopCell, BotCell).Resize(, 16).Copy _
                                Destination:=destCell
                        End If
                    End If
                End If
            End With
        Loop
    End With

End Sub

--

Dave Peterson



Mon, 28 Nov 2005 02:41:25 GMT  
 Selecting a Range
Dave Peterson,

Thank you so much for taking the time out to help.  I GREATLY appreciate it.
This forum is fantastic and I love to see professionals and experts helping
others out.  It is truely wonderful.  Once again.  Thanks for all your time.

Dave. C

Quote:
> In a private message, Dave said that he just wants a new worksheet for
each
> customer grouping.

> Dave, I think that this minor modification of the original suggestion
works ok.
> (Watch out for Customer: vs. customer.  (I wasn't sure it should/should
not
> include the colon.)

> The biggest change was how the destcell got assigned:
> Instead of a bunch of looking for the last row of a specific worksheet and
> pasting there, I just add a new worksheet and use A1.

> Set destCell = Worksheets.Add.Range("a1")

> (I dumped some of the variables that didn't matter, too.)

> And in your code (in the private email), you had .pastevalues and
> .pasteformats.  I'm sure you'll change that to match, too.

> Option Explicit
> Sub testme01()

>     Dim TopCell As Range
>     Dim BotCell As Range
>     Dim searchRng As Range
>     Dim destCell As Range
>     Dim okToContinue As Boolean
>     Dim firstTopCellAddr As String

>     Dim wks As Worksheet

>     Set wks = Worksheets("sheet1")

>     With wks
>         Set searchRng = .Range("a:a")
>         Set BotCell = .Cells(.Rows.Count, "A")
>         okToContinue = True
>         Do
>             If okToContinue = False Then
>                 Exit Do
>             End If
>             With searchRng
>                 Set TopCell = .Find(what:="Customer:", after:=BotCell, _
>                                 LookIn:=xlValues, lookat:=xlWhole, _
>                                 MatchCase:=False, searchdirection:=xlNext)

>                 If TopCell Is Nothing Then
>                     okToContinue = False
>                 Else
>                     If firstTopCellAddr = "" Then
>                         firstTopCellAddr = TopCell.Address
>                     Else
>                         If TopCell.Address = firstTopCellAddr Then
>                             okToContinue = False
>                         End If
>                     End If
>                 End If

>                 If okToContinue Then
>                     Set BotCell = .Find(what:="Total", after:=TopCell, _
>                                 LookIn:=xlValues, lookat:=xlWhole, _
>                                 MatchCase:=False)

>                     If BotCell Is Nothing Then
>                         okToContinue = False
>                     Else
>                         If BotCell.Row < TopCell.Row Then
>                             okToContinue = False
>                         Else
>                             Set destCell = Worksheets.Add.Range("a1")
>                             .Range(TopCell, BotCell).Resize(, 16).Copy _
>                                 Destination:=destCell
>                         End If
>                     End If
>                 End If
>             End With
>         Loop
>     End With

> End Sub

> --

> Dave Peterson




Mon, 28 Nov 2005 13:24:54 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Select a Range, deselect bottom row of range

2. prompting user to select a range and using this range

3. Selecting a Range of Cells, with Unknown Rows in Range

4. How to select a range in a macro - range size differs with each sheet

5. MACRO to Select a Range (end of range)

6. Why I need to select a sheet before selecting a range

7. Cannot select print range

8. Selecting a range

9. How to select a range of rows in a worksheet with VBA

10. How to select a range from selection to the before last cell


 
Powered by phpBB® Forum Software © phpBB Group