• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy & paste sheet1 data to sheet2 and delete all the data in sheet1

Code: 
Option Explicit

Sub cpynpste()
    Dim w1 As Worksheet, w2 As Worksheet
    Dim lr As Long, lr2 As Long
    Set w1 = Sheets("Sheet1"): Set w2 = Sheets("Sheet2")
    lr = w1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = w2.Range("A" & Rows.Count).End(xlUp).Row
    w1.Range("A2:G" & lr).Copy
    w2.Range("A" & lr2 + 1).PasteSpecial (xlPasteValues)
    w1.Range("A2:G" & lr).ClearContents
    MsgBox "Action Complete"
End Sub
 

But it was not you wrote in first post as data is without headers !
As you can easily mod the code - at beginner level - and as you already
got a sample in another of your own threads and
as attaching a result workbook in first post is so obvious …
​
 
At very beginner level : as my code as very few statements,
place the text cursor on one and hit F1 key and read : easy way to learn !
Redo the same for next statement …
And if you won't understand my code, so use the first one of Alan
as you can clearly read within it what range is copied …
​
 
I think there is some misunderstanding regarding this problem
1)delete sheet2 data completely ( dont keep headers also, make sheet2 blank sheet)
2)copy sheet1 complete data including headers and paste to sheet2
3) delete all the data of sheet1 including headers (make sheet1 a blank sheet)

Previous code is of shifting of sheet1 data to sheet2 but i don't have to do that
 
I think you misread my previous post …

Tip for beginner :
activate the Macro Recorder and operate manually,
you will get your own code base !
Then you could compare with previous codes …
​
 
I tried but i am getting error
I am new to vba & u r experts in vba
I don't know where i made mistake, can u plz look into this problem and provide me the code
 
Have you tried the code I supplied in post #2.


How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button
 
Yes Alan sir that code is transferring data from sheet1 to sheet2
But i am looking for the code that will
1)delete sheet2 data completely ( dont keep headers also, make sheet2 blank sheet)
2)copy sheet1 complete data including headers and paste to sheet2
3) delete all the data of sheet1 including headers (make sheet1 a blank sheet)
 
ok. Got it!

Code: 
Option Explicit

Sub abc()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1"): Set s2 = Sheets("Sheet2")
    Application.ScreenUpdating = False
    s2.Range("A1").CurrentRegion.Delete
    s1.Range("A1").CurrentRegion.Copy
    s2.Range("A1").PasteSpecial xlPasteValues
    s1.Range("A1").CurrentRegion.Delete
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "completed"

End Sub
 
You must log in or register to reply here.
Back
Top