Solved: Simple VBA change

[4elephants]

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.

[Bob Phillips]

Sub SheetSetUP()
Dim LastRow As Long

With Sheets("Main")

.Columns("J:J").Delete Shift:=xlToLeft
.Columns("G:G").Delete Shift:=xlToLeft
.Columns("B:B").Insert Shift:=xlToRight
.Range("B1").FormulaR1C1 = "Tab"
.Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[Accounts.xls]Sheet1'!R2C1:R65C5,5,0)"
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("B2").AutoFill Destination:=Range("B2").Resize(LastRow - 1)
With Range("B2").Resize(LastRow - 1)

.Value = .Value
End With
.Columns("B:B").Cut
.Columns("A:A").Insert Shift:=xlToRight
End With
End Sub

[4elephants]

Thanks for the rapid response XLD.

However, when I try to run this new code I get an error message Run-time error '1004': Autofill method of range class failed. If I click on debug it show me this line highlighted in yellow
.Range("B2").AutoFill Destination:=Range("B2").Resize(LastRow - 1)

Any ideas?

[Bob Phillips]

I missed a couple of dot qualifiers, but the problem is that we are trying to find the last row to fill down into in a blank column, the column B that you have just inserted. We need another column to key off of.WHich should it be?

[4elephants]

Hi Xld,

It can key off column A, as this is what the Vlookup will be matching to.

Thanks for taking the time to assist

[Bob Phillips]

Okay so try this



Sub SheetSetUP()
Dim LastRow As Long

With Sheets("Main")

.Columns("J:J").Delete Shift:=xlToLeft
.Columns("G:G").Delete Shift:=xlToLeft
.Columns("B:B").Insert Shift:=xlToRight
.Range("B1").FormulaR1C1 = "Tab"
.Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[Accounts.xls]Sheet1'!R2C1:R65C5,5,0)"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2").AutoFill Destination:=Range("B2").Resize(LastRow - 1)
With Range("B2").Resize(LastRow - 1)

.Value = .Value
End With
.Columns("B:B").Cut
.Columns("A:A").Insert Shift:=xlToRight
End With
End Sub

[Bob Phillips]

Small correction



Sub SheetSetUP()
Dim LastRow As Long

With Sheets("Main")

.Columns("J:J").Delete Shift:=xlToLeft
.Columns("G:G").Delete Shift:=xlToLeft
.Columns("B:B").Insert Shift:=xlToRight
.Range("B1").FormulaR1C1 = "Tab"
.Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[Accounts.xls]Sheet1'!R2C1:R65C5,5,0)"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2").AutoFill Destination:=.Range("B2").Resize(LastRow - 1)
With .Range("B2").Resize(LastRow - 1)

.Value = .Value
End With
.Columns("B:B").Cut
.Columns("A:A").Insert Shift:=xlToRight
End With
End Sub

[4elephants]

Thanks very much Xld, you are the man. That works perfectly, thanks for your time.