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]

I get a compile error on the Intersect line if you can help please?

 

[Fluff]

You're missing a ) from the end of the range

 

[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 "C:\Users\ *insert file path of workbook
    Set destWB = ActiveWorkbook
   
        '   Find last row of data in destination workbook
    lastRow = destWB.Sheets("Daily Stats WC Template").Cells(Rows.Count, "A").End(xlUp).Row + 1
'   Copy data from source workbook to destination workbook
srcWB.Sheets("Daily Stats").Range("A1:A15").Copy
    destWB.Sheets("Daily Stats WC Template").Range("A" & lastRow).Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("B1:B15").Copy
    destWB.Sheets("Daily Stats WC Template").Range("B" & lastRow).Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("C1:C15").Copy
    destWB.Sheets("Daily Stats WC Template").Range("C" & lastRow).Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("D1:D15").Copy
    destWB.Sheets("Daily Stats WC Template").Range("D" & lastRow).Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("E1:E15").Copy
    destWB.Sheets("Daily Stats WC Template").Range("E" & lastRow).Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("F1:F15").Copy
    destWB.Sheets("Daily Stats WC Template").Range("F" & lastRow).Resize(15, 1).PasteSpecial xlPasteValues
srcWB.Sheets("Daily Stats").Range("G1:G15").Copy
    destWB.Sheets("Daily Stats WC Template").Range("G" & lastRow).Resize(15, 1).PasteSpecial xlPasteValues
'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True
End Sub

 

[jondavis1987]

I'm sure there's a more effective way to do that but this is how i have my vba set up. There are asterisks where you need to fill out some information. This should work. It's a variation of the macro I use. This will open the workbook, copy the data into it, save it, and close it. My vba also saves the current document as a pdf but i removed that section for you. My vba has end with and then end sub under it. So if that doesn't work you may need to add the end with in there.

 

[Fluff]

@jondavis1987
In future please use code tags when posting code, the # icon in the reply window.

 

[jondavis1987]

My bad. Been a while since I posted in here.

 

[Patriot2879]

Hi Thank you for the code, but i am still getting an error, the sheet i want to paste into is called 'Template' which is located in G:\Daily Stats WC Template.xlxs. and i want to copy the current workbook and this sheet is called Daily Stats. sorry with all the queries i am still learning.

 

[Fluff]

Have you seen my reply in post#2?

 

[jondavis1987]

Fluff's will probably work well for you. I was typing mine when he posted it so I didn't see that he had fixed yours.