Macro to populate multiple tabs from a master file
I have a data file with 50 columns, including one named "location". There are
hundreds of locations, with values including '01', '21', '71'. I also have report tabs which contain a template - the number of report tabs matches the number of unique values in the location column, so in my example there are three report tabs. The report tabs are identical to each other, except for their name. I want to create a macro to help populate the report tabs (the template contains formulas and calculations), based on the value of the location column. What I have been doing is manually inserting "=IF(Data!A2=71,Data!M2)" in the report tab, to pull data from cell M2 of the master file, if the value of cell A2 = 71. What I want is to be able to do is to receive the master file, which will always have the variables in the same columns, and run a macro which will dump the data into the pre-created report tabs. The number of tabs may differ from time to time, but that can be manually handled. Ideas? |
Macro to populate multiple tabs from a master file
Hi Tanya
I assume you have two sheets in your workbook: Data & Template The macro below will copy template sheet and rename the copy as "location", then insert "location" in B2. Now you can use your previous formula (insert the formula in Template sheet before the macro is run): "=IF(Data!A2=B2,Data!M2)" If you want the macro to populate the reports, further details is needed. Can the row be copied to the target sheet if Location match, or .... Sub CreateReport() NoOfTabs = Sheets("Data").Range("A2").End(xlDown).Row - 1 Set TemplSh = Sheets("Template") For sh = 1 To NoOfTabs Set NewSh = TemplSh.Copy(After:=Sheets(Sheets.Count)) NewSh.Name = Sheets("Data").Range("A1").Offset(sh, 0).Value Range("B1").Value = NewSh.Name Next End Sub Hopes it helps Regards, Per "Tanya" skrev i meddelelsen ... I have a data file with 50 columns, including one named "location". There are hundreds of locations, with values including '01', '21', '71'. I also have report tabs which contain a template - the number of report tabs matches the number of unique values in the location column, so in my example there are three report tabs. The report tabs are identical to each other, except for their name. I want to create a macro to help populate the report tabs (the template contains formulas and calculations), based on the value of the location column. What I have been doing is manually inserting "=IF(Data!A2=71,Data!M2)" in the report tab, to pull data from cell M2 of the master file, if the value of cell A2 = 71. What I want is to be able to do is to receive the master file, which will always have the variables in the same columns, and run a macro which will dump the data into the pre-created report tabs. The number of tabs may differ from time to time, but that can be manually handled. Ideas? |
Macro to populate multiple tabs from a master file
A good start, but let me give more detail. I have one Data sheet, and the
goal is to have as many Report sheets as there are values in the column named, "Site" (around 75, now). The idea is to read the "Site" column from the Data sheet, and if the value is "10", to populate the appropriate cells in the tab with the site name, "10". If the Data Sheet contains data like this: Site Gender Age Score1 Score1 1 f 25 34 56 10 m 23 35 57 I want tab '1' to read the Data sheet, search the Site column, and if it finds a value of '1', to pull the data in the record associated with '1' into the preordained cells in tab '1'. Some of these cells in tab 1 have formulas, some are direct moves. The issue is that the number of values in the Site column may change, so creating a link is not the way to go (data for Site '1' will not always occur in the same row). "Per Jessen" wrote: Hi Tanya I assume you have two sheets in your workbook: Data & Template The macro below will copy template sheet and rename the copy as "location", then insert "location" in B2. Now you can use your previous formula (insert the formula in Template sheet before the macro is run): "=IF(Data!A2=B2,Data!M2)" If you want the macro to populate the reports, further details is needed. Can the row be copied to the target sheet if Location match, or .... Sub CreateReport() NoOfTabs = Sheets("Data").Range("A2").End(xlDown).Row - 1 Set TemplSh = Sheets("Template") For sh = 1 To NoOfTabs Set NewSh = TemplSh.Copy(After:=Sheets(Sheets.Count)) NewSh.Name = Sheets("Data").Range("A1").Offset(sh, 0).Value Range("B1").Value = NewSh.Name Next End Sub Hopes it helps Regards, Per "Tanya" skrev i meddelelsen ... I have a data file with 50 columns, including one named "location". There are hundreds of locations, with values including '01', '21', '71'. I also have report tabs which contain a template - the number of report tabs matches the number of unique values in the location column, so in my example there are three report tabs. The report tabs are identical to each other, except for their name. I want to create a macro to help populate the report tabs (the template contains formulas and calculations), based on the value of the location column. What I have been doing is manually inserting "=IF(Data!A2=71,Data!M2)" in the report tab, to pull data from cell M2 of the master file, if the value of cell A2 = 71. What I want is to be able to do is to receive the master file, which will always have the variables in the same columns, and run a macro which will dump the data into the pre-created report tabs. The number of tabs may differ from time to time, but that can be manually handled. Ideas? |
Macro to populate multiple tabs from a master file
Hi again Tanya
Try if this is what you need, just change ranges as desired. Sub CreateReport() Dim DataSh As Worksheet Dim TemplSh As Worksheet Dim FilterRange As Range Dim ListCell As String Dim Site As String Application.ScreenUpdating = False Set DataSh = Worksheets("Data") Set TemplSh = Worksheets("Template") DataSh.Activate Set FilterRange = Range("A1", Range("A1").End(xlDown)) ListCell = "X1" ' Change to unused column on Data sheet Range(ListCell, Range(ListCell).End(xlDown)).ClearContents FilterRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range(ListCell), Unique:=True NoOfTabs = DataSh.Range(ListCell, DataSh.Range(ListCell) _ .End(xlDown)).Rows.Count - 1 Set FilterRange = FilterRange.Resize(FilterRange.Rows.Count, 5) FilterRange.Select For sh = 1 To NoOfTabs Worksheets("Template").Copy After:=Sheets(Sheets.Count) Site = Sheets("Data").Range(ListCell).Offset(sh, 0).Value Sheets(Sheets.Count).Name = Site DataSh.Activate Selection.AutoFilter Field:=1, Criteria1:=Site Selection.Copy Sheets(Site).Range("A4") ' change range to suit Next FilterRange.AutoFilter Application.ScreenUpdating = True End Sub Regards, Per "Tanya" skrev i meddelelsen ... A good start, but let me give more detail. I have one Data sheet, and the goal is to have as many Report sheets as there are values in the column named, "Site" (around 75, now). The idea is to read the "Site" column from the Data sheet, and if the value is "10", to populate the appropriate cells in the tab with the site name, "10". If the Data Sheet contains data like this: Site Gender Age Score1 Score1 1 f 25 34 56 10 m 23 35 57 I want tab '1' to read the Data sheet, search the Site column, and if it finds a value of '1', to pull the data in the record associated with '1' into the preordained cells in tab '1'. Some of these cells in tab 1 have formulas, some are direct moves. The issue is that the number of values in the Site column may change, so creating a link is not the way to go (data for Site '1' will not always occur in the same row). |
All times are GMT +1. The time now is 03:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com