Range Select

  • Thread starter Thread starter mb
  • Start date Start date
M

mb

Good morning,

I have a report which lists five different customer's data - each ending
with the phrase "Weekly Turns". Customer data is preceeded by the actual
customer name.

I am trying to find a method to select from the Customer name to the Weekly
Turn row, copy all of the data and paste into a new worksheet. The macro I
used is focused on the specific row number - which will not work, as the
amount of data changes on a regular basis.

Thanks,
MB
 
Afternoon,

Here's a simple macro.

Few assumptions.

The names are in column A
The first name is in Row 1
There are blank rows between the final row and the next name

I have renamed the sheets with the customer's name

Sub ConvertCustomers()
Dim iLastRow As Long
Dim iStart As Long
Dim iEnd As Long
Dim oWS As Worksheet

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iStart = 1
iEnd = 1

With ActiveSheet
For iStart = 1 To iLastRow
iEnd = iStart
Do
iEnd = iEnd + 1
Loop Until Cells(iEnd, "A").Value = "Weekly Turns" Or iEnd >
iLastRow
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name =
..Cells(iStart, "A").Value
.Range(.Cells(iStart, "A"), .Cells(iEnd, "A")).EntireRow.Copy _
Destination:=Worksheets(.Cells(iStart,
"A").Value).Range("A1")
iStart = iEnd + 1
.Activate
Next iStart
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top