Copy data to new-added worksheets based on selection

Question

I have the following code that adds new worksheets based on a range of cells in another worksheet. The code adds up to 10 worksheets and it will name them after the data written in cells. For example: I might have the following data in my "VG-Class" worksheet:

Cell U2: 1851-A3 --------- Cell U3: 1736-A9 ------ Cell U4: 2171-A7

So the macro will create 3 new worksheets with the names 1851-A3, 1736-A9 and 2171-A7. This is my code:

Private CommandButton1_Click()
Dim lRow As Integer, i As Integer
Dim ws As Worksheet
Dim cel As Range
Dim numLines As Integer
i = 0
lRow = Worksheets("V-Class GC").Cells(Rows.Count, 10).End(xlUp).Row

For Each cel In Worksheets("V-Class GC").Range("U2:U" & lRow)
    If shtNameChk(cel.Value) = False Then
    Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        ws.Name = cel.Value
    cel.Offset(0, -1).Hyperlinks.Add Anchor:=cel.Offset(0, -1), Address:="", _
    SubAddress:=ws.Name & "!A1", TextToDisplay:=cel.Offset(0, -2).Value & ", " & cel.Offset(0, -3).Value
    i = i + 1
    End If
Next
Worksheets("V-Class GC").Select
End Sub

What I'm trying to do is to populate each one of these new worksheets based on the data from another worksheet called "Information". The new created worksheet "1851-A3" has associated a model, package, reference and price in a list with more codes. I'll use a VLOOKUP to find and place these values. However, I don't know how to make a reference between the name of my worksheets and the codes in this list so the macro can find and place the information corresponding to every code/worksheet name. It's like a report generator.

Look at this image if you want to see graphically what I mean:

Thank you so much in advance!

barvobot barvobot · Accepted Answer · 2017-09-06T13:42:37

Put this stuff at the top of your macro:

Dim wInfo As Worksheet
Dim x as Long
Set wInfo = Sheets("Information")

Then put this right before your End If

'check if model # is found in Col D of Information
If Application.CountIf(wInfo.Columns(4), ws.Name) Then

    'copy headers
    wInfo.Range("D1:H1").Copy ws.Range("D1")

    'look for matching model # and copy data if match
    For x = 1 To wInfo.Cells(wInfo.Rows.Count, 4).End(xlUp).Row
        If wInfo.Cells(x, 4).Value = ws.Name Then wInfo.Range("D" & x & ":" & "H" & x).Copy ws.Cells(Application.CountA(ws.Columns(4)) + 1, 4)
    Next x
End If

In the sample data you provided, it looked like there was only info to be copied over between columns D and H, so that's what I based this answer on. It copies the values from D to H on the Information sheet to D to H on the new worksheet.

Copy data to new-added worksheets based on selection

1 Answers