PDA

View Full Version : Solved: Copy from one workbook to another



theSizz
11-27-2008, 08:10 PM
I have a workbook name Updates. It has a command button that has the following code attached to it.


Private Sub CommandButton1_Click()

Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\Sizzler\Inventory\WalkRoutes.xlsx"
Workbooks("walkroutes.xlsx").Activate
Sheets("WalkRoute").Activate
Range("A1:G205").Select
Range("A1:G205").Copy


There's more code but my problem lies in this part of the code.
What I am trying to do is copy the range A1 to G 205 in the workbook named "Walkroutes".
then paste it into the workbook named "Updates"

When I try to run the code it fails with a Runtime Error 1004 Select Method of Range Class Failed.

Can someone help me with the code to make it copy the cells in the workbook "Walkroutes" and paste them into the workbook "Updates" ?

Thank you for reading this and any help you can offer.

rbrhodes
11-28-2008, 12:08 AM
Hi theSizz,

Here's the simplistic way:


Private Sub CommandButton1_Click()

'Opens workbook
Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\Sizzler\Inventory\WalkRoutes.xlsx"
'//Not needed. Workbook is active when opened
'Workbooks("walkroutes.xlsx").Activate
'//Not really needed either
'Sheets("WalkRoute").Activate
'//Replace sheet name and range address
ActiveWorkbook.Sheets("Walkroute").Range("A1:G205").Copy Workbooks("Updates").Sheets("PutNameHere").Range("PutAddressHere")



and here's a better way


Option Explicit

Sub ImBetter()

Dim WSdest As Range
Dim WSsource As Worksheet

'Create object with workbook 'Updates'
Set WSdest = ActiveWorkbook.Sheets("P").Range("A1")

'Opens workbook
Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\Sizzler\Inventory\WalkRoutes.xlsx"

'Create object with worksheet 'walkroute'
Set WSsource = ActiveWorkbook.Sheets("walkroute")

'Copy with destination
WSsource.Range("A1:G205").Copy WSdest

'Destroy objects
Set WSdest = Nothing
Set WSsource = Nothing

End Sub

theSizz
11-28-2008, 12:30 AM
WOW !
Great............thanks so much.
They both work (naturally), I decided to use the sub ImBetter.
Nice and clean and descriptive.

Thank you so much !!!