问题描述:

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

相关阅读:
Top