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" ?
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 Bob Phillips; 2014-07-25 at 12:19 PM. Reason: Added VBA tags
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" ?
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
@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
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
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
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.
Bookmarks