| Author |
Message |
|
Dave
#1 / 7
|
 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 |
|
 |
|
Dave
#2 / 7
|
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 |
|
|
|
Dave Peterso
#3 / 7
|
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 |
|
|
|
Dave
#4 / 7
|
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 |
|
|
|
Dave Peterso
#5 / 7
|
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 |
|
|
|
Dave Peterso
#6 / 7
|
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 |
|
|
|
Dave
#7 / 7
|
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 |
|
| |
|
