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

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
 
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....
I will have a look tomorrow when I'm back at work and see what I can come up with.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
HI Pyclen

I know this is not a solution, but I hope it helps you understand vba a little more:

This is the vba code I use. This looks to see if there is an entry in the rows then combines them and placing all entries in to one cell (Info Box).

VBA Code:
Sub supplier_issues()
'
'Compiles info from the Supplier Issues tab
'

Dim info_box As Variant
Dim i As Long
Sheets("5. Supplier Issues").Select

    For i = 4 To 13
        If Range("B" & i).Value <> "" Then
            info_box = info_box & Range("A" & i).Value & ". " & Range("B" & i).Value & " - " & Range("C" & i).Value & " - " & Range("E" & i).Value & " - rejected QTY " & Range("F" & i).Value & vbCrLf
        End If
        
    Next
    
If info_box = "" Then

    info_box = "No supplier issues reported"

End If

    
Sheets("Report").Select

Range("S_supplier_issues_details") = info_box

End Sub

Here "i" is the variable that makes the vba look through all rows from 4 - 13. "vbCrlf" on the end is a return call/ next line to convert each row in to another line in the info box. this is the format of the info source sheet:

A​
B​
C​
D​
E​
F​
3​
#SupplierPart DescriptionPart NumberConcern descriptionQTY advised
4​
1
5​
2
6​
3
7​
4
8​
5
9​
6
10​
7
11​
8
12​
9
13​
10

This is where I am stuck, You could try something like this, but I have no clue if it would work:

VBA Code:
 For i = B To Z
        If Range(i & "13").Value >0 Then
            info_box = info_box & Range(i & "13").Value & ". " & Range(i & "13").Value & " - " & Range(i & "13".Value & " - " & Range(i & "13").Value & " - rejected QTY " & Range(i & "13").Value & vbCrLf
        End If
        
    Next

I can only recommend that you try a search through this sites database. I found this that might help:
VBA Macro to copy and paste certian cells to another sheet based on another cell value

The person here is asking for a similar solution.
NOTE: The "Application.ScreenUpdating = True/False" lines in his code help speed it up and well worth adding to any code you may have. Place the false at the start and true at the end.
also to help you with keeping things easier you can "call" other vba routines in a single vba i.e. (This is a test vba that I use to trial changes.)

VBA Code:
Sub run_test()

    'Runs all macros in given order at button push
    'No saving. No sending. No KPI. No closing.
    '
        
    find_replace 'This is a vba that replaces set cells with the required formulas
    
    dnorm 'This is one this site helped me out with
    
    fill_Cust 'this vba copies info in to empty cells based on the value of another cell
    
    Pivot 'This controls 5 pivot tables and their updates and filtering
    
    Windows("Master Report.xlsb").Activate
    
Sheets("4. Customer Complaints").Select
    customer_complaints 'this is a carbon copy of the one below
    
Sheets("5. Supplier Issues").Select
    supplier_issues 'This is the vba mentioned above. 
    
Sheets("Report").Select

ActiveSheet.PivotTables("Common").PivotCache.Refresh

    Failure_Mode 'this is a pivot table update, filter and copy values vba
    
    
End Sub
 
Upvote 0
Hi dnorm,

thanks again for your help, and I will check out the thread mentioned as well as this forum
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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