Need macro to move cell contents from one sheet to another

[Bern Notice]

I need a macro to move the contents of 5 columns (A-E) on one sheet to the
bottom of a set of data on another page. Variables to deal with:
1) There will be a variable amount of rows on the first sheet,
2) the end of the data set on the second page will move down each time new
data is added,
3) There are sum() formulas at the bottom of the second page in columns
(A-H) to total the data above - note there are more columns on second page.
I need the contents of the first page to be added above the "total" row and
have the sum() formulas move down accordingly in columns (A-H).
- Note: I have an empty row between the data on page 2 and the sum() row.

I keep getting an error message that says the paste area has to be the same
size as the copy area.

Any thoughts?

 

[Chip Pearson]

Use code like the following. Change the lines marked with <<< to the
appropriate values.


Sub CopyData()
Dim SourceFirstRow As Long
Dim SourceLastRow As Long
Dim DestLastRow As Long
Dim SourceWS As Worksheet
Dim DestWS As Worksheet
Dim CopyRange As Range

SourceFirstRow = 1 '<<< CHANGE AS NEEDED
Set SourceWS = Worksheets("Sheet1") '<<< CHANGE AS NEEDED
Set DestWS = Worksheets("Sheet2") '<<< CHANGE AS NEEDED
With SourceWS
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set CopyRange = .Range(.Cells(SourceFirstRow, "A"), _
.Cells(SourceLastRow, "H"))
End With
With DestWS
DestLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(DestLastRow).Resize(CopyRange.Rows.Count).Insert
CopyRange.Copy Destination:=.Cells(DestLastRow, "A")
End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)