4elephants
03-12-2008, 12:34 PM
Good evening All,
I'm a newbie trying to teach myself VBA, but I'm not sure what I need to do to correct this macro.
The macro was created by recording my actions, and basically deletes a couple of columns, add a column, enters a Vlookup formula into this column, copies it down to all rows then copy paste values this column and moves it.
What I need to do is alter this macro because the number of rows will be different every week, and currently it stops at row 8545 so I end up with rows with nothing in (if there are more rows) or I get N/A's (if there are less rows).
Could any of you VBA experts take a look at my code and suggest a change that wil calculate the number of rows and adjust the formula accordingly.
Sub SheetSetUP()
'
' SheetSetUP Macro
' Macro recorded 10/03/2008 by user
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("Main").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Tab"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Accounts.xls]Sheet1'!R2C1:R65C5,5,0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B8545")
Range("B2:B8545").Select
Range("B2:B8545").Copy
Range("B2:B8545").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
End Sub
Thanks very much for your time.
I'm a newbie trying to teach myself VBA, but I'm not sure what I need to do to correct this macro.
The macro was created by recording my actions, and basically deletes a couple of columns, add a column, enters a Vlookup formula into this column, copies it down to all rows then copy paste values this column and moves it.
What I need to do is alter this macro because the number of rows will be different every week, and currently it stops at row 8545 so I end up with rows with nothing in (if there are more rows) or I get N/A's (if there are less rows).
Could any of you VBA experts take a look at my code and suggest a change that wil calculate the number of rows and adjust the formula accordingly.
Sub SheetSetUP()
'
' SheetSetUP Macro
' Macro recorded 10/03/2008 by user
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("Main").Select
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Tab"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Accounts.xls]Sheet1'!R2C1:R65C5,5,0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B8545")
Range("B2:B8545").Select
Range("B2:B8545").Copy
Range("B2:B8545").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
End Sub
Thanks very much for your time.