Copy formulas on one sheet dynamically sized based on another sheet

Question

I have a workbook with two sheets.

Sheet1 gets the raw data (which will always have a different number of columns). I rearrange the columns of the raw data as they to may not be in the same order to "normalize" them. Column CE has a formula that returns True or False.

Sheet2 has formulas that extract certain data from the first sheet.

Formulas on Sheet2:

Cell A2: IF(Sheet1!CE2,Sheet1!A2,"")
Cell B2: IF(Sheet1!CE2,Sheet1!B2,"")
Cell C2: IF(Sheet1!CE2,Sheet1!C2,"")
etc.

If I put those formulas in Sheet2 and copy down, when I rearrange the columns on Sheet1, the Sheet2 formulas are changed accordingly.

So I have a macro to write all the formulas on Row 2 of Sheet2 AFTER the Sheet1 columns are rearranged.

I have a function that finds the last row of Sheet1.

I want to copy the Sheet2 formulas down the same number of rows that are in Sheet1.

'All of the formulas are in row1
lastRow = GetLastRow() 'This successfully retrieves a Long with the last row# of Sheet1

'Be sure am on correct sheet
Sheets("Sheet2").Select

'Copy the formulas
Range("A2:O2").Select
Selection.Copy

'Paste the formulas to the range
'THIS BLOWS UP
Sheet2.Range(startCell, ws1.Cells(lastRow, "O")).Select
ActiveSheet.Paste

urdearboy urdearboy · Accepted Answer · 2018-10-26T14:28:46

Here is a generic example of how to fill a range with an equation. This will just look at Column A on both sheets.

I am using Sheet1 Column A to determine the last row. Change this as needed. Update the columns wherever needed and duplicate the .Formula statement as many times as needed to get your desired result

Notice that you do not need to .Select anything to modify it. VBA is indifferent to the active sheet when you properly qualify your objects.

Option Explicit

Sub PhilNye()

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")

Dim LRow As Long
LRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row

ws2.Range("A2:A" & LRow).Formula = "=IF(Sheet1!CE2, Sheet1!A2,"""")" '<-- Duplicate This Statement

End Sub

Copy formulas on one sheet dynamically sized based on another sheet

2 Answers