Excel Template, macro to create new spreadsheet (2022)

Have a procedure that is being used where large amounts of data are copied from Excel template to individual templates for users.

Wrote a macro to perform this task, worked perfectly for a few minutes, and then the formatting was off for the individual spreadsheet; the individual performing is again copying and pasting.

Other than picking up empty spaces in the macro, what else could be wrong?

Here is the macro:

'This macro copies all visible data from the source file to the create file, removes any excess rows/columns/formatting,
' deletes any sheets from the create file if no data is present, and renames the create file to the manager's name
' so that it's ready to be mailed to them
'Ranges need to be modified for each new set of data. They have been included in the comments for convenince for using
' find and replace throughout this page. A2 should always been the starting point for manager data. If
' multiple headers exist within a single sheet, delete all but 1 row for this macro to function properly.
' Only the end range should need to be changed. This should indicate the last row containing data for each sheet.
' Ranges in this function need to be updated to match the ranges called in each sub
'Vista Range("A2:A18210")
'EAS Range("A2:A34785")
'EFC Range("A2:A8265")
'INAS Range("A2:A1886")
'Vertex Range("A2:A109")
'Oracle Range("A2:A13018")
'Oracle CCVA Range("A2:A619")
'Oracle EFDW Range("A2:A8736")
'UNIX Server Range("A2:A2542")
'UNIX EFDW Range("A2:A301")
'Count Sub, located at the bottom can be called to check validity of results, but the each page will require a manual
' click to continue

Sub CopyResults()

'set variables
Dim x As Workbook
Dim y As Workbook

'set workbook locations and open new workbook. Source Data.xlsx and Create File.xlsx should be in the same folder
Set y = Workbooks.Open("C:\Users\ox723d\Desktop\Finance Audit\Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

'call subs, 1 for each tab - loop could possibly be built in the future to reduce code
Sub1
Sub2
Sub3
Sub4
Sub5
Sub6
Sub7
Sub8
Sub9
Sub10

'activates created workbook
Workbooks("Create File.xlsx").Activate

'removes excess blank rows (to reduce file size)
For Each wks In y.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
'sets last row
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
'sets last column
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks

'##calls Count macro. Uncomment if needing to run. (remove ' from next line)
'Count

(Video) Create a Macro to Save Each Worksheets as New Excel File | Excel VBA Tutorial

'automatically select no to save over existing file. Since the autofilter picks up multiple name entries
' multiple files don't need to be saved. All data will exist on single file
Application.DisplayAlerts = False
'saves filtered workbook as manager's name
ActiveWorkbook.SaveAs Filename:="C:\Users\ox723d\Desktop\Finance Audit\Manager Files\" & Range("A3")
'closes new filtered workbook
ActiveWorkbook.Close SaveChanges:=False
'reactivates Overwrite save prompt incase of another possible issue (just for error checking redundancy)
Application.DisplayAlerts = True
'activates manager's list tab to perform next run
x.Sheets("Manager List").Activate

End Sub
'Vista Application Tab
Sub Sub1()
'assign variables and sets workbook names
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("Vista Application")
'activate sheet
x.Sheets("Vista Application").Activate
'set range from 2nd row to last row to contain data (This range will need to be altered for each new set of source data)
Range("A2:A18210").Select
'if there is no visible data to select, skips copy and paste function and delete the tab on the new file
On Error GoTo ErrHandler:
'select visble data
Selection.SpecialCells(xlCellTypeVisible).Select
'creates variable and selects only visible data
variable = x.Sheets("Vista Application").Cells(1, 1).End(xlDown).Row
'copy selection to new workbook
x.Sheets("Vista Application").Rows("1:" & variable).Copy Destination:=y.Sheets("Vista Application").Range("A2")
'removes text wrapping
y.Sheets("Vista Application").Cells.WrapText = False
'autofits all columns
y.Sheets("Vista Application").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
'error code to delete tab if no visible results
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("Vista Application").Delete
Application.DisplayAlerts = True
End Sub
'all subs have same code pattern as sub1

