Copy Paste loop in multiple tabs

**Mengo85** · 01-07-2020, 07:26 PM

Hi there,

I am using a loop to have a number put into tab "Batch size calcs" cell "AB2" and then copy the range "X15:AE15" into tab "Tabelle1" for 100 iterations. Then I do the same thing from 101 until 200 and paste it in the tab "Tabelle2". This all works fine but the problem is that in "Tabelle2" the first paste is in row 102 and not in 2. What is wrong with my code? What do I have to change? Please find my code below.

Thanks in advance for your help.

Dim lastRow As Long
Dim x As Integer

Worksheets("Tabelle1").Range("A2:I20000").Clear
Worksheets("Tabelle2").Range("A2:I20000").Clear

For x = 1 To 100

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Batch size calcs").Select
Range("AB2").Select
ActiveCell.FormulaR1C1 = x
Range("X15:AE15").Copy
Sheets("Tabelle1").Select
Range("A2").Select
Cells(lastRow + 1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Next

For x = 101 To 200

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Batch size calcs").Select
Range("AB2").Select
ActiveCell.FormulaR1C1 = x
Range("X15:AE15").Copy
Sheets("Tabelle2").Select
Range("A2").Select
Cells(lastRow + 1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Next

**mrbusto71** · 01-07-2020, 07:48 PM

Do you have an example workbook you could attach? At first glance my thought is that this is the culprit:

Please Login or Register  to view this content.

but without a workbook to test on I can't be sure.

**Mengo85** · 01-08-2020, 08:52 AM

Hi, mrbusto71,

thanks for your quick reply. Attached you will find an example workbook. The tab "Batch size calcs" has been changed to "calcs".

I hope this helps.