Copy data range from macro free workbook to another macro free workbook

Question

I am new to VBA!

I have a workbook A that I use as a template for spinoff workbooks B, C, D, etc.

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 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.

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.

Without code, it is very hard to help you. Also I assume that you put your code into the personal workbook? Can you clarify that? — Pᴇʜ

Pᴇʜ Pᴇʜ · Accepted Answer · 2021-02-16T07:45:49

First a note:

ThisWorkbook always refers to the workbook the code is written in. This never changes.
ActiveWorkbook is the workbook that has focus (is on top). This changes easily with a mouse click on any workbook.

The issue is probably that you run the code from VBA editor. But if it is in your personal workbook and you run from the editor, then as soon as you are in the code your active workbook is the personal one because that is where your code is and if you click there to run the code it has focus.

You can check if the active workbook is the personal one

If ActiveWorkbook.Name = ThisWorkbook.Name Then
    MsgBox "The active workbook is the personal one. Make sure to focus on the correct workbook."
    Exit Sub
End If

'rest of your code

Create a button or link a ribbon button to launch your macro and use ActiveWorkbook in your code.

Copy data range from macro free workbook to another macro free workbook

1 Answers