'EAS Application Tab
Sub Sub2()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("EAS Application")

x.Sheets("EAS Application").Activate
Range("A2:A34785").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("EAS Application").Cells(1, 1).End(xlDown).Row
x.Sheets("EAS Application").Rows("1:" & variable).Copy Destination:=y.Sheets("EAS Application").Range("A2")
y.Sheets("EAS Application").Cells.WrapText = False
y.Sheets("EAS Application").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("EAS Application").Delete
Application.DisplayAlerts = True
End Sub

'EFC & ELD Application Tab
Sub Sub3()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("EFC & ELD Application")

x.Sheets("EFC & ELD Application").Activate
Range("A2:A8265").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("EFC & ELD Application").Cells(1, 1).End(xlDown).Row
x.Sheets("EFC & ELD Application").Rows("1:" & variable).Copy Destination:=y.Sheets("EFC & ELD Application").Range("A2")
y.Sheets("EFC & ELD Application").Cells.WrapText = False
y.Sheets("EFC & ELD Application").Range("A2:Z1000").Columns.AutoFit

(Video) Tutorial - Excel Macro Create new sheet

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("EFC & ELD Application").Delete
Application.DisplayAlerts = True
End Sub

'INAS & SLCC Application Tab
Sub Sub4()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("INAS & SLCC Application")

x.Sheets("INAS & SLCC Application").Activate
Range("A2:A1886").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("INAS & SLCC Application").Cells(1, 1).End(xlDown).Row
x.Sheets("INAS & SLCC Application").Rows("1:" & variable).Copy Destination:=y.Sheets("INAS & SLCC Application").Range("A2")
y.Sheets("INAS & SLCC Application").Cells.WrapText = False
y.Sheets("INAS & SLCC Application").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("INAS & SLCC Application").Delete
Application.DisplayAlerts = True
End Sub

'Vertex Application Tab
Sub Sub5()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("Vertex Application")

x.Sheets("Vertex Application").Activate
Range("A2:A109").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("Vertex Application").Cells(1, 1).End(xlDown).Row
x.Sheets("Vertex Application").Rows("1:" & variable).Copy Destination:=y.Sheets("Vertex Application").Range("A2")
y.Sheets("Vertex Application").Cells.WrapText = False
y.Sheets("Vertex Application").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("Vertex Application").Delete
Application.DisplayAlerts = True
End Sub

'Oracle Database Tab
Sub Sub6()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

(Video) VBA to Create New Workbook - Excel VBA Macro Example

With x.Sheets("Oracle Database")

x.Sheets("Oracle Database").Activate
Range("A2:A13018").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("Oracle Database").Cells(1, 1).End(xlDown).Row
x.Sheets("Oracle Database").Rows("1:" & variable).Copy Destination:=y.Sheets("Oracle Database").Range("A2")
y.Sheets("Oracle Database").Cells.WrapText = False
y.Sheets("Oracle Database").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("Oracle Database").Delete
Application.DisplayAlerts = True
End Sub

'Oracle CCVA Database Tab
Sub Sub7()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("Oracle CCVA Database")

x.Sheets("Oracle CCVA Database").Activate
Range("A2:A619").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("Oracle CCVA Database").Cells(1, 1).End(xlDown).Row
x.Sheets("Oracle CCVA Database").Rows("1:" & variable).Copy Destination:=y.Sheets("Oracle CCVA Database").Range("A2")
y.Sheets("Oracle CCVA Database").Cells.WrapText = False
y.Sheets("Oracle CCVA Database").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("Oracle CCVA Database").Delete
Application.DisplayAlerts = True
End Sub

'Oracle EFDW Tab
Sub Sub8()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("Oracle EFDW")

