Copy from one workbook to another-command button

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.

Please stick to the forum rules and link to crossposts.

http://www.mrexcel.com/forum/showthread.php?t=356002

I'm sorry............how do I go about doing that?

Here you go,

Sub CopyRangeToAnotherSheet()

'replace "Sheets2" with the target sheet
'the final range("a1").Select is just to unselect the selection on sheet2
'the code then goes back to "Walkroute" and deselects the selection and then goes back to sheet2

    'select and activate the sheet named "Walkroute"
    Sheets("Walkroute").Activate
    'select the range in this case A1:G205 and copy to clipboard
    Range("A1:G205").Select
    Selection.Copy
    'select the sheet named Sheet2 and select the cell A1 and paste
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    'then select the cell A1 do deselect the selection
    Range("A1").Select
    'go back to original sheet and select the cell A1
    Sheets("walkroute").Select
    Range("A1").Select
    'then deselect the selection, to see what will happen just comment the last line of code out.
    Application.CutCopyMode = False
End Sub

Hope this helps, you just need to replace the code Sheet2 to the name of your second sheet.

That's not what I'm trying to do. If I understand your code that will copy from a sheet in the workbook to another sheet in the same workbook.
What I want to do is copy cells from a sheet in "Workbook A" to cells in a sheet in "Workbook B"
In other words What I am trying to do is copy the cells in range A1 to G 205 in the workbook named "Walkroutes".
and then paste it into a different sheet in a different workbook named "Updates"

Sub CopyRangeToAnotherSheet()



'replace "Sheets2" with the target sheet
'the final range("a1").Select is just to unselect the selection on sheet2
'the code then goes back to "Walkroute" and deselects the selection and then goes back to sheet2

    'select and activate the sheet named "Walkroute"
    Sheets("Walkroute").Activate
    'select the range in this case A1:G205 and copy to clipboard
    Range("A1:G205").Select
    Selection.Copy
    
    'after copy open the book named book2
    
    Workbooks.Open Filename:="C:\Documents and Settings\1\Desktop\Book2.xls"

    'select the sheet named updates and select the cell A1 and paste
    Sheets("Updates").Select
    Range("A1").Select
    ActiveSheet.Paste
    'then select the cell A1 do deselect the selection
    Range("A1").Select
   'then deselect the selection, to see what will happen just comment the last line of code out.
    Application.CutCopyMode = False
End Sub

Try that.

you need to make sure that the work book2 is closed if you want to perform an update again. otherwise you will get an error. do you want it to close the workbook2 after updating the information?

Originally Posted by theSizz

That's not what I'm trying to do. If I understand your code that will copy from a sheet in the workbook to another sheet in the same workbook.
What I want to do is copy cells from a sheet in "Workbook A" to cells in a sheet in "Workbook B"
In other words What I am trying to do is copy the cells in range A1 to G 205 in the workbook named "Walkroutes".
and then paste it into a different sheet in a different workbook named "Updates"

Ok I fixed it up and made it a bit neater.

and now you dont have to close the book2.

Sub CopyRangeToAnotherSheet()
On Error GoTo IsClosed
If Not Workbooks("Book2.xls") Is Nothing Then
     
Windows("Book1.xls").Activate
    Sheets("Walkroute").Activate
    Range("A1:G205").Select
    Selection.Copy
    
    
Windows("Book2.xls").Activate
    Sheets("Updates").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    
    Exit Sub
    End If
  
IsClosed: Workbooks.Open Filename:="C:\Documents and Settings\1\Desktop\Book2.xls"

End Sub

Let me know how it goes

Did this solve the problem?

Sorry.
It still doesn't copy. Still get a runtime error 1004

The error is because the file does not exist. go to the file on your desktop right click and go to properties and where it says location.

That all error 1004 means.

if that dont work try moving all files to the c drive

so save book 1 into "C:\book1.xls
and book 2 into "C:\book2.xls"

Then change the last line of code to reflect this.

Then it should work.

If you could upload both books so I can program it to reflect where you have it saved.

ok
I want to copy from walkroutelan.csv to copyofwalkrt444.xlsm
the code is included.

I'm not sure if the second file got attached here it is again in case you didn't get it.

The first file is not here walkroutelan.csv

here is the first file.

still not there

I'm having trouble attaching the file. It wouldn't attach because the .csv format is not supported, so here it is in the .xlsx format.
Here it is again.

here it is in .xlsx format
Evidently the .csv format is not supported and it wouldn't upload.

Ok so let me get this right.

You want to copy all information from Walkroutelan.xlsx and paste it into walkrt444.xlsm?

Like what are you trying to achieve, as the data looks the same.

Is this part of a bigger project?

is it in relation to an order sheet type thing?

please explain what your actual goal is and please be as descriptive as possible.

Zip up all the files that reference each other and list for me what each file is meant to do.

ie. I want to copy all data from walkroutelan.xlsx from cells A1 to G400 and paste them into walkrt444.xlsm B3

I need references.

and if you could save the files as plain .xls files that would make it easier to program them.

Thanks

it's getting to past midnight here.
I'll have a fresh head and deal with it in the morning.
I really appreciate the time you've spent. I'll post back in the morning.
Thank you so much.

Thanks to rbrhodes who provided the solutions listed below.
Both procedures work and produce the desired results.

Here's the simplistic way:


VBA:

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


VBA:

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

I'd like to thank all who worked on this for their time and effort.
Being new to the forums I didn't realize about the cross posting issues and implications. Please accept my sincere apology.
Thanks to all.

The sizz,
the least you can do is to provide a link to this thread on the other forum you posted the same question.
You are having different people working on the same problem without knowing that others are working on it.
Keep in mind they are all volunteers and may be investing time to answer you when someone else has already done so

Being new to the forums I didn't realize about the cross posting issues and implications. Please accept my sincere apology.

Thesizz, you have been a member of two forums for 4 years, posted 27 times on another forum and 13 times on this one. I wouldn't call that new.
You also didn't take the time to post the link on the other forum, showing no respect for posters .
This attitude is not acceptable, and therefore I will ban you.
I hope that after this period, you will have understood how things work on forums.