simple copy and paste

[Qroozn]

ok. this is a stupid request.

I have two workbooks.
I want a macro to copy all the cells of worksheet1(wbook1) to worksheet2 in the second Wbook.

what is the simplest way to do this using VBA code

TIA

[brettvba]

record it!

[Qroozn]

brett. the problem is that the record function records the final outcome.. not all the actions taken to achieve it.

[Tom Urtis]

Here in agonizing detail are the actions you could take. This code is definitely the long way to the solution, but you did say you wanted to see "all the actions taken to achieve it."

This code works just fine but it's darned inefficient. In real applications you could do the same thing with just a few lines of code. However, some new readers may find this code useful if they are getting accustomed to VBA, especially for the first time.

Purists look the other way please.

'''''''''''''''''''

'Prepare Excel for the import:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

'First, see if you have the Workbook1 file open; if not then open it:
On Error GoTo b:
Windows("Workbook1.xls").Activate
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:YourFilePath"
Workbooks.Open Filename:="C: YourFilePath Workbook1.xls"
'This will open the Workbook1 file
c:

'Now when we get to c: the file is open either way so we activate it:
Windows("Workbook1.xls").Activate
'Then select your worksheet...
Sheets("Sheet1").Select
'...then select all the cells in the sheet...
Cells.Select
'...and then copy the entire sheet's range onto the clipboard.
Selection.Copy

'Next, re-activate the Workbook2 file:
Windows("Workbook2.xls").Activate
'Then select the destination worksheet...
Sheets("Sheet2").Select
'...then select the entire sheet's range...
Cells.Select
'...and Paste Special Values from Workbook1's Sheet1 into Workbook2’s Sheet2:
Selection.PasteSpecial Paste:=xlValues
‘Go to A1, it seems like a nice place to set up shop
Range("A1").Select
‘Clear the clipboard
Application.CutCopyMode = False

'Close Workbook1
Windows("Workbook1.xls").Activate
ActiveWindow.Close

‘Return Excel to its original condition
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

''''''''''''''''''''''''''''

[Tom Urtis]

Couldn't resist, here's that code condensed into 3 lines, in case you wanted to keep it simple. The only thing is, it does not select all cells as you specified (your system would likely run out of memory if you tried A1:IV65536), but you probably don't have all 16,777,216 cells populated. Adjust the range as needed.

Workbooks.Open Filename:="C:YourFilePathWorkbook1.xls"
ThisWorkbook.Worksheets("Sheet2").Range("A1:Z50").Value = _
Workbooks("Workbook1.xls").Worksheets("Sheet1").Range("A1:Z50").Value
Workbooks("Workbook1.xls").Close

HTH