x.Sheets("Oracle EFDW").Activate
Range("A2:A8736").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("Oracle EFDW").Cells(1, 1).End(xlDown).Row
x.Sheets("Oracle EFDW").Rows("1:" & variable).Copy Destination:=y.Sheets("Oracle EFDW").Range("A2")
y.Sheets("Oracle EFDW").Cells.WrapText = False
y.Sheets("Oracle EFDW").Range("A2:Z1000").Columns.AutoFit

(Video) VBA ADD SHEET | Write a Macro (VBA CODE) to Add a New Worksheet in Excel

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("Oracle EFDW").Delete
Application.DisplayAlerts = True
End Sub

'UNIX Server Tab
Sub Sub9()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("UNIX Server")

x.Sheets("UNIX Server").Activate
Range("A2:A2542").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("UNIX Server").Cells(1, 1).End(xlDown).Row
x.Sheets("UNIX Server").Rows("1:" & variable).Copy Destination:=y.Sheets("UNIX Server").Range("A2")
y.Sheets("UNIX Server").Cells.WrapText = False
y.Sheets("UNIX Server").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("UNIX Server").Delete
Application.DisplayAlerts = True
End Sub

'UNIX EFDW Tab
Sub Sub10()
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")

With x.Sheets("UNIX EFDW")

x.Sheets("UNIX EFDW").Activate
Range("A2:A301").Select

On Error GoTo ErrHandler:
Selection.SpecialCells(xlCellTypeVisible).Select
variable = x.Sheets("UNIX EFDW").Cells(1, 1).End(xlDown).Row
x.Sheets("UNIX EFDW").Rows("1:" & variable).Copy Destination:=y.Sheets("UNIX EFDW").Range("A2")
y.Sheets("UNIX EFDW").Cells.WrapText = False
y.Sheets("UNIX EFDW").Range("A2:Z1000").Columns.AutoFit

End With

Exit Sub
ErrHandler:
Application.DisplayAlerts = False
y.Sheets("UNIX EFDW").Delete
Application.DisplayAlerts = True
End Sub

Sub Count()
'assign variables to be used in the count function
Dim x As Workbook
Dim y As Workbook
Set y = Workbooks("Create File.xlsx")
Set x = Workbooks("Source Data.xlsm")
Dim Vistax, Vistay, EASx, EASy, EFCx, EFCy As Integer
Dim INASx, INASy, Vertexx, Vertexy, Oraclex, Oracley As Integer
Dim OCCVAx, OCCVAy, OEFDWx, OEFDWy, UServerx, UServery, UEFDWx, UEFDWy As Integer

(Video) Excel Macro to export worksheets to new Excel files

'set count values to each variable
On Error Resume Next
Vistax = x.Sheets("Vista Application").Range("A2:A18210").Columns(1).SpecialCells(xlCellTypeVisible).Count
Vistay = y.Sheets("Vista Application").UsedRange.Rows.Count - 2
EASx = x.Sheets("EAS Application").Range("A2:A34785").Columns(1).SpecialCells(xlCellTypeVisible).Count
EASy = y.Sheets("EAS Application").UsedRange.Rows.Count - 2
EFCx = x.Sheets("EFC & ELD Application").Range("A2:A8265").Columns(1).SpecialCells(xlCellTypeVisible).Count
EFCy = y.Sheets("EFC & ELD Application").UsedRange.Rows.Count - 2
INASx = x.Sheets("INAS & SLCC Application").Range("A2:A1886").Columns(1).SpecialCells(xlCellTypeVisible).Count
INASy = y.Sheets("INAS & SLCC Application").UsedRange.Rows.Count - 2
Vertexx = x.Sheets("Vertex Application").Range("A2:A109").Columns(1).SpecialCells(xlCellTypeVisible).Count
Vertexy = y.Sheets("Vertex Application").UsedRange.Rows.Count - 2
Oraclex = x.Sheets("Oracle Database").Range("A2:A13018").Columns(1).SpecialCells(xlCellTypeVisible).Count
Oracley = y.Sheets("Oracle Database").UsedRange.Rows.Count - 2
OCCVAx = x.Sheets("Oracle CCVA Database").Range("A2:A619").Columns(1).SpecialCells(xlCellTypeVisible).Count
OCCVAy = y.Sheets("Oracle CCVA Database").UsedRange.Rows.Count - 2
OEFDWx = x.Sheets("Oracle EFDW").Range("A2:A8736").Columns(1).SpecialCells(xlCellTypeVisible).Count
OEFDWy = y.Sheets("Oracle EFDW").UsedRange.Rows.Count - 2
UServerx = x.Sheets("UNIX Server").Range("A2:A2542").Columns(1).SpecialCells(xlCellTypeVisible).Count
UServery = y.Sheets("UNIX Server").UsedRange.Rows.Count - 2
UEFDWx = x.Sheets("UNIX EFDW").Range("A2:A301").Columns(1).SpecialCells(xlCellTypeVisible).Count
UEFDWy = y.Sheets("UNIX EFDW").UsedRange.Rows.Count - 2

