I am asked to build reports based on informaiton in one worksheet. There are more than 30 reports to build. I have set the linkage of the cells for calculation ready(data can be filtered from another sheet to be used by the reports) as a template and new reports can be generated simply by copying the template to a newly-created sheet and change one cell that indicates the specific identifier of each report.
The question here is:
I will still need to copy and paste for 30 times and change the identifier of the report. Is there anyway of building a program based on VBA or other to automate the process that utilize the already built template without recoding for each cell value's assignment? Thank you very much!
Here you are. Create two sheets with names "Template" and "ControlSheet". On "ControlSheet" create two columns: first containing names for new sheets, and second containing parameter values. Then add this macro:
Public Const TemplateSheetName = "Template" Public Const ControlSheetName = "ControlSheet" Public Const ControlSheetFirstCell = "A2" Public Const TargetCell = "B5" Sub ParseReport() Dim i As Integer Dim NextSheetName As String Dim NextSheetValue As Variant i = 0 Do While True NextSheetName = Sheets(ControlSheetName).Range(ControlSheetFirstCell).Offset(i, 0).Cells(1, 1).Value If NextSheetName = "" Then Exit Do End If NextSheetValue = Sheets(ControlSheetName).Range(ControlSheetFirstCell).Offset(i, 0).Cells(1, 2).Value Sheets(TemplateSheetName).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) ActiveSheet.Name = NextSheetName ActiveSheet.Range(TargetCell) = NextSheetValue i = i + 1 Loop End Sub
ControlSheetFirstCell - the first cell in the control sheet in column containing names for sheets to be created. The values are in the column to the right. TargetCell - cell on newly created sheet where you need to write the report parameter