Copying multiple ranges to another workbook in VBA, Active workbook won't work as I'm using a perosnal.xlsb macro

Question

I have looked at multiple forums trying to find a solution to this.

We receive weekly reports at work & I would like to create a macro that takes this data from two different tabs & uploads it to a master document on a sharepoint site.

The issue I have is that I'm using a PERSONAL.XLSB macro, I can't use "ThisWorkbook" because it refers to the Personal.XLSB workbook. I also can't use "Activeworkbook" because I need to go back to the original workbook to copy a second range of data from a second tab, the workbook where the data gets pasted becomes the "Activeworkbook".

The Code I am using is as follows.

Sub DataUpload()

    Dim y As Workbook
    Dim x As Workbook

    Set x = ActiveWorkbook
    Set y = Workbooks.Open("URL")

    x.Sheets("ORSA026").Range("A2:B250").Copy
    y.Sheets("ORSA994").Range("A1:B200").EntireColumn.Delete
    y.Sheets("ORSA026").Range("A1:B200").EntireColumn.Delete
    y.Sheets("ORSA026").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
    x.Sheets("ORSA994").Range("A2:B250").Copy
    y.Sheets("ORSA994").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

    x.Save
    x.Close
End Sub

Help would be much appreciated!

What problems are you having? (From the start of your question I was thinking "Ahh - we need to show how to set some Workbook objects" but you seem to be doing that correctly.) — YowE3K
It gives me "Run-time error '-2147221080 (800401a8)': Automation error. It highlights the line the first instruction to copy. — user7329566
I managed to fix it with this in the end, thanks for your help. — user7329566

user7329566 user7329566 · Accepted Answer · 2016-12-22T10:37:22

Sub DataUpload()

Dim y As Workbook

Dim x As Workbook

Set x = ActiveWorkbook

Set y = Workbooks.Open("URL")

y.Sheets("ORSA026").Range("A1:B249").Value = x.Sheets("ORSA026").Range("A2:B250").Value

y.Sheets("ORSA994").Range("A1:B249").Value = x.Sheets("ORSA994").Range("A2:B250").Value

y.Save

y.Close

End Sub

Copying multiple ranges to another workbook in VBA, Active workbook won't work as I'm using a perosnal.xlsb macro

1 Answers