copy data from one workbook to another

[Patriot2879]

Hi I have the code below where I am trying to copy a range A1 to G15 into another workbook but its not working please can you help. I am trying to copy from Daily stats into Daily Stats WC Template, hope you can advise?

Private Sub CommandButton1_Click()
Dim x As Workbook
Dim y As Workbook
Dim vals As Variant
Set x = ThisWorkbook
Set y = Workbooks.Open("G:Daily Stats WC Template.xlsx")
With x.Sheets("Daily Stats")
   Intersect(.UsedRange, .Range("A1:G15").Copy y.Sheets("Daily Stats").Range("A1")
End With
End Sub

 

[Patriot2879]

Hi I tried the ) at the end of the Range but still didn't work

 

[Fluff]

Can you please repost the code with the amendment?

 

[jondavis1987]

Sub Open_Workbook()
 
    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
 
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook
 
'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "G:\Daily Stats WC Template.xlxs”
    Set destWB = ActiveWorkbook
  
  

'   Copy data from source workbook to destination workbook
srcWB.Sheets("Daily Stats").Range("A1:A15").Copy
    destWB.Sheets("Template").Range("A1").Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("B1:B15").Copy
    destWB.Sheets("Template").Range("B1").Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("C1:C15").Copy
    destWB.Sheets("Template").Range("C1").Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("D1:D15").Copy
    destWB.Sheets("Template").Range("D1").Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("E1:E15").Copy
    destWB.Sheets("Template").Range("E1").Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("F1:F15").Copy
    destWB.Sheets("Template").Range("F1").Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("G1:G15").Copy
    destWB.Sheets("Template").Range("G1").Resize(15, 1).PasteSpecial xlPasteValues

'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True
End Sub

If his fix doesn't work, this should do it

 

[jondavis1987]

I just realized I left the last row part on there. My vba was set to enter into a table and keep data. It doesn't appear that's what you want. So i just edited the last code I posted to take out the last row portion. It should automatically paste over any data in those ranges now. The way it was before would post under data in those ranges

 

[Patriot2879]

I just realized I left the last row part on there. My vba was set to enter into a table and keep data. It doesn't appear that's what you want. So i just edited the last code I posted to take out the last row portion. It should automatically paste over any data in those ranges now. The way it was before would post under data in those ranges

Thank you I shall try this in the morning

 

[Patriot2879]

Hi thank you for this it works great please can you advise how can it automoatically open the daily stats wc template nstead of loading it manually? thanks again much appreciated

 

[jondavis1987]

If the file path is correct in the open destination workbook part of the code, you just have to start running the code and it will do everything for you.

That file path tells it where it’s at, and you’re making it open. If you have to open and copy over stuff in a lot of workbooks it’s a very useful bit of code to include