Solved How to copy all data from one sheet to another excel vba?

Microsoft Excel 2010 - complete product...
July 16, 2012 at 16:43:34
Specs: Windows 7 pro, i5 2500k 4.8 ghz / 8gb 2x4gb gskill ddr3 1600 cl9
What is the correct way to copy a whole sheet to another existing sheet in the same workbook?

i would like sheet4 to store my data and copy it to sheet1 each time i run my macro, sheet1 being the sheet the macro does its work on.

every which way i have tried to do this it either doesnt work, or copies 1000s of columns of blanks and excel says it has no resources.

the macro is going to destroy the data on sheet1 when it runs, but i will need to repeat this proceses a few times to get my output data the way i want. so i need it to refresh sheet1 with the unadultered data each time i run the macro.


See More: How to copy all data from one sheet to another excel vba?

Report •

#1
July 16, 2012 at 17:20:42
✔ Best Answer
Why not just copy the entire Sheet4 to a new sheet instead of copying the data? Then run the other macro against the new sheet.

This code will delete the new sheet each time and then copy Sheet4 to recreate it.

Sub CopySheet4()
'Don't display error if MyDataSheet doesn't exist
  On Error Resume Next
'Don't display confirmation message before deleting sheet
    Application.DisplayAlerts = False
'Delete the MyDataSheet
      Sheets("MyDataSheet").Delete
'Renable Alerts
    Application.DisplayAlerts = True
'Copy Sheet 4 and rename it
    Sheets("Sheet4").Copy Before:=Sheets(1)
    Sheets("Sheet4 (2)").Name = "MyDataSheet"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 16, 2012 at 20:14:21
sweet deal buddy, i was wondering if that was the best fix... i shoved it in at the top of my existing code, works fine as is.

all hail derby!


Report •

#3
July 17, 2012 at 13:07:25
BTW...if you have cells with more than 255 characters, anything above 255 won't get copied and since Alerts ae disabled, you won't be informed.

Just thought you might need to know that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question