VBA to copy formula from one cell to variable range

[rasinc]

I am trying to copy a cell formula from one cell to a column that starts at cell G6 but is variable length.

I created a macro and the copy worked but with a variable number of rows, the code created may not work.

So I have no idea how to come up with the formula for the destination in the following code

    Sheets("Setup").Select
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("PriceLists").Select
    Range("G6").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("G6:G90")  'here I believe may be the issue
    Range("G6:G90").Select

So I want to be able to copy but down as many rows as may be there. However, I do not know how to determine the range to copy based on what is in column F.

Any help is appreciated.

TIA, rasinc

 

[AM2014]

I am a little less familiar with this code, but I believe you are suggesting I replace:

LR = Range("A" & Rows.Count).End(xlDown).Row

with:

LR = Range(strStartCol & Rows.Count).End(xlUp).Row

which gives me the final code:

    'Paste in Due/Ready formulas and formatting
    Range("C6:M6").Select
    Selection.Copy
    Range("C6").Select
    Selection.End(xlDown).Offset(1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    'Pastes format into last row
    Range("B6:M6").Select
    Selection.Copy
    Range("M6").Select
    Selection.End(xlDown).Offset(0, -11).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Copies down formulas on Orders tab
    Windows("CAL.CSV").Activate
    Range("C1").Select
    Dim LR As Long
    LR = Range(strStartCol & Rows.Count).End(xlUp).Row
    Windows("PFP.xlsm").Activate
    Range("M6").Select
    Range(ActiveCell, Cells(LR, ActiveCell.Row)).FillDown

Is that correct? When I run this macro it gets hung up on the portion that I changed. I think I am adding this in at the wrong part.

 

[rasinc]

I am a little less familiar with this code, but I believe you are suggesting I replace:
Is that correct? When I run this macro it gets hung up on the portion that I changed. I think I am adding this in at the wrong part.

No sorry. I was answering based on the original topic of the thread, where I posted a question that asked how to copy a formula in a cell to cells in a variable number of rows. eg. copy the formula in C1 to D5:D10.

Your question is different and should probably be in a different message thread. Now that I am reading it more carefully, you want to look at the calc.csv file for the number of rows. Then copy a formula in pfp.xlsm down the same number of rows from a starting point.

Could this be accomplished by knowing what your range selection and final destination are and then after activating pfp.xlsm

Selection.Copy
Range("A" & row).PasteSpecial Paste:=xlPasteAll

Just change Range("A" & row) to what ever destination you want. Change the Paste:= to formulas, formats, etc. as needed.

Now if in fact you are attempting to count the number of rows in calc.csv and that number today happens to be three, then you want to paste the cells M6:M10 three times, you would need a loop to do the PasteSpecial three times and Range("A" & row) would then be "A" & row and on each iteration of the loop, add the number of rows to row so each paste of the range starts x rows down from the first. This would then start each set at M6, M11 and M16.

Tomorrow calc.csv might contain 15 rows, so the loop would function 15 times to copy the range down that many times.

 

[SydneyGeek]

I'm guessing that the LR bit isn't working correctly, and this snippet could be the reason.
Change

[COLOR=#333333]LR = Range("A" & Rows.Count).End(xlDown).Row[/COLOR]

to

LR = Range("A" & Rows.Count).End(xlUp).Row

See if that fixes the problem.

Denis