Home |
Search |
Today's Posts |
#1
|
|||
|
|||
A little macro help?
I would like to use a macro to copy and repeat lines 'X' times on a new
page. To find out how many times each row is to be copied, the number is provided in the last column ('C' in this case) of a spreadsheet. Sample data for start point: Lastname First Name Qty Smith John 3 Hank Aaron 5 Result required on new sheet in same spreadsheet: Lastname First Name Smith John Smith John Smith John Hank Aaron Hank Aaron Hank Aaron Hank Aaron Hank Aaron Can someone provide some macro code that will create the number of required rows on a new sheet called 'list' within the same spreadsheet? TIA, Alan |
#2
|
|||
|
|||
Alan,
Sub CopyRecords() Dim i As Long Dim SourceRow As Long, DestRow As Long SourceRow = 2 ' starting row DestRow = 2 ' destination row Do While Cells(SourceRow, 1) < "" For i = 1 To Cells(SourceRow, 3) ActiveSheet.Cells(SourceRow, 1).Resize(1, 2).Copy Destination:=Sheets("List").Cells(DestRow, 1) DestRow = DestRow + 1 Next i SourceRow = SourceRow + 1 Loop End Sub The source sheet must be the active sheet. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Alan" wrote in message 42... I would like to use a macro to copy and repeat lines 'X' times on a new page. To find out how many times each row is to be copied, the number is provided in the last column ('C' in this case) of a spreadsheet. Sample data for start point: Lastname First Name Qty Smith John 3 Hank Aaron 5 Result required on new sheet in same spreadsheet: Lastname First Name Smith John Smith John Smith John Hank Aaron Hank Aaron Hank Aaron Hank Aaron Hank Aaron Can someone provide some macro code that will create the number of required rows on a new sheet called 'list' within the same spreadsheet? TIA, Alan |
#3
|
|||
|
|||
Earl Kiosterud wrote:
Many thanks Earl, it's working for me. One more question if I may... If the sheet "list" doesn't exist, how can I add the sheet "list"? The command Sheets.add doesn't accept the name list. I also can't assume that the next sheet added is going to be called "sheet1". Any ideas? Thx, Alan Alan, Sub CopyRecords() Dim i As Long Dim SourceRow As Long, DestRow As Long SourceRow = 2 ' starting row DestRow = 2 ' destination row Do While Cells(SourceRow, 1) < "" For i = 1 To Cells(SourceRow, 3) ActiveSheet.Cells(SourceRow, 1).Resize(1, 2).Copy Destination:=Sheets("List").Cells(DestRow, 1) DestRow = DestRow + 1 Next i SourceRow = SourceRow + 1 Loop End Sub The source sheet must be the active sheet. |
#4
|
|||
|
|||
Alan,
Sub CopyRecords() Dim i As Long Dim SourceRow As Long, DestRow As Long Dim Wks As Worksheet Dim HaveSheetList As Boolean SourceRow = 2 ' starting row DestRow = 2 ' destination row For Each Wks In ActiveWorkbook.Sheets ' search for sheet named "List" If Wks.Name = "List" Then ' found one HaveSheetList = True ' set flag Exit For ' get out End If Next Wks If Not HaveSheetList Then ' don't have "List" Sheets.Add ' add it ActiveSheet.Name = "List" ' name it End If Do While Sheets("Master").Cells(SourceRow, 1) < "" For i = 1 To Sheets("Master").Cells(SourceRow, 3) Sheets("Master").Cells(SourceRow, 1).Resize(1, 2).Copy Destination:=Sheets("List").Cells(DestRow, 1) DestRow = DestRow + 1 Next i SourceRow = SourceRow + 1 Loop End Sub The source sheet is called "Master" now. You can change occurences of Sheets("Master") to reflect the name of your source sheet. Note that this will start copying records to row 2 even if "List" already exists and has stuff in it. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Alan" wrote in message ... Earl Kiosterud wrote: Many thanks Earl, it's working for me. One more question if I may... If the sheet "list" doesn't exist, how can I add the sheet "list"? The command Sheets.add doesn't accept the name list. I also can't assume that the next sheet added is going to be called "sheet1". Any ideas? Thx, Alan Alan, Sub CopyRecords() Dim i As Long Dim SourceRow As Long, DestRow As Long SourceRow = 2 ' starting row DestRow = 2 ' destination row Do While Cells(SourceRow, 1) < "" For i = 1 To Cells(SourceRow, 3) ActiveSheet.Cells(SourceRow, 1).Resize(1, 2).Copy Destination:=Sheets("List").Cells(DestRow, 1) DestRow = DestRow + 1 Next i SourceRow = SourceRow + 1 Loop End Sub The source sheet must be the active sheet. |
#5
|
|||
|
|||
Works great! Thanks so much for your help!
Alan Earl Kiosterud wrote: Alan, Sub CopyRecords() Dim i As Long Dim SourceRow As Long, DestRow As Long Dim Wks As Worksheet Dim HaveSheetList As Boolean SourceRow = 2 ' starting row DestRow = 2 ' destination row For Each Wks In ActiveWorkbook.Sheets ' search for sheet named "List" If Wks.Name = "List" Then ' found one HaveSheetList = True ' set flag Exit For ' get out End If Next Wks If Not HaveSheetList Then ' don't have "List" Sheets.Add ' add it ActiveSheet.Name = "List" ' name it End If Do While Sheets("Master").Cells(SourceRow, 1) < "" For i = 1 To Sheets("Master").Cells(SourceRow, 3) Sheets("Master").Cells(SourceRow, 1).Resize(1, 2).Copy Destination:=Sheets("List").Cells(DestRow, 1) DestRow = DestRow + 1 Next i SourceRow = SourceRow + 1 Loop End Sub The source sheet is called "Master" now. You can change occurences of Sheets("Master") to reflect the name of your source sheet. Note that this will start copying records to row 2 even if "List" already exists and has stuff in it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date macro | Excel Discussion (Misc queries) | |||
Can't get simple macro to run | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro Formula revision? | Excel Worksheet Functions |