0

This code is supposed to transfer pivot table data from one workbook to other, works fine for a few loops, however, the row increment which is being tried isn't catching up after a few loops. I'm not sure why it messes up after running a few loops!

Sub copypivotdata()

Dim x As Workbook
Dim y As Workbook
Dim n As String, ws As Worksheet
Dim pvt As Range
Dim i, i2 As Integer



Set x = Workbooks("WorkbookA.xlsm")
Set y = Workbooks("WorkbookB.xlsm")
i = 2

'Now, transfer values from x to y:

x.Activate

For Each ws In ActiveWorkbook.Worksheets
'With ActiveWorkbook.ActiveSheet
If ws.Name <> "Main Sheet" And ws.ChartObjects().Count <> 0 Then
    ws.Activate
Else:
    GoTo nextws
End If

    ws.Activate

    Range("A1048576").Select
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToRight)).Select

    Set pvt = Selection
    i2 = Selection.Rows.Count
    'MsgBox i2

    y.Activate 'activate other workbook 
    'With
    y.Sheets("Pivot data").Range("C" & i, "D" & i2 + i) = pvt.Value

    i = i2 + 2
    i2 = 0


nextws: Next ws

y.Activate

End Sub
Yusuf
  • 31
  • 10
  • See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba), and [this one](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) too. – BigBen Apr 17 '20 at 13:26
  • Try adding 'As Integer' behind i (even though I can't think of why this should matter, not defining it might set the type as variant) – jonadv Apr 17 '20 at 13:27
  • Exactly my thought @l3ob, I removed it from Dim.. still same issue.. I found out, the screw up occurs on 3rd loop! but cant figure out as in WHY?! – Yusuf Apr 17 '20 at 13:37
  • GOT IT, "i = i2 + 2" is incorrect. Should be "i = i+i2 ).. – Yusuf Apr 17 '20 at 13:43

1 Answers1

0

The loop logic was incorrect.. Just realized. "i = i2 + 2" is incorrect. Should be i = i + i2

Yusuf
  • 31
  • 10