Formatting Macro Assistance

paulcianf

New member
Joined
Jul 24, 2014
Messages
3
Reaction score
0
Points
0
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:

Code:
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
 
Last edited by a moderator:

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Maybe this

Code:
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

New member
Joined
Jul 24, 2014
Messages
3
Reaction score
0
Points
0
@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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
I missed an s


Code:
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

New member
Joined
Jul 24, 2014
Messages
3
Reaction score
0
Points
0
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 VersionClientProject NameForecast TypeForecast (in days)Tracking #
IAP
2.3.1EX1Example 2Approved for Placeholder750 1855
No
00='Forecasting Module'!R[8]C[5]0000
00='Forecasting Module'!R[8]C[5]0000
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.
 
Top