Add data from list to worksheet based on tab name

EKinATX

New Member
Joined
Sep 30, 2014
Messages
6
Hello,

I have a macro that creates a new sheet from a template and renames these tabs based on a list. The template is called "Template" and the list is in column A of a tab named "List". See code below.

Sub Copy_Sheets()
Dim i As Integer
Dim wks As Worksheet
Dim Last_Row As Long
Set wks = Sheets("List")
Last_Row = wks.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Last_Row
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = wks.Cells(i, 1)
Next
Calculate
End Sub

I would like to add some additional code, where I copy and paste data from a list into the new sheet.
I have additional data in each row of the "List" sheet in column B, C, and D that corresponds to the sheet name in column A (same row). For example, cells B10, C10, and D10 correspond to it's tab name in A10. I would like to paste this data into specific cells C3, C4, C5, for each sheet.

Thanks for the help!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

does this work as expected?

Code:
Sub Copy_Sheets()
    Dim i As Integer
    Dim wks As Worksheet
    Dim Last_Row As Long
    Set wks = Sheets("List")
    Last_Row = wks.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To Last_Row
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = wks.Cells(i, 1)
        Sheets("List").Range("B" & i & ":D" & i).Copy Range("C3")
    Next
    Calculate
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,956
Messages
5,525,884
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top