Autofill Columns on Multiple Worksheets


New Member
Sep 7, 2005
I have a workbook with a pivot table on the sheet "States2". Column 2 is a list of city/states. I have another sub routine that creates a worksheet from a template for each item on this pivot table and names it the city/state in column 2. After all of the sheets are created, I need a routine to autofilter a worksheet called "Export" by each item in this pivot table, copy the rows of data and paste them onto the worksheet that was created above. The worksheet names are the same as the city/state in the pivot table and the auto-filtered data. Here is the script I have pieced together from other great scripts I found on this board, I can't see why it won't work. Any help would be greatly appreciated. A few more pieces of info....the column on the Export worksheet to filter by is column 12 (or L). The data needs to be pasted into row 13 (column A) on the individual city/state worksheets. Thank you in advance for your help!

Sub test()
Dim FromSheet As Worksheet ' currently active sheet containing table
Dim FromColumns As Integer ' number of columns in table
Dim FromLastRow As Long ' automatic count. no blanks here
Dim CopyRange As Range ' range to copy. automatically set
Dim ToSheet As Worksheet ' amend name below
Dim ToLastRow As Long ' automatic count
Dim myCell As Range
Dim WS As Worksheet

With Worksheets("State2")
On Error Resume Next
For Each myCell In .Range(("A5"), _
.Cells(.Rows.Count, 1).End(xlUp)).Cells

For Each WS In ActiveWorkbook.Worksheets
If WS.Name = myCell Then
Set ToSheet = WS

Worksheets("Export").AutoFilter Field:=12, Criteria1:=myCell
FromColumns = Worksheets("Export").Range("A1").End(xlToRight).Column

FromLastRow = Worksheets("Export").Range("A1").End(xlDown).Row 'no blanks in this column
'- set copy range from row 2 down

Set CopyRange = Worksheets("Export").Range(Cells(2, 1), Cells(FromLastRow, FromColumns))


WS.Cells(13, 1).PasteSpecial xlPasteValuesAndNumberFormats
WS.Activate ' to show copied data
Application.CutCopyMode = False ' cancel selection

End If
Next WS
Next myCell
End With

End Sub

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Latest member