Change destination workbook in macro

[BDD2015]

Hi,

I am currently using a macro that works great but I would like to change the destination -

Dim wsSource As Worksheet, wsDest As Worksheet
Dim lastRow As Integer

Set wsSource = ThisWorkbook.Sheets("RDBMergeSheet")
Set wsDest = ThisWorkbook.Sheets("MasterData")

I would like the destination to be a workbook that I will have open called "Volume Data".xlsm with the ws called "Master Data"

Any help would be appreciated. Thanks in advance,

[PaulM100]

Try:

Please Login or Register  to view this content.

Change to your path in red

[BDD2015]

Hi, Paul

Thanks but it has a problem with Set WD...
I have changed it to -

Dim WD As Workbook
Dim FP As String
Dim wsSource As Worksheet, wsDest As Worksheet
Dim lastRow As Integer

FP = "G:\APP ZONE\BDs MASTERS\BD CPC\BD NEW\Volume Data" & "\"

Set WD = Workbook.Open(FP & "Volume Data.xlsm")
Set wsSource = ThisWorkbook.Sheets("RDBMergeSheet")
Set wsDest = WD.Sheets("MasterData")

Volume Data is also the name of the folder

[BDD2015]

Is there a problem with my path?

[PaulM100]

Looks fine. What is the problem with Set WD?

[BDD2015]

Hi. Thanks.
When I run the macro I get a Run time error 424 and when I click Debug, the Set WD... is highlighted yellow

[PaulM100]

Oh, I forgot a letter. Change to this:

Please Login or Register  to view this content.

[BDD2015]

hahaha. Closer. But Volume Data.xlsm is already open and that's where I am running the code from.
Now it's telling me that Volume Data is already open and re-opening it will...

[PaulM100]

I would like the destination to be a workbook that I will have open called "Volume Data".xlsm with the ws called "Master Data"

This is saying that you need the destination as another workbook. How you run the code from it, if is another workbook?

[BDD2015]

I run the code in the wb called Volume Data which creates an RDBMergeSheet which then copies the new data into the MasterData sheet which was in the same wb.
But the file is getting too big so I moved it into its own wb called wb called Raw Data with the ws named Master Data. I need the RDBMergeSheet data copied to that wb.
Sorry for the confusion. I would make a terrible teacher. lol

[BDD2015]

I think I have it. Thank you for pointing out that conflict. I made the following change - Set WD = Workbooks.Open(FP & "Raw Data.xlsm"). Exactly what I was looking for. Thank you for your patience and help!