Macro to copy a range

  • Thread starter Thread starter PCOR
  • Start date Start date
P

PCOR

I want to write a macro- just wish I knew how- that will copy from A2 to the
last column with data to the right
and the last row down to a NEW sheet
These 2 condition will varies from time to time.
There will NOT be any blank rows but there could be blank col
Can some onr help me with this
Thanks
 
The following macro copies from A2 to the last used cell on Sheet1, and
pastes onto Sheet2.

Sub CopyToNewSheet()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
ws1.Range(ws1.Range("A2"), ws1.Cells _
.SpecialCells(xlLastCell)).Copy _
Destination:=ws2.Range("A2")
End Sub
 
Many thanks.....BUT
It does work very well BUT I omitted one s m a l l point
The way you told me to do it copies the ENTIRE sheet.
What I now need is a macro that will copy all the cols(up to the last one
with data
and stop at the First BLANK row.
IE There is some data below that line that is NOT required.
I would appreciate it if you could ALSO solve that one for me
TIA
 
Can you pick out a row that's always filled--so you can find that last column

And same thing for the column?

If you can (I used Row 1 (maybe headers???) and column A to get the dimensions:

Sub CopyToNewSheet2()
Dim LastRow as long
dim LastCol as long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

with ws1
lastrow = .Range("a1").end(xldown).row
lastcol = .range("a1").end(xltoright).column

.range("A2",.cells(lastrow,lastcol)).copy _
Destination:=ws2.Range("A2")
end with

End Sub
 
Back
Top