A few words of thanks would be greatly appreciated.

Use a Macro to Create New Workbook and Copy Data in Excel

Latest update on June 3, 2020 at 11:29 AM by Aoife McCambridge .

Often we need to carry out manipulations in Excel documents and apply them to multiple rows, columns and sheets. To do this manually takes too much time and allows for errors to creep in, so Excel offers the option of Macros, which carry out data manipulation operations for you. In this article we will show you how to use a pre-existing Macro to create a new workbook and copy data into a new spreadsheet.

Use a Macro to Create a New Workbook and Copy Data

1. Make a backup of the spreadsheet
2. Open the work book
3. Press ALT + F11 to open VBE
4. Go to Insert > Module
5. Copy and paste the following code into the module:

Sub details()    
Dim thisWB  As String    
Dim newWB As String    
thisWB = ActiveWorkbook.Name    
On Error Resume Next    
    Sheets("tempsheet").Delete    
    On Error GoTo 0    
Sheets.Add    
    ActiveSheet.Name = "tempsheet"    
Sheets("Sheet1").Select    
If ActiveSheet.AutoFilterMode Then    
        Cells.Select    
On Error Resume Next    
ActiveSheet.ShowAllData    
On Error GoTo 0    
End If    
Columns("B:B").Select    
    Selection.Copy    
Sheets("tempsheet").Select    
    Range("A1").Select    
    ActiveSheet.Paste    
    Application.CutCopyMode = False    
If (Cells(1, 1) = "") Then    
        lastrow = Cells(1, 1).End(xlDown).Row    
If lastrow <> Rows.Count Then    
            Range("A1:A" & lastrow - 1).Select    
            Selection.Delete Shift:=xlUp    
        End If    
End If    
Columns("A:A").Select    
    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _    
                CopyToRange:=Range("B1"), Unique:=True    
Columns("A:A").Delete    
Cells.Select    
    Selection.Sort _    
            Key1:=Range("A2"), Order1:=xlAscending, _    
            Header:=xlYes, OrderCustom:=1, _    
            MatchCase:=False, Orientation:=xlTopToBottom, _    
            DataOption1:=xlSortNormal    
lMaxSupp = Cells(Rows.Count, 1).End(xlUp).Row    
For suppno = 2 To lMaxSupp    
Windows(thisWB).Activate    
supName = Sheets("tempsheet").Range("A" & suppno)    
If supName <> "" Then    
Workbooks.Add    
            ActiveWorkbook.SaveAs supName    
            newWB = ActiveWorkbook.Name    
Windows(thisWB).Activate    
Sheets("Sheet1").Select    
            Cells.Select    
If ActiveSheet.AutoFilterMode = False Then    
                Selection.AutoFilter    
            End If    
Selection.AutoFilter Field:=2, Criteria1:="=" & supName, _    
                        Operator:=xlAnd, Criteria2:="<>"    
lastrow = Cells(Rows.Count, 2).End(xlUp).Row    
Rows("1:" & lastrow).Copy    
Windows(newWB).Activate    
            ActiveSheet.Paste    
ActiveWorkbook.Save    
            ActiveWorkbook.Close    
End If    
Next    
Sheets("tempsheet").Delete    
Sheets("Sheet1").Select    
    If ActiveSheet.AutoFilterMode Then    
        Cells.Select    
        ActiveSheet.ShowAllData    
    End If    
End Sub

6. Press F5 to run the macro
7. Check it has worked

Image © Dzmitry Kliapitski - 123rf.com

Excel vba create new workbook and copy data
Macro to create new workbook and copy data - Best answers
Excel macro create new workbook - Best answers
Excel - A macro to create new workbook and copy data ✓ - Forum - Excel
Macro to create new workbook and copy data ✓ - Forum - Excel
VBA Copy sheet to new Excel File + automated manual data entry ✓ - Forum - Excel
Macro to Create New Workbook and Copy Data at Each Change of X - Forum - Excel
Excel vba open another workbook and copy data - How-To - Excel

This document, titled « Use a Macro to Create New Workbook and Copy Data in Excel », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Excel - Using integers as cell reference

Excel - Compare column A & B give results in column C

Use a Macro to Create a New Workbook and Copy Data

Subscribe To Our Newsletter!