Sheet name and copy paste macro

[mfd2112]

Hi,
I have a couple questions about these macros.
The locations macro copies from a sheet named Wkd and I can’t figure out how to have the macro run without having to be at that sheet.
The location macro will paste the filtered data in a new sheet that was created from a template “Blank” sheet, how can the macro delete the all the rows below what was pasted in the new sheet.
Also I’m trying to combine this code with code that creates sheets and names the sheets from a sheet with a list “List”. Currently I run the createSheetNames first then I run the Loactions macro but if they can be combined that would be awesome.

[FONT=Verdana][COLOR=black]Sub CreateNameSheets()[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] ' by Dave Peterson[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] ' List sheetnames required in col B in a sheet: List[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] ' Sub will copy sheets based on the sheet named as: Template[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] ' and name the sheets accordingly[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Dim TemplateWks As Worksheet[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Dim ListWks As Worksheet[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Dim ListRng As Range[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Dim mycell As Range[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Set TemplateWks = Worksheets("Blank")[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Set ListWks = Worksheets("List")[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      With ListWks[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      Set ListRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  End With[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  For Each mycell In ListRng.Cells[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      TemplateWks.Copy after:=Worksheets(Worksheets.Count)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      On Error Resume Next[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      ActiveSheet.Name = mycell.Value[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      If Err.Number <> 0 Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]          MsgBox "Please fix: " & ActiveSheet.Name[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]          Err.Clear[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]      On Error GoTo 0[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Next mycell[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]Sub Locations1()[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Dim Lastrow As Long     'Last used row in column A[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Lastrow = Range("A" & Rows.Count).End(xlUp).Row[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]  Range("A2:A" & Lastrow).AutoFilter Field:=1, Criteria1:="=10th & 102nd"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("A3:L" & Lastrow).Cells.Copy[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]  Sheets("10th & 102nd").Select[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("A3").PasteSpecial[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("H3").Select[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]  Sheets("Wkd").Select[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("A2:A" & Lastrow).AutoFilter Field:=1, Criteria1:="=145th & I-5"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("A3:L" & Lastrow).Cells.Copy[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]  Sheets("145th & I-5").Select[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("A3").PasteSpecial[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("H3").Select[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]  Sheets("Wkd").Select[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("H3").Select[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Application.CutCopyMode = False[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  ActiveSheet.AutoFilterMode = False[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("A2:L2").Select[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Selection.AutoFilter[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Range("H3").Select[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]

 

[mfd2112]

Ok, Jerry did it 'SHEET1 TO MANY SHEETS<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks Jerry those are awesome! Works excellent!<o></o>
My regards,<o></o>
Dale<o></o>