simple copy and paste

Qroozn

Well-known Member
Joined
Mar 12, 2002
Messages
543
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
brett. the problem is that the record function records the final outcome.. not all the actions taken to achieve it.
 
Upvote 0
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

''''''''''''''''''''''''''''
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top