how do i write a macro that copies a template sheet an names it after a call in another sheet, I want this to loop all the way down a column creating new sheets for each entry.
thanks
how do i write a macro that copies a template sheet an names it after a call in another sheet, I want this to loop all the way down a column creating new sheets for each entry.
thanks
I got this far, but i cant loop it so that it repeats this, creating a new sheet with a new name taken from cell AR3, and so on
any ideas?
Sub export2()
'
' export2 Macro
' Macro recorded 26/07/2007 by Michael Skinner
'
'
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(3)
Sheets("export").Select
Range("AR2").Select
Selection.Copy
Sheets("Template (2)").Select
Sheets("Template (2)").Name = "matt Meacham"
Sheets("export").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("E2:AA2").Select
ActiveSheet.Paste
Sheets("export").Select
Range("b2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h10").Select
ActiveSheet.Paste
Sheets("export").Select
Range("c2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("H11").Select
ActiveSheet.Paste
Sheets("export").Select
Range("d2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("E6").Select
ActiveSheet.Paste
Sheets("export").Select
Range("e2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h18").Select
ActiveSheet.Paste
Sheets("export").Select
Range("f2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("e36").Select
ActiveSheet.Paste
Sheets("export").Select
Range("g2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h20").Select
ActiveSheet.Paste
Sheets("export").Select
Range("h2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h23").Select
ActiveSheet.Paste
Sheets("export").Select
Range("i2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h24").Select
ActiveSheet.Paste
Sheets("export").Select
Range("j2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h26").Select
ActiveSheet.Paste
Sheets("export").Select
Range("k2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h28").Select
ActiveSheet.Paste
Sheets("export").Select
Range("l2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("e38").Select
ActiveSheet.Paste
Sheets("export").Select
Range("m2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("h30").Select
ActiveSheet.Paste
Sheets("export").Select
Range("n2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("matt Meacham").Select
Range("E40").Select
ActiveSheet.Paste
End Sub
I've now done this which is better, it loops and creates sheets with the right name but because of the way i am using the Activecell it only creates two sheets
is there a work around?
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 26/07/2007 by Michael Skinner
'
Sheets("export").Select
Range("Ar2").Select
Do Until IsEmpty(ActiveCell)
Dim sSheetName As String
sSheetName = (ActiveCell)
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(3)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = sSheetName
Sheets("export").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets(sSheetName).Select
Range("E2:AA2").Select
ActiveSheet.Paste
Sheets("export").Select
Range("Ar2").Select
ActiveCell.Offset(1, 0).Select
Loop
'
End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks