Copy visible cells after autofilter

[busby66]

Hi Everyone,

I'm a newbie to the forum & only a beginner in VBA trying to self teach myself as I did with MS Excel, so would be grateful for your assistance & advice.

So here's my problem...
I've got a large data set table of information with various row records identified by a unique reference number. I've coded the module to insert a sheet & insert a pivot table on this reference number, so I only see the ref number once. The code then looks at the first number in the pivot which then becomes a variable (myvar) which has been declared as a variant. The next piece of code inserts a new sheet, renames it as myvar then goes back to the data table and applies an autofilter on myvar value.

So all fine until this point where I simply want to copy all visible cells when the filter is applied & copy them to the newly added sheet named myvar.

My Code reads:

'Apply filter and copy the data range visible to new sheet
With DataRange
.AutoFilter Field:=3, Criteria1:=myvar
.SpecialCells (xlCellTypeVisible)
.Copy Destination:=Sheets(myvar).Range("A1")<---------- Code falls over on this line? (Run-time error 9 Subscript out of range)
End With

'autofit columns
Sheets(myvar).Columns.EntireColumn.AutoFit


'now clear autofilter to show all records
Sheets(wsSource).AutoFilterMode = False

 

[MARK858]

Where are you defining a sheet name to the variable myvar? That error normally means either a workbook name or a sheet name doesn't match what is in the code.

 

[busby66]

This is the previous piece of code that runs the pivot, picks the first ref number, adds the sheet & renames it as myvar.value

'call pivot table sub
Call PivotTable1


'make sure the pivot sheet is active
Sheets("MyPivot").Activate


'select start of first record in the pivot table
Range("A2").Select


'start loop condition
Do While IsNumeric(ActiveCell.Value)


'set 1st ID variable
myvar = ActiveCell.Value


Worksheets.Add
ActiveSheet.Name = myvar

 

[Fluff]

Try

With DataRange
   .AutoFilter Field:=3, Criteria1:=MyVar
   .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(CStr(MyVar)).Range("A1")
End With

 

[busby66]

That works!

It falls over on the next line of code ....
'autofit columns
Sheets(MyVar).Columns.EntireColumn.AutoFit

Which I rewrote as...
Sheets(CStr(MyVar)).Columns.EntireColumn.AutoFit which seems to work fine.

Many thanks for your help. Can you tell me what I was ding wrong?

 

[Fluff]

Because your sheet names are numbers, if "MyVar" was 10 Sheets(MyVar) is the same as Sheets(10), so VBA is trying to use the sheet index, not the sheet name.
By converting MyVar to a string, you are then using sheets("10") which is the sheet name.

 

[busby66]

Oh now I see...
Many thanks for passing on your knowledge & tips, much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback