Compiling data from worksheets into one

January 17, 2012 at 00:23:43
Specs: Windows XP
I am trying to compile data from worksheets (same structure) to one sheet. I am using the following code. The issue is, when the code runs it does not copy data one below other, instead it replaces the earlier pasted data. Need help.

Sub Macro3()
' Macro3 Macro
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Compiled"
LastRow1 = DestSh.Range("A" & Rows.Count).End(xlUp).Row + 1
LastRow = sh.Range("A" & Rows.Count).End(xlUp).Row
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
sh.Range("A9:A" & LastRow).EntireRow.Copy Destination:=DestSh.Range("A1:A" & LastRow1)
End If
Next sh

End Sub

See More: Compiling data from worksheets into one

Report •

January 18, 2012 at 17:51:17
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post code in this forum.

The problem with your code is that you set the LastRow1 variable at the beginning of you code and never change it. The line that sets the LastRow1 variable needs to be inside the loop so that it gets updated after every Paste operation.

In fact, I surprised that you aren't having the same problem with the LastRow variable, unless the LastRow is the same for all sheets.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 18, 2012 at 21:49:08
This is my third question on this forum, but unlike previously I could not use the proper method to paste the code, as it gives me an error (Error on Page) when I click "Pre" button to paste my code. I am really sorry for that. (If you know why this is happening, I would change setting accordingly)

As suggested, I did paste the codes inside the loop, but still the problem exists. It overwrites the earlier data, and replaces with new data. Whereas I want it to paste the data below earlier pasted data.
Thanks again.

Report •

January 23, 2012 at 21:18:40
still not able to crack the problem with this code, need your help

Report •

Related Solutions

Ask Question