CODEWITHSUNDEEP
excelvba
jamesfzhang
jamesfzhang

Reputation: 4491

Sheet generation in Excel

In Sheet1, I have rows of data points for several different measures (columns). I manually created a second sheet which performs some analysis on the first column of data. How can I create a macro or VBA code such that a new sheet is created for each of the columns in Sheet1 and the exact same analysis is performed as the one I created manually? Thanks.

Upvotes: 0

Views: 139

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Create a "template" analysis sheet with a place to paste a column of data, then make copies of that template and copy-paste in the relevant column from Sheet1.

Eg:

Sub Tester()
    Dim rngData As Range, col As Range, colNum As Integer
    Dim shtTemplate As Worksheet, shtData As Worksheet

    Set shtData = Sheets("Sheet1")
    Set shtTemplate = Sheets("Template")

    Set rngData = shtData.Range("A1").CurrentRegion
    colNum = 0

    For Each col In rngData.Columns
        colNum = colNum + 1
        shtTemplate.Copy before:=shtTemplate
        With Sheets(shtTemplate.Index - 1)
            .Name = "Column " & colNum
            col.Copy .Range("A1")
        End With
    Next col
End Sub

Upvotes: 2

Related Questions