Macro to Loop Through a Repetitive Task?

[Smartkid]

Hello all,

I'm hoping to get some help preparing a Macro that will ease a relatively repetitive task that I perform. Basically, I have a template in Excel that populates a table based on input data. (Sheet 'Template')

Usually I take the following steps:
1. Download input data (Sheet 'Input')
2. Copy the first row of input data into the template to populate the table (Sheet 'Template')
3. Copy the table to a new sheet (Sheet 'Output')
4. Copy the second row of input data into the template to populate a new table (Sheet 'Template')
5. Copy the table below the first one (Sheet 'Output')
6. Continue this process until end of input data

I feel like a VBA Macro would make my life a lot easier. I wish I could just dump the input data on Sheet 'Input' and press a GO button, the Sheet 'Output' would automatically populate. Is this possible?

I've attached a mock workbook for reference. Basically my template is on rows 3-16 of the Sheet 'Template'. I copy my input data into Sheet 'Input' from rows C1:JXX (The number of rows of input data varies.) For each row of input data, I copy C:J and paste on the Sheet 'Template' C3:J3. I then copy rows 3-16 from Sheet 'Template' onto Sheet 'Output.' Rinse and repeat.

Any help to make my life easier would be greatly appeciated. Sometimes my input data exceeds 100 rows so it gets quite time consuming.

Thanks in advance

[Aussiebear]

Have you tried the macro recorder to see what is required?

[Smartkid]

Hi,

I have tried the Recorder but I don't think it would work because the task is dependant upon the number of input rows and the output needs to be pasted in succession.

[Aussiebear]

Can you post the results of the macro recorder?

[Smartkid]

This is the output that I get from the Macro recorder. It works for the first line of input, so I think it just needs to be amended somehow in a loop.

[VBA]Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Input").Select
Range("C3:J3").Select
Selection.Copy
Sheets("Template").Select
Range("C3").Select
ActiveSheet.Paste
Rows("3:16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Output").Select
Rows("3:3").Select
ActiveSheet.Paste
End Sub

[/VBA]
I'm pretty bad with modifying Macros but I guess I want to add a Loop that will continue to copy the input lines and paste them in succession on the output tab.

[Smartkid]

I don't really know VBA code, but this is what I think would work. (The highlighed lines are things that I put into the original Macro, but they are obviously not in VBA code and need to be translated.)

Also, would I be able to create a button on the "Input" Sheet in Cell J1 that I can click to activate the Macro?

[VBA]Sub Macro1()
' Macro1 Macro

Sheets("Input").Select

Variable ICounter = 3
Variable OCounter = 3

While Row.ICounter Does Not Equal BLANK

Range("C(ICounter):J(ICounter)").Select
Selection.Copy
Sheets("Template").Select
Range("C3").Select
ActiveSheet.Paste
Rows("3:16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Output").Select
Rows("OCounter:OCounter").Select
ActiveSheet.Paste

OCounter = OCounter + 14

End Sub[/VBA]

[Smartkid]

I've had a play around and have gotten to the following, but it isn't working correctly. Can someone please help out?

[VBA]Sub Generate()
Dim InputRow As Long
Dim OutputRow As Long
Dim LastRow As Long
Dim NextRow As Long
Dim Counter As Long

Set Target = Worksheets("Output")
Set Template = Worksheets("Template")
With Worksheets("Input")

Counter = 1
InputRow = 3
OutputRow = 3
Target.Range(Target.Range("A3"), Target.Range("Y3").End(xlDown)).Delete Shift:=xlUp 'Selects target output cells
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
On Error Resume Next
Do While InputRow <= LastRow

.Cells(InputRow, "A").Value = Counter

For Col = 1 To 8
Template.Cells(3, Col) = .Cells(InputRow, Col)
Next Col

Template.Range("A3", "X16").Copy
Target.Range(Target.Cells(OutputRow, "A"), Target.Cells(OutputRow, "X")).Select
Target.Paste

InputRow = InputRow + 1
OutputRow = OutputRow + 14
Counter = Counter + 1

Loop

End With

End Sub[/VBA]

[Smartkid]

I think I almost have it, but I need to change the following line:
Template.Range("A3:X16").Copy Target.Cells(OutputRow, "A")
so that it copys and pastes values+formats but not formulas.

[VBA]Sub Generate()

Dim InputRow As Long
Dim OutputRow As Long
Dim LastRow As Long
Dim NextRow As Long
Dim Counter As Long

Set Target = Worksheets("Output")

Set Template = Worksheets("Template")

Set Source = Worksheets("Input")

Counter = 1
InputRow = 3
OutputRow = 3

Target.Range(Target.Range("A3"), Target.Range("Y3").End(xlDown)).Delete Shift:=xlUp 'Selects target output cells

LastRow = Source.Cells(Source.Rows.Count, "C").End(xlUp).Row
On Error Resume Next

Do While InputRow <= LastRow

Source.Cells(InputRow, "A").Value = Counter

For Col = 1 To 8
Template.Cells(3, Col) = Source.Cells(InputRow, Col)
Next Col

Template.Range("A3:X16").Copy Target.Cells(OutputRow, "A")
InputRow = InputRow + 1
OutputRow = OutputRow + 14
Counter = Counter + 1

Loop

End Sub[/VBA]