'display results. this is done for each manager. The source and new values should match. If not, check code and
' source sheet data for irregularities such as extra header rows
MsgBox "File Created and Saved" & vbNewLine & _
"Vista Source = " & Vistax & "| " & Vistay & " = Vista New" & vbNewLine & _
"EAS Source = " & EASx & "| " & EASy & " = EAS New" & vbNewLine & _
"EFC Source = " & EFCx & "| " & EFCy & " = EFC New" & vbNewLine & _
"INAS Source = " & INASx & "| " & INASy & " = INAS New" & vbNewLine & _
"Vertex Source = " & Vertexx & "| " & Vertexy & " = Vertex New" & vbNewLine & _
"Oracle Source = " & Oraclex & "| " & Oracley & " = Oracle New" & vbNewLine & _
"OCCVA Source = " & OCCVAx & "| " & OCCVAy & " = OCCVA New" & vbNewLine & _
"OEFDW Source = " & OEFDWx & "| " & OEFDWy & " = OEFDW New" & vbNewLine & _
"UServer Source = " & UServerx & "| " & UServery & " = UServer New" & vbNewLine & _
"UEFDW Source = " & UEFDWx & "| " & UEFDWy & " = UEFDW New"

End Sub

FAQs

Can Excel macro create new sheet? ›

Excel lets you create new worksheets in a number of different ways. What if you want to create a new worksheet and name it all in one step? The easiest way to do this is with a macro. The following is an example of a macro that will ask for a name, and then create a worksheet and give that worksheet the name provided.

How do I create a multiple sheet in Excel using VBA? ›

To add multiple sheets in one go, you just need to define the COUNT argument with the number of sheets you want to add. Now the count of the sheets that you have defined is 5, so when you run this code it instantly adds the five new sheets in the workbook.

How do I create multiple new sheets in Excel? ›

Hold down SHIFT, and then select the same number of existing sheet tabs of the worksheets that you want to insert in the open workbook. For example, if you want to add three new worksheets, select three sheet tabs of existing worksheets. On the Home tab, in the Cells group, click Insert, and then click Insert Sheet.

How do I create a spreadsheet from an Excel list? ›

Follow these steps:
  1. Select any worksheet name in the column.
  2. Display the Insert tab of the ribbon.
  3. Click the PivotTable tool, at the left side of the ribbon. ...
  4. Click OK. ...
  5. In the PivotTable Fields pane, click the checkbox next to the field used for your list of worksheets.
24 Sept 2022

How do you create multiple sheets based on cell value? ›

1. Create new worksheets programmatically based on values in a cell range
  1. Press Alt + F8 to open the Macro dialog box.
  2. Select macro CreateSheets.
  3. Press with mouse on "Run" button.
  4. An input box appears asking for a cell range.
  5. Select a cell range and press with left mouse button on the "OK" button.
16 Oct 2022

