Drag down formula based on the last row of a another sheet

Question

Hope someone can help me :)

I have data in sheet A, and formula's in sheet B. The formula's in sheet B are only in row 2.

So if sheet A has 33 rows filled with data, I switch to sheet B and drag the data in row 2 down to 33 to match it with sheet A.

Is it possible to do this with VBA?

I also tried just copying them over but in sheet B, but they are in a different order, so that's not really a solution for me.

What I tried is:

Private Sub Worksheet_Activate()
Range("A2:N2").Select
Selection.AutoFill Destination:=Range("A2:N" & Lastrow), Type:=xlFillDefault
End Sub

score 0 · Accepted Answer · answered Jun 18 '21 at 23:19

0

No need to use AutoFill

LOGIC:

Find the last row in Sheet A
Fill the formula using .Formula in the entire range in one go.

CODE:

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim lRow As Long
    
    '~~> Set these to relevant sheets
    Set wsA = Sheet1
    Set wsB = Sheet2
    
    '~~> Find last row in Sheet A
    lRow = wsA.Range("A" & wsA.Rows.Count).End(xlUp).Row
    
    '~~> Update formulas in Sheet B till relevant rows
    wsB.Range("A2:N" & lRow).Formula = wsB.Range("A2:N2").Formula
End Sub

NOTE:

Also you may want to read up on How to avoid using Select in Excel VBA

answered Jun 18 '21 at 23:19

Siddharth Rout

147,039
17
206
250

Hello, I keep getting the error message variable is not defined :S – Ultimate Processor Jun 18 '21 at 23:40
That is because of `Option Explicit`. You have some variable which you have not defined in your code. See how I have defined the worksheet objects and the lRow variable using `Dim`? – Siddharth Rout Jun 18 '21 at 23:43
But I'm getting the error with Sheet1 and Sheet2. – Ultimate Processor Jun 18 '21 at 23:47
But why did you use that? There is a reason why I have commented the code :) `'~~> Set these to relevant sheets` – Siddharth Rout Jun 18 '21 at 23:48
I did with ("NameofSheetA") and ("NameofSheetB") – Ultimate Processor Jun 18 '21 at 23:49
`Set wsA =Sheets("YourSheetName")` – Siddharth Rout Jun 18 '21 at 23:51
Thank you, Siddharth it works really well! I do have a follow-up question about it, I'm using it in a Private Sub Worksheet_Activate(), so when I add new information in SheetA and go back to SheetB it does not show it in SheetB (the new added data). – Ultimate Processor Jun 19 '21 at 00:06
You need to use it in `Private Sub Worksheet_Change(ByVal Target As Range)` of `SheetA`. This event will fire when ever there is a change in the worksheet. I would recommend reading up on it. Also save your work before you work with `Worksheet_Change`. It's almost 6AM. I am off to bed. Feel free to post followup questions and I will post back later in the day. – Siddharth Rout Jun 19 '21 at 00:09
Yes thank you so much I will try some things and then also go to sleep T_T – Ultimate Processor Jun 19 '21 at 00:16
Hello! So it works but for SheetB it always shows data till row 21 and not further for some reason. – Ultimate Processor Jun 19 '21 at 10:36
**1.** did you change anything in the code besides the sheet names? **2.** Check SheetA. Is the last row 21? – Siddharth Rout Jun 19 '21 at 13:13
1. No I didn't 2. No – Ultimate Processor Jun 20 '21 at 06:32
Tested it again for some reason it always stops at row 21. – Ultimate Processor Jun 20 '21 at 06:38
I changed to Colum from A to B (because A is empty, now it matched the total columns in A to B, but if I have 34 rows in A, then in B I see the 34 rows + it goes on till row 234. – Ultimate Processor Jun 20 '21 at 06:50
Hi Siddarth, I was using it on the wrong sheet the code hehe it works now thanks really much again! – Ultimate Processor Jun 20 '21 at 07:06

Drag down formula based on the last row of a another sheet

1 Answers1