Please help the newbie

[debschofield]

I'm very new to macros so please be gentle with me!

I'm trying to set up a series of worksheets to copy and paste raw data that is filtered from a main workbook into different cost centres and then update the various pivot tables. Unfortunately I don't think I have fully understood the way to write my code. I have tried to insert variable ranges for the copy and paste areas that will change each month but have then been getting various errors about objects and subscript and now the one i have captured in the image below

Sub CarLeasingPivot()' This is a copy of the recorded macro to test writing in VBA
'
' Im setting a dimension to automatically find the last free row in a data range
Dim lr As Long
' to get the last filled row in column A then the formula is
lr = Cells(Rows.Count, "A").End(xlUp).Row


'Print lr's value to the immediate Window
Debug.Print lr




Sheets("GL Raw Data").Range("A1:X" & lr).AutoFilter Field:=10, Criteria1:="505080" (this gets run time error 438 object doesn't support this property or method)


'The range below was originally the number of rows recorded in the macro but we need this to be dynamic so that
'it will change every month with the number of entries that are received.




Range("A1:U" & lr).Copy
Sheets("Car lease data").Range("A1" & lr).Paste
Sheets("Car Leases 505080").Select
ActiveWorkbook.RefreshAll
Sheets("Car lease data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Car Leases 505080").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("Header").Select



End Sub

Can anyone offer some advice??

Many thanks

Deb

 

[baitmaster]

welcome to the board


For a start you need to better understand the objects you are working with, and how best to refer to them

Every worksheet is an object. Each range within it is an object. VBA allows us to refer to just a Range, but if the worksheet it belongs to is not specified then it defaults to the activesheet - even if you have a different file active. So when you wrote

[COLOR=#333333]lr = Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]

what VBA saw was

[COLOR=#333333]lr = activesheet.Cells(activesheet.Rows.Count, "A").End(xlUp).Row[/COLOR]

When you use this variable later and refer to a worksheet, you risk referring to a range on one sheet that is linked to the size of another

Now look at the VBA Project window, and you will see two names against every worksheet. It probably says something like "Sheet1 (Car Lease Data)" and similar for every worksheet. Sheet1 in this case is the worksheet.codename, and is the name of the worksheet object. Referring to this is much better than referring to the worksheet.name, which can be changed in Excel

This helps us correct a common mistake you are making, where you [object].SELECT in order to create a SELECTION object that you can play with. No need to do this in most cases - you usually only need to select a different worksheet when messing with limited features such as freezepanes

Examples

[COLOR=#333333]shtCarLeaseData[/COLOR][COLOR=#333333].Visible = False   ' I've assumed you rename you worksheet to do this[/COLOR]

I also like to create my object and before working with it, which helps when debugging why something doesn't work

Dim rng as range
Set rng = shtGlRawData.Range("A1:X" & lr)
rng.parent.activate    ' only needed during debugging, helps us see where the object is
rng.select      ' again, only for debug purposes
rng.autofilter...   ' have you switched off any existing autofilter?

 

[debschofield]

Thank you so much for your help. I am going to try out your advice straight away

Deb