Autofill Macro for Rows and Columns

[jmac121233]

I've searched existing threads but haven't had much luck finding the answer I need.

I have a formula in cell B2 that I'm trying to copy across all columns (with data in Row 1) and down all rows (with data in Column A). The number of rows and columns is going to be constantly changing (for example, this week I may need to copy B2 from B2:G50, but next week it may be B2:J100). I've figured out how to copy the formula down every row, but cannot figure out how to copy the formula across all columns. Any help would be greatly appreciated!! Macro code is provided below:

Also, much less importantly, is there a way to make my vlookup range dynamic as well?

Sheets("One").Select
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,Data!R3C1:R33C17,HLOOKUP(R1C,Data!R1C1:R2C17,2,FALSE),FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)

 

[krishhi]

According to My understandings.

Copy the Formula and Press Ctrl+Shift+End then paste.

If my assumption is wrong, please provide a Sample file.

 

[jmac121233]

Because I'm going to have dozens of worksheets that this copy/paste needs to be done on, I don't want this to be a manual process. I'm looking to have the macro do it for me.

Sorry, I don't see the option to attach the workbook.

 

[jmac121233]

Has anybody had any luck setting a macro to autofill down all rows and then across all columns? I'm continuing to have no luck with my efforts to get this to work. Thanks

 

[krishhi]

Dude,

mail your sample spreadsheet to my id : krishi_984@yahoo.com

I will look into it.

 

[jmac121233]

Krishhi - Thanks for the offer to help! I FINALLY figured it out. The code I used is...

Sheets("One").Select
Dim LR As Long, LC As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 2), Cells(LR, LC)).Formula = "=VLOOKUP(RC1,Data!R3C1:R33C17,HLOOKUP(R1C,Data!R1C1:R2C17,2,FALSE),FALSE)"