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

[gguevara]

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

 

[tyger0951]

Heres a small bit of code to move all formulas from Column A in sheet 2 to column A of Sheet 3.

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

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

 

[gguevara]

Thanks Tyger.
I will give it a shot and let you know how it goes.

gg