VBA code simple copy data from one workbook sheet and simple paste to another workbook sheet.

[Lv4games]

Hello everyone,

New to the site.

So I have this repetitive duty where I copy the whole sheets data from one workbook (name AMS RT AMSCOMMAND, sheet (AMS_RT_AMSCOMMAND_Universal) and paste it on to another workbook (name:Mobile Services Dashboard v4 091916(mc), sheet (Cisco Report) . I have the below code but it keeps crashing my excel. I am working with excel 2013. Here is the code that I have so far (if there is a better code I am willing to try), any help would be much appreciated.

Sub foo3()
Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks.Open("\\cead.prd\data\USERS\PDX0\j6h068\USRDATA\Documents\Projects\DCR\AMS_RT_AMSCOMMAND_Universal Albany.xls")
Set y = Workbooks.Open("\\cead.prd\data\USERS\PDX0\j6h068\USRDATA\Documents\Projects\DCR\Mobile Services Dashboard v4 091916(mc).xls")

Set ws1 = x.Sheets("AMS_RT_AMSCOMMAND_Universal")
Set ws2 = y.Sheets("Cisco Report")

With ws1
    .Cells.Copy ws2.Cells
    y.Close True
    x.Close False
End With

End Sub

 

[yky]

It seems ws2 is a blank sheet. Is its position in the workbook important? If not, try this code, it works faster but you need to delete 'Cisco Report" from y.

Sub foo3()
Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

[COLOR=#574123]Set x = Workbooks.Open("\\cead.prd\data\USERS\PDX0\j6h068\USRDATA\Documents\Projects\DCR\AMS_RT_AMSCOMMAND_Universal Albany.xls")[/COLOR]
[COLOR=#574123]Set y = Workbooks.Open("\\cead.prd\data\USERS\PDX0\j6h068\USRDATA\Documents\Projects\DCR\Mobile Services Dashboard v4 091916(mc).xls")[/COLOR]

[COLOR=#574123]Set ws1 = x.Sheets("AMS_RT_AMSCOMMAND_Universal")[/COLOR]

With ws1
.Copy after:=y.Sheets(Sheets.Count)
y.Sheets(Sheets.Count).Name = "[COLOR=#574123]Cisco Report[/COLOR]"
End With

y.Close True
x.Close False


End Sub

 

[Lv4games]

I tried the code you provided and it still did not work. Is there away we can modify the code were it doesnt open Workbook Y? I guess I just want it to copy whats on workbook X sheet AMS_RT_AMSCOMMAND_Universal over to Workbook Y sheet Cisco Report. The code you provided me still crashes my excel. I put the code exactly how it is on your code.

AMS_RT_AMSCOMMAND_Universal

 

[yky]

I had tested the code on my computer (using local files, not networked ones) and, after reading your reply, tested the code again. It worked both times.

When your Excel crashed, did you get any error messages?

You can step through the code and see which line causes the crash.

 

[Lv4games]

It looks like its now crashing now, however when it runs the macro it only opens the files but doesn't copy the data over like I want it. Here is the code I currently have in the file.

Sub foo3()
Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks.Open("\\cead.prd\data\USERS\PDX0\j6h068\USRDATA\Documents\Projects\DCR\AMS_RT_AMSCOMMAND_Universal Albany.xls")
Set y = Workbooks.Open("\\cead.prd\data\USERS\PDX0\j6h068\USRDATA\Documents\Projects\DCR\Mobile Services Dashboard v4 091916(mc).xls")

Set ws1 = x.Sheets("AMS_RT_AMSCOMMAND_Universal")
Set ws2 = y.Sheets("Cisco Report")

With ws1
    .Cells.Copy ws2.Cells
    y.Close True
    x.Close False
End With

End Sub

 

[Lv4games]

I meant to say its is not crashing anymore.

 

[yky]

Try this:

.UsedRange.Copy ws2.Cells

 

[Lv4games]

Looks like it didnt paste the last update. Thank you so much it is working now.