Autofill Columns on Multiple Worksheets

harmanie

New Member
Joined
Sep 7, 2005
Messages
6
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))

CopyRange.Copy

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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,505
Messages
5,832,117
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top