Copy-paste VBA

[Excelnoobisme]

Hi,

i have 2 tabs, 'working' and 'data'.

'working' tab is where i will do my daily work, after done, i will paste to 'data'.
Can i have a marco say, 1st day working i have 200 rows, it will paste to 'data' and on the 2nd day if i have 150 rows, it will go to 'data' and paste starting from row 201...and so on.

 

[JackDanIce]

Try:

Sub CopyandMove()
Dim i As Long
With Sheets(1)
   i = .Range("A" & Rows.Count).End(xlUp).Row
   .Range("A1:J" & i).Copy
'change J to whatever letter your last column is in
End With
With Sheets(2)
    If IsEmpty(.Range("A1")) Then
        .Range("A1").PasteSpecial Paste:=xlValues
    Else
        .Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues
    End If
End With
End Sub

 

[Excelnoobisme]

Not working, it will overwrite day 1 data when i try paste day 2 data

 

[daniels012]

A1 must be empty.
Where does your data start on page 2?
Do you have a title line on row1 or does your data indeed start in A1?

Michael

 

[JackDanIce]

I didn't change it from my test code to your requirements. I'm assuming you want row 1 copied over. If this is a header row, then change the red 1 below to 2 (or whatever row your data starts on in working)

Try:

Sub CopyandMove()
Dim i As Long
With Sheets("working")
   i = .Range("A" & Rows.Count).End(xlUp).Row
   .Range("A1:J" & i).Copy
'change J to whatever letter your last column is in
End With
With Sheets("data")
    If IsEmpty(.Range("A1")) Then
       .Range("A1").PasteSpecial Paste:=xlValues
    Else
        .Range("A" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial Paste:=xlValues
    End If
End With
End Sub

 

[Excelnoobisme]

ok, thanks alot Jack

 

[Excelnoobisme]

Hi, if i want to safeguard the same data from the same day from pasting twice on a same day how can i do that? My column A is date.