Copy Worksheet...how???

[Guest]

Hi,

Can anybody give the code to create a macro which will select the entire
contents of worksheet1 (except the headers which are on cells A2:G2) and
paste this onto the next available line on worksheet 2 ??

Easy....uh !!!!!!

thanks/regards
Anthony

 

[Dave Peterson]

Option Explicit
Sub testme02()

Dim RngToCopy As Range
Dim DestCell As Range

With Worksheets("sheet1")
Set RngToCopy = .Range("a3", .Cells.SpecialCells(xlCellTypeLastCell))
End With

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

End Sub

This assumes that you can find the next available row by looking in column A.

Another way if you can trust row A of sheet1 to be filled for each row.

Option Explicit
Sub testme02A()

Dim RngToCopy As Range
Dim DestCell As Range
Dim LastRow As Long

With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set RngToCopy = .Range("a3:G" & LastRow)
End With

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

End Sub

 

[Guest]

Thanks for your help Dave, but just another question

The macro sript works fine , however I want to just copy the cell 'VALUES'
not any formulas or shading or boxes etc. can this be done ??

Thanks again

 

[Dave Peterson]

This portion:

RngToCopy.Copy _
Destination:=DestCell

can be replaced with:

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues