Consilidate VBA and insert row

[tlindeman]

Good Morning,
I have three tabs: CB PAID CB Partial CB Unpaid. What I would like to do is copy all the data from all three and paste into a tab called consilidated. The number of rows in each tab will vary each month. So I would like to paste into a blank row in the consilidated tab. Then after that is complete, I would like to insert a row every 10,000 rows so I can create a pivot table. I am using excel 2010. Please supply the VBA Thank you in advance.

Tony

 

[John Davis]

Good Morning,
I have three tabs: CB PAID CB Partial CB Unpaid. What I would like to do is copy all the data from all three and paste into a tab called consilidated. The number of rows in each tab will vary each month. So I would like to paste into a blank row in the consilidated tab. Then after that is complete, I would like to insert a row every 10,000 rows so I can create a pivot table. I am using excel 2010. Please supply the VBA Thank you in advance.

Tony

Does this help in any way? Untested, use on a copy first.

 Sub tlindeman()
 Dim x As String
 Dim lr As Long
 Dim lr2 As Long
 Dim i As Long
 Dim i2 As Long
 
 lr = Cells(Rows.Count, 1).End(xlUp).Row
 lr2 = Sheets("Consolidated").Cells(Rows.Count, 1).End(xlUp).Row
 
 x = Array("CB PAID", "CB Partial", "CB Unpaid")
 
 For i = LBound(x) To UBound(x)
 
    Sheets(i).Range(Range("A2"), Range("A" & lr)).EntireRow.Copy Sheets("Consolidated").Range("A" & Rows.Count).End(3)(2)
 
 Next i
 
 With Sheets("Consolidated")
 
 For i2 = lr2 To 2 Step 10000
     
     Rows(i2).EntireRow.Insert shift:=xlUp
        
 Next i2
 
 End With
 
 End Sub

 

[tlindeman]

Sorry for the late reply, but that definatley helps, thank you for your help

 

[John Davis]

Sorry for the late reply, but that definatley helps, thank you for your help

You're welcome, thanks for the feedback.