Copy Data from external workbook with external macro

HunterA

New Member
Joined
May 18, 2018
Messages
7
Hello, thanks in advance for any help! Im newish to VBA.

I am trying to fix a mistake that I made from a master workbook into multiple save as workbooks.

The formulas I wrote in the original master were incorrect from range (A36:E37)

I wrote a VBA in my personal book that attempts to unlock the sheet, clear the data, replace the data from an external sheet, protect the sheet, save, and close.

I am using thisworkbook, and activeworkbook but it refrences my personal workbook in that case, not the one I want to.

Example, Open workbook 1 (corrected master) and 2. Copy range data from 1 to 2, save 2, close 2, repeat with 3-100

' fixprimalrecipe Macro
'

'
Range("A36:E37").Select
ActiveSheet.Unprotect
Selection.ClearContents
Windows("PRIMAL ALCHEMY RECIPE COSTING CARD.xls").Activate
Range("A36:E37").Select
Selection.Copy
ActiveWorkbook.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save
End Sub


Any help is again, much appreciated!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

HunterA

New Member
Joined
May 18, 2018
Messages
7
possibly this is a more concise way of explaining this:

I have a workbook A that I use as a template for spinoff workbooks B, C, D, wateverworkbook name is chosen

I made an error in formulas range A36:E37. I need to correct it in all the subsequently created workbooks, which can have any random name

I want to open the corrected master workbook A, and copy the range from A to whateverworkbookname

Every time I use thisworkbook refrerence, it pastes the data to my personal macro workbook, same thing with activeworkbook.

I'm sure there's a simple solution, (like assigning a variable to the freshly opened workbook that needs fixing?) but I don't know how to do that.

Help is much appreciated!

Also of note, I am planning on manually opening the whaverworkbookname, then VBA unprotecting the sheet, copy paste function, protecting the sheet, saving, and closing the whateverworkbookname book when the macro completes, to be repeated with the rest of the incorrect workbooks.

I don't want to put the macro into the source or target workbooks if possible. it could be in the source if it needed to be.

If there is a smarter way to do this (which is probably way over my head) like applying a macro to all workbooks in a folder for instance, I would be interested in a point in the right direction to learn about it.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,860
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top