How to copy formulas down an actual used range (rows and/or columns)

gguevara

New Member
Joined
Sep 3, 2014
Messages
10
Hello forum,
This is my first post.

In worksheet1 I have a set of data that is arranged in a normal layout (lets say it has 12 columns, with respective headers on top row). The column arrangement stays the same every month, but the number of rows changes every month (or day). One month it can be 400 rows, and another month it can be 249 rows. So for example, I have a data range from A2:L400 (minus the header row on top)

In worksheet2 I have a set of formulas that reference the 1st worksheet - arranged in one row and across columns (lets say row 4, and across 7 columns, because I only care about 7 out of the 12 columns from the 1st worksheet). So for example, I have a set of locked formulas from $A4:$G4.

I have an empty worksheet3. I am looking for a macro that will copy my row of formulas from the 2nd worksheet into the 3rd worksheet (lets say into row 4). And then copy/drag the formulas down, by the equal amount of actual rows in the 1st worksheet (400 rows, minus the header row). So for example, I will end up with resulting values from A4:G403. Remember that the number of rows in the 1st worksheet can/will change every month. So the macro needs to be "smart" enough to copy/drag the correct amount of actual rows.

Ultimately I then want to copy the resulting values over itself as paste-values. In other words, replace the formulas with the resulting values (in order to reduce the file size, and continue to further manipulate this 3rd worksheet later - if need be).

I am new to VBA macro coding. I can somewhat read/interpret the code and tweak it to fit basic customization - but I can not yet create the more complex codes from scratch.

Can someone please help me. Any help will be greatly appreciated.

Thanks, and keep up all the great work.

gg
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

tyger0951

Board Regular
Joined
Aug 9, 2014
Messages
103
Heres a small bit of code to move all formulas from Column A in sheet 2 to column A of Sheet 3.

Code:
Sub test ()

Dim LastRowColA as Integer
Dim  SLastRowColA as Integer 
Dim cell As Range 

LastRowColA = Sheets(2).Range("A" & Rows.Count).End(xlup).rows  ' Returns Number of Last Filled Cell in Col A 

For each Cell in Sheets(2).Range("A2",Range("A" & LastRowColA))

If Cell.HasFormula Then 

SLastRowColA = Sheets (3).Range("A" & Rows.Count).End(xlup).rows

Sheets(3).Range("A" & SLastRowColA).Offset(1,0).Formula = Cell.Formula
end if 

Next  

End Sub

Or

Code:
Sub CopyOnlyFormulas()


Sheets(2).UsedRange.Copy
Sheets(3).Cells.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone


    For Each cell In Sheets(3).UsedRange
        If Not cell.HasFormula Then
            cell.Clear
        End If
    Next


End Sub




Sub CopyDataAndFormulas()
Sheets(2).UsedRange.Copy
Sheets(3).Cells.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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