Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Select
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Select
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) "mb" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
I can't select a range in VBA | Excel Discussion (Misc queries) | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Select Range as far as..... | Excel Programming | |||
Select a range | Excel Programming |