Copy a template and fill in values from another worksheet

user_15949554 0
Viewed 50

I am now trying to creating several worksheets and copying data from an existing worksheet to the worksheet that I just created.

This is what I have tried so far:

Sub CreateTemplate()

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "CUST001"
Worksheets("Template").Cells.Copy Worksheets("CUST001").Cells
Worksheets("CUST001").Select
Range("C4") = "='CDE Information'!R[-2]C[-2]"
Range("C5") = "='CDE Information'!R[-3]C[-1]"
Range("C6") = "1111"
Range("C7") = "2222"

End Sub

This is an example of a table that I want to copy. Table

I also want to create the worksheets and name them by the values of each row in column A.

So, it seems to me that I should do something with loops but I have no idea about that. Can anyone help me out? Thank you in advance!

vba excel
1 Answers

Welcome to stack. Try this:

Option Explicit
Sub copyWs()
    Dim arr, j As Long
    With Sheet1
        arr = .Range("A1").CurrentRegion.Value2 'get all data in memory
        For j = 1 To UBound(arr) 'traverse rows
            .Copy After:=ActiveWorkbook.Sheets(Worksheets.Count) 'add ws after the last ws
            Sheets(ActiveWorkbook.Sheets(Worksheets.Count).Index).Name = arr(j, 1) 'name the last added ws
        Next j
    End With
End Sub

Now that we already have an array with all data we can also copy only part of our data to a new sheet instead of copying the whole sheet. To achieve this we'll just create a blank sheet first:

Sheets.Add After:=ActiveWorkbook.Sheets(Worksheets.Count) 'add ws after the last ws

When iterating an array we'll use 2 "counter" variables. 1 to go trough the lines, 1 to go trough the columns.

Dim j As Long, i As Long 'initiate our counter vars
For j = 1 To UBound(arr) 'traverse rows
    For i = 1 To UBound(arr, 2) 'traverse columns
        'here we can access each cell by referencing our array(<rowCounter>, <columnCounter>
        'e.g. arr(j,i) => if j = 1 and i = 1 we'll have the values of Cell A1
    Next i
Next j

The "Ubound" function allows us to get the total nr of rows and columns.

Dim arr2
ReDim arr2(1 To 1, 1 To UBound(arr)) '=> we only need 1 line but all columns of the source, as we cannot dynamically size an array with the "dim", we redim
For j = 1 To UBound(arr) 'traverse rows
    For i = 1 To UBound(arr, 2) 'traverse columns
        'here we can access each cell by referencing our array(<rowCounter>, <columnCounter>
        'e.g. arr(j,i) => if j = 1 and i = 1 we'll have the values of Cell A1
        'we can dump these values anywhere in the activesheet, other sheet, other workbook, .. but to limit the number of interactions with our sheet object we can also create new, intermediant arrays
        'e.g. we could now copy cel by cel to the new sheet => Sheets(arr(j,1).Range(... but this would create significant overhead
        'so we'll use an intermediant array to store the full line

        arr2(1, i) = arr(j, i)
    Next i
        'when we have all the columns we dumb to the sheet
        With Sheets(arr(j, 1)) 'the with allows us the re-use the sheet name without typing it again
            .Range(.Cells(1, 1), .Cells(UBound(arr2), UBound(arr2, 2))).Value2 = arr2 'the ubound function allows us to size the "range" to the same size as our array, once that's done we can just dumb it to the sheet
        End With
Next j
user_92722700
Related