Formatting Macro Assistance

[paulcianf]

Hi there- I have a question regarding my formatting macro. Bit of background: on one sheet I have created a module where the user selects a set of values (some in a list form) and enters in texts based off of 7 cells. Once those values are entered, the user then presses "submit". Now, what is supposed to happen is that the values transfer to another sheet where it lists their entries into a chart format. When I did the formatting macro, all I did was select a cell, press '=' click the previous sheet, select the cell it needs to copy to and press enter:

Range("B3").Select
Sheets("Forecasting Module").Select
Range("F12").Select

This is repeated 7 times for all values, then I stopped recording and assigned the macro to the button- and it works (kinda). My issue is that when I try to submit a second entry with different values, it overrides my previous entry, and I need it to continuously add on my second sheet. Any help is greatly appreciated! Here is the macro currently used:

Sub Forecasting1()
 '
 ' Forecasting1 Macro
 '

 '
    Range("B3").Select
    Sheets("Forecasting Module").Select
    Range("F12").Select
    Sheets("Test Macro").Select
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[3]C[2]"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[8]C[1]"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[3]C[4]"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[13]C[-2]"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[3]C[6]"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D3").Select
    Selection.NumberFormat = "General"
    Range("G3:H3").Select
    Selection.NumberFormat = "General"
    Range("E6").Select
 End Sub

 

[NoS]

are you trying to take 7 values from Sheets("Forecasting Module") and put them into the third row of Sheets("Test Macro") starting in column B?
if so, what are the 7 cell addresses on "Forecasting Module" ?

 

[Bob Phillips]

Maybe this

Sub Forecasting1()
Dim lastrow As Long

    With Worksheets("Test Macro")
    
        lastrow = .cell(.Rows.Count, "B").End(xlUp).Row
        .Cells(lastrow + 1, "B").FormulaR1C1 = "='Forecasting Module'!R[3]C[2]"
        .Cells(lastrow + 1, "C").FormulaR1C1 = "='Forecasting Module'!R[8]C[1]"
        .Cells(lastrow + 1, "D").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
        .Cells(lastrow + 1, "E").FormulaR1C1 = "='Forecasting Module'!R[3]C[4]"
        .Cells(lastrow + 1, "F").FormulaR1C1 = "='Forecasting Module'!R[13]C[-2]"
        .Cells(lastrow + 1, "G").FormulaR1C1 = "='Forecasting Module'!R[3]C[6]"
        .Cells(lastrow + 1, "H").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
    End With
End Sub

 

[paulcianf]

@NoS: Correct, the 7 values from sheet “Forecasting Module” will transfer to sheet “Test Macro” starting on cell B to cell H once I press the submit button. The issue is that the macro does not build on “Test Macro” if I submit something else, it just overwrites my previous submission.

@Bob Phillips: Thanks for the code. Unfortunately, I am receiving a run time error 438 “object doesn't support this property or method”. Any idea how to debug?
Paul

 

[Bob Phillips]

I missed an s

Sub Forecasting1()
Dim lastrow As Long

    With Worksheets("Test Macro")

        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Cells(lastrow + 1, "B").FormulaR1C1 = "='Forecasting Module'!R[3]C[2]"
        .Cells(lastrow + 1, "C").FormulaR1C1 = "='Forecasting Module'!R[8]C[1]"
        .Cells(lastrow + 1, "D").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
        .Cells(lastrow + 1, "E").FormulaR1C1 = "='Forecasting Module'!R[3]C[4]"
        .Cells(lastrow + 1, "F").FormulaR1C1 = "='Forecasting Module'!R[13]C[-2]"
        .Cells(lastrow + 1, "G").FormulaR1C1 = "='Forecasting Module'!R[3]C[6]"
        .Cells(lastrow + 1, "H").FormulaR1C1 = "='Forecasting Module'!R[8]C[5]"
    End With
End Sub

 

[paulcianf]

Thanks again, it worked but I am still having some issues. The macro is working in the sense that it is building on my "Test Macro" sheet. However, when I submit a second or third forecast, the previous one turns to 0's. Here is a paste of what is occurring: (note: that the top line is the most recent submission, and the bottom 2 were the previous ones.) Appreciate the help.

FORECASTING TRACKER
Target Version	Client	Project Name	Forecast Type	Forecast (in days)	Tracking #	IAP
2.3.1	EX1	Example 2	Approved for Placeholder	750	1855	No
0	0	='Forecasting Module'!R[8]C[5]	0	0	0	0
0	0	='Forecasting Module'!R[8]C[5]	0	0	0	0

 

[NoS]

Paul, in my first post I asked what those 7 addresses were. Reason for that was that you write 7 times, but the third and seventh appear to be the same.

To write to the top of your data every time, go back to what you posted originally and change Rows("4:4") back to Rows("3:3"), the way you had it in your first posting of this question at another forum.