How do I split an Excel spreadsheet into multiple worksheets without VBA? ›

Select below the row where you want the split, or the column to the right of where you want the split. On the View tab, in the Window group, click Split. To remove the split panes, click Split again.

What is an Excel macro sheet? ›

If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes.

How do I hard code an entire Excel workbook? ›

The steps for the macro would be:

Select all sheets, copy and paste special all cells in the sheets. Save the Workbook with Save As (be careful you don't SAVE, but SAVEAS) Save it in the same directory as the Workbook. Call the new Workbook "HC_" followed by the current name.

How do I apply VBA code to all worksheets? ›

Here are the steps to do this:
  1. Go to the Developer tab.
  2. Click on the Visual Basic option. ...
  3. In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. ...
  4. Go to Insert and click on Module. ...
  5. Copy and paste the code in the module window.

How do I copy a sheet in Excel to another sheet with the same format and formula? ›

Here's how:
  1. Select all the data in the worksheet. Keyboard shortcut: Press CTRL+Spacebar, on the keyboard, and then press Shift+Spacebar.
  2. Copy all the data on the sheet by pressing CTRL+C.
  3. Click the plus sign to add a new blank worksheet.
  4. Click the first cell in the new sheet and press CTRL+V to paste the data.

How do I create a new Excel file in VBA? ›

To create a new workbook using VBA, you need to use the “Workbooks. Add” method. When you use this method, it inserts a new workbook (without saving it) and activates it after that. It works like when you press the keyboard shortcut CONTROL + N.

How do you auto populate data from multiple sheets to a master? ›

How to collect data from multiple sheets to a master sheet in...
  1. In a new sheet of the workbook which you want to collect data from sheets, click Data > Consolidate.
  2. In the Consolidate dialog, do as these: (1 Select one operation you want to do after combine the data in Function drop down list; ...
  3. Click OK.

How do you create a table in Excel VBA? ›

Add method of sheet to create tables in excel VBA.
...
Instructions:
  1. Open an excel workbook.
  2. Press Alt+F11 to open VBA Editor.
  3. Double click on ThisWorkbook from Project Explorer.
  4. Copy the above code and Paste in the code window.
  5. Press F5.
  6. GoTo Sheet1 and Select Range A1 to D10.
  7. You should see the above output in Sheet1.

How do I automate a sheet name in Excel? ›

Enter SheetNames into the Name field, enter the following formula into the Refers to field: =REPLACE(GET. WORKBOOK(1),1,FIND("]",GET. WORKBOOK(1)),""), and then select OK. This action will create a named formula that can then be used in conjunction with the INDEX function to produce a list of worksheet names.

Videos

1. How to Create Macros in Excel Tutorial
(Kevin Stratvert)
2. Excel VBA Macro: Add New Sheets To Beginning Or End of Workbook (With Names)
(greggowaffles)
3. How to Create a Personal Macro Workbook in Excel and Why You Need It (Part 1 of 4)
(Excel Campus - Jon)
4. Creating a Macro Enabled Template in Excel
(Christopher Kalodikis)
5. Excel VBA Macro to Create Workbook for Each Worksheet
(Chester Tugwell)
6. Add Month Sheets Automatically in Excel With This Macro
(Contextures Inc.)

Top Articles

You might also like

Latest Posts

Article information

Author: Duncan Muller

Last Updated: 08/14/2022

Views: 5925

Rating: 4.9 / 5 (79 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Duncan Muller

Birthday: 1997-01-13

Address: Apt. 505 914 Phillip Crossroad, O'Konborough, NV 62411

Phone: +8555305800947

Job: Construction Agent

Hobby: Shopping, Table tennis, Snowboarding, Rafting, Motor sports, Homebrewing, Taxidermy

Introduction: My name is Duncan Muller, I am a enchanting, good, gentle, modern, tasty, nice, elegant person who loves writing and wants to share my knowledge and understanding with you.