I am copying and pasting over information with blanks in between. I also
need to copy over the value of a calculation which needs to be left at the
end of the information. This is the formula i am using. Can anyone help?
Sub Macro1()

Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")


sh1.Range("A1:A200").SpecialCells( _
xlConstants).EntireRow.Copy Destination:=sh2.Cells( _
Rows.Count, 1).End(xlUp)(2)

Range("A200:D200").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A5").Select
Sheets("Sheet1").Select
Range("A1").Select
End Sub