Macro to create a new sheet and copy/paste data from another sheet in the new sheet

P

pyclen

Board Regular
Joined
Jan 17, 2022
Messages
85
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

I am trying to create a macro that would
  1. 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)
  2. After creation of the new sheet perform the following
    1. create a table like the one below
    2. copy the data in the two middle rows (Cost at time, micro cost at time) from the calculation sheet
    3. 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)
The caveats are as follows
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.

T0T1T2T3T4
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]
[/CODE]

Many thanks to anyone who takes a crack at this or can point me somewhere with a solution
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi pyclen

looking at your VBA, you have given "Sheet2" a name at the start, then asked it to paste everything into "Sheet2" which does not exist.
Add a sheet with a static name. Then, at the end change it to your dynamic name.
i.e.
VBA Code: 
Sub Add_New_Sheet
Sheets.Add.Name = "NEW"
' Refer to new sheet as "New" not "Sheet2" - Ctrl+F replace all is the fastest method

    Range("N9:AB9").Select
    Selection.Copy
    [B]Sheets("NEW").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("NEW").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("NEW").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

' Then change the sheet name based on cell reference just before End Sub

Sheets("NEW").Name = Range("C3")

End Sub

Hopefully the rest of your code does what you want, but this should sort out the sheet name issue.
 
Upvote 0
Hi dnorm,

appreciate the input and for the most part it does at least the sheet name issue however the macro hangs up here: Sheets("NEW").Name = Range("C3") (my reference is in B3, and I adapted that) with an error 1004, application - defined or object-defined error

My macro does create a new sheet called 'NEW' and the copied data is there but I am left with the error, and all other macros I have are not working until I stop the above
 
Upvote 0
I think I know what the problem is,
Sheets("NEW").Name = Range("C3") tries to name the sheet the same as an existing sheet and that fails.
what I'd like is to add the word summary or just sum before or after the name referenced in C3 (example here is INH Stability Calculation 2)

in other words I want the new sheet to be named SummaryINH Stability Calculation 2, or SumINH Stability Calculation 2, or INH Stability Calculation 2Sum, or INH Stability Calculation 2Summary
 
Upvote 0
pyclen said:
I think I know what the problem is,
Sheets("NEW").Name = Range("C3") tries to name the sheet the same as an existing sheet and that fails.
what I'd like is to add the word summary or just sum before or after the name referenced in C3 (example here is INH Stability Calculation 2)

in other words I want the new sheet to be named SummaryINH Stability Calculation 2, or SumINH Stability Calculation 2, or INH Stability Calculation 2Sum, or INH Stability Calculation 2Summary
Click to expand...
If you can, place your desired sheet name in another cell. Use the "CONCAT" formula to merge your B3 with another dynamic i.e. =CONCAT(B3,SHEETS()) This in theory will put a dynamic number on the end of B3 based on the number of sheets the workbook has (the formula " =SHEETS()" returns the number of worksheets in a workbook). So in theory every time you add a new sheet the number will increase so no clashes.
 
Upvote 0
OK change this line and try it
VBA Code: 
Sheets("NEW").Name = Range("C3")

Change it to..
VBA Code: 
Sheets("NEW").Name = "Sum " & Range("C3")

You can add or change "Sum" to whatever you want, but this should solve the clash error hopefully.
 
Upvote 0
Solution
dnorm said:
OK change this line and try it
VBA Code: 
Sheets("NEW").Name = Range("C3")

Change it to..
VBA Code: 
Sheets("NEW").Name = "Sum " & Range("C3")

You can add or change "Sum" to whatever you want, but this should solve the clash error hopefully.
Click to expand...
Thank you, that did the trick, at least for the naming and copying of data. And learned something again

Is there any way to make this dynamic as I outlined in my original post? Dynamic in the sense that it only copies the fields w/actual values onto a new sheet and leaves all others that are empty or have $0 out (not copy)
 
Upvote 0
There is, i have something like that running in my daily reports, but I'm not sure how to convert it to your requirements. It may be something like stating for each column ("i") along row 13 (as an example) >0 copy and paste. The vba would look a little like:

Dim i As Long
i = G to Z
For Each Range(i & "13").Value >0
... then the copy and paste function....
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
157
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
954
Joe4
Joe4
P
  • Solved
Counter in For Each loop
Replies
2
Views
193
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
305
Azamamir
A
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
314
kevin9999
K

Forum statistics

Threads
1,214,830
Messages
6,121,839
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top