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

[pyclen]

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.

	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)

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.

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

[CODE=vba]

[/CODE]

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

 

[dnorm]

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.

 

[dnorm]

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).

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​	#	Supplier	Part Description	Part Number	Concern description	QTY 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:

 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.)

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

 

[pyclen]

Hi dnorm,

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