copy and paste multiple ranges from multiple tabs in old wkbk to new addresses in multiple tabs in new wkbk


Board Regular
Nov 3, 2014
I have a multi tab template which has been significantly revised. I need to migrate the data from about 500 files of the old template to the new one. This would involve copying and pasting multiple ranges from multiple tabs in old workbook to multiple tabs in the new workbook. (probably about 20-30 groups of data) The new template cell address would be different than the cell range being copied from. Also, it is possible that rows or columns may be hidden in some of the old files, so I think we need to unhide them first.

I am a beginner at macros, but trying to learn on my own.

What I need for this forum is a block of code to do 2 or 3 ranges with comments to tell me how to edit to add the remaining blocks.

I would expect the macro to reside in a separate workbook. I could easily have both the new template and the old file open at the time of running the macro, so no need to build locate file info into the macro. but I don't know how to identify the 2 files - the new template would have a static name, for example "new template", but the old files would all have different names.


First unhide all columns and rows in each tab of the old template

Copy cell range b2..b30 from oldtab2 in old template to cell c5 in newtab2 in new template

Copy cell range c2..c30 from oldtab2 in old template to cell d5 in newtab2 in new template

Copy cell range b2..b22 from oldtab3 in old template to cell b4 in newtab3 in new template

Indicate completion with message: "Migration done, save as new file"

This is a time sensitive project so any help would be greatly appreciated!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...