Excel VBA - Copy down formulas based on number of rows on different sheet same workbook

ArtyS20

New Member
Joined
Oct 1, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello:

I have a workbook with 2 sheets. Sheet 1 has raw data with variable number of rows (including a header row). Sheet 2 has different formulas based on Sheet 1. It has a header row and row 2 has all the formulas I want to copy down to as many rows as Sheet 1 has. So if Sheet 1 has 30 rows, I would copy down on Sheet 2 formula row which is on column A row 2 to Column A row #30. So far this is what I have but the final step is incomplete as it selects the range but it only copies the formula row to one row and not to the entire range:

Sub Output()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("RawData")

Dim nrows As Long

nrows = sh.Range("A1", sh.Range("A1").End(xlDown).End(xlDown).End(xlUp)).Rows.Count

Sheets("Formulas").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Copy
Sheets("Formulas").Rows("3:" & nrows).Select
Selection.PasteSpecial

End Sub
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,543
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Output()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("RawData")

Dim nrows As Long

nrows = sh.Range("A&rows.count").End(xlUp).Row

Sheets("Formulas").Select
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(nrows).FillDown
End Sub
 

ArtyS20

New Member
Joined
Oct 1, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
How about
VBA Code:
Sub Output()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("RawData")

Dim nrows As Long

nrows = sh.Range("A&rows.count").End(xlUp).Row

Sheets("Formulas").Select
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(nrows).FillDown
End Sub
Thanks Fluff, this worked with a small change:

Sub Output()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("RawData")

Dim nrows As Long

nrows = sh.Range("A1", sh.Range("A1").End(xlDown).End(xlDown).End(xlUp)).Rows.Count

Sheets("Formulas").Select
Range("A2", Cells(2, Columns.Count).End(xlToLeft)).Resize(nrows - 1).FillDown
End Sub

I was getting an error at nrows - Method 'Range of object Worksheet' failed

Thanks for your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,543
Office Version
  1. 365
Platform
  1. Windows
Oops that should have been
VBA Code:
nrows = sh.Range("A" & rows.count).End(xlUp).Row
Had the Quotes in the wrong place.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top