Hi,
I am trying to create a macro that would
1. the data originates from a different tab (Calculation tab, and stored/shown as below, i.e., horizontally)
2. the data is dynamic, in other words there can be more data (T0 - T8 for example) or less data (T0 - T4 as an example)
3. I want the table formatted w/specific font, all centered, and the totals bolded (less critical/important)
The below is a simplified example but captures what I want to do.
I found a procedure to create a new sheet based on a cell reference (below)
Afterwards I recorded a separate macro where I created the table and copied the data, formatted etc. myself.
And in of of itself the procedure works, I create a new tab (manually or w/above macro), create the table, copy/paste the data, sum etc. but it is not automatic.
And when I try to combine the 2 macros or when I inserted the code above I get a debug error here
Sheets("Sheet2").Select
I understand why that is however I do not know where/how to fix it and it is also not dynamic as the data range can vary.
[/CODE]
Many thanks to anyone who takes a crack at this or can point me somewhere with a solution
I am trying to create a macro that would
- create a new sheet based on a cell reference with the addition of summary (or Sum) either before or after the cell reference (that would be Cell B3)
what I mean is, the sheet name is Calculation and it is stored in cell B3 (name is pulled via macro) and , then the new sheet name needs to be Calculation Sum(mary) - After creation of the new sheet perform the following
- create a table like the one below
- copy the data in the two middle rows (Cost at time, micro cost at time) from the calculation sheet
- sum up the two rows for each time point, sum up each of the two rows ($65, $14), and again a total sum of both rows (here $79)
1. the data originates from a different tab (Calculation tab, and stored/shown as below, i.e., horizontally)
2. the data is dynamic, in other words there can be more data (T0 - T8 for example) or less data (T0 - T4 as an example)
3. I want the table formatted w/specific font, all centered, and the totals bolded (less critical/important)
The below is a simplified example but captures what I want to do.
T0 | T1 | T2 | T3 | T4 | ||
Cost at time | $5 | $20 | $20 | $10 | $10 | $65 |
Micro Cost at time | $2 | $4 | $4 | $2 | $2 | $14 |
Total | $7 | $24 | $24 | $12 | $12 | $79 |
I found a procedure to create a new sheet based on a cell reference (below)
VBA Code:
Sub Add()
Sheets.Add.Name = Range("c3").Value
End Sub
Afterwards I recorded a separate macro where I created the table and copied the data, formatted etc. myself.
And in of of itself the procedure works, I create a new tab (manually or w/above macro), create the table, copy/paste the data, sum etc. but it is not automatic.
And when I try to combine the 2 macros or when I inserted the code above I get a debug error here
Sheets("Sheet2").Select
I understand why that is however I do not know where/how to fix it and it is also not dynamic as the data range can vary.
VBA Code:
Sub CopyStabilityData()
'
'
' Sheets.Add.Name = Range("c3").Value
Range("N9:AB9").Select
Selection.Copy
[B]Sheets("Sheet2").Select[/B]
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("INH Stability Calculation 2").Select
Range("N35:AB35").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("INH Stability Calculation 2").Select
Range("N40:AB40").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Cost"
Range("D7").Select
ActiveCell.FormulaR1C1 = "Micro"
Range("E8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("E8").Select
Selection.AutoFill Destination:=Range("E8:T8"), Type:=xlFillDefault
Range("E8:T8").Select
Range("T6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[-15]:RC[-1])"
Range("T7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(RC[-15]:RC[-1])"
Range("T8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("T9").Select
End Sub
VBA Code:
[CODE=vba]
Many thanks to anyone who takes a crack at this or can point me somewhere with a solution