Add mutiple worksheet in new workbook and copy & paste data value and format from another worksheet

[arunsjain]

Hi,

Following code is adding workbook (wb2) and add worksheet subsequently. Copy data from Wb1 and paste in Wb2 in added new worksheet but not able to paste format of wb1.

Please make changes in following code to simplify it as well.

Highly appreciate your help.

Sub Summary()
    
    Dim x As Integer
    Dim lRow
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    
    Set wb1 = ActiveWorkbook
    Set wb2 = Application.Workbooks.Add
    
    lRow = wb1.Worksheets("List").Cells(Rows.Count, "A").End(xlUp).Row
    
     Application.ScreenUpdating = False
        
      For x = 2 To lRow
        
        wb1.Worksheets("Calculator").Range("C2").Value = wb1.Worksheets("List").Cells(x, "C").Value
        wb1.Worksheets("Calculator").Range("C3").Value = wb1.Worksheets("List").Cells(x, "D").Value
        wb1.Worksheets("Calculator").Range("C4").Value = wb1.Worksheets("List").Cells(x, "E").Value
        wb1.Worksheets("Calculator").Range("C5").Value = wb1.Worksheets("List").Cells(x, "F").Value
        wb1.Worksheets("Calculator").Range("C6").Value = wb1.Worksheets("List").Cells(x, "G").Value
        wb1.Worksheets("Calculator").Range("C7").Value = wb1.Worksheets("List").Cells(x, "H").Value
        wb1.Worksheets("Calculator").Range("C8").Value = wb1.Worksheets("List").Cells(x, "I").Value
        wb1.Worksheets("Calculator").Range("C9").Value = wb1.Worksheets("List").Cells(x, "J").Value
        wb1.Worksheets("Calculator").Range("C10").Value = wb1.Worksheets("List").Cells(x, "K").Value
                                                     
      
        If wb1.Worksheets("Calculator").Range("C5").Value <> 0 Then
      
             wb1.Worksheets("Calculator").Range("A1:W45").Copy
             wb2.Worksheets.Add
              
             With wb2.ActiveSheet
            .PasteSpecial xlPasteValuesAndNumberFormats
            End With
        
        Else
            GoTo nextiteration
        End If
                
nextiteration:
                            
    Next x
     
    Application.CutCopyMode = False
     
    Application.ScreenUpdating = True
End Sub

Kind Regards
Arun