Hello, Show
I need to generate several hundred identical excel workbooks using a macro. I'd like to standardise the file names based on a list in Excel. So if column A says 'AC.334.997' and column B says 'Process improvement project', it would generate workbooks based on the template - in this case named 'AC.334.997_Process improvement project', and so on. Any help would be much appreciated! Thanks, Earlier we have split our data into separate excel sheet but this time, we will split spreadsheet records into a new workbook. It’s useful and reduces our effort in making different workbooks with the same data. I hope you have enjoyed our earlier blogs, revisit “How to split excel data into separate spreadsheets using VBA“. Split spreadsheet data into new Excel workbooks:
We take our earlier data which looks like below: Please look at the column state. If you do it manually then you need to apply a filter and then chose one state name. After that, you need to copy the records and press Ctrl + N to create a new workbook and then paste the records. And then you need to save the file in your desired location. After that, you will go to the second state.
Not only that if any new records inserted into your raw sheet you need to do the entire stuff once again. But at the same time, a single piece of code will automate the entire process.
Step by Step guide on how to split the excel sheet:
Use this Macro Code:Sub ExtractToNewWorkbook() Dim ws As Worksheet Dim wsNew As Workbook Dim rData As Range Dim rfl As Range Dim state As String Dim sfilename As String Set ws = ThisWorkbook.Sheets("emp") 'Apply advance filter in your sheet With ws Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 11).End(xlUp)) .Columns(.Columns.Count).Clear .Range(.Cells(2, 6), .Cells(.Rows.Count, 6).End(xlUp)) .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True For Each rfl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp)) state = rfl.Text Set wsNew = Workbooks.Add sfilename = state & ".xlsx" 'Set the Location ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sfilename Application.DisplayAlerts = False ws.Activate rData.AutoFilter Field:=6, Criteria1:=state rData.Copy Windows(state).Activate ActiveSheet.Paste ActiveWorkbook.Close SaveChanges:=True Next rfl Application.DisplayAlerts = True End With ws.Columns(Columns.Count).ClearContents rData.AutoFilter End Sub In my case, I have saved all workbooks where this file is located. My file is saved in (“C:\Users\Desktop\State Name Separate in a New workbook”) this location. So all the news files will be created and saved in the same location. Now my folder looks like below Only one file is present there. Now if you run the code then all the new files will be created here and the folder looks like below picture: It will take hardly 1 min to complete the task. Isn’t that interesting? Moreover, if you add new records in your raw spreadsheet what will have to do is you need to delete all the files before running the macro code. You can also add a button in your excel sheet and assign the macro to that button. You have already discussed the same in our earlier blog. Now I will discuss the macro code part:First of all, I have applied an advanced filter on States in my raw spreadsheet. Then I have stored the entire state’s name in a variable. Then I have applied a loop. Every time it will get a new state name it will create a new workbook and save the file in the location and then paste the records in the new workbook. After pasting the data in the new worksheet, it will close the new workbook. The entire process will go on until it will copy and paste till the last state (Split spreadsheet data). If you wish you can add a msgbox at the end of your macro code to get the information that it has successfully run the macro code. Note: Please note that before you run the code please check that there is no other file with the same name. For example, if you run the code for the second time it will give you the following error as the same file already exists in your location. It will look for your confirmation. Now if you press the cancel button it will give you an error as shown below: We can overcome the scenario in two ways: When you are saving the new workbook, you need to check whether the file name exists or not. If it exists then you need to delete it from the folder or the best option is that when you are saving the file you can save the file with the current date. I will suggest to delete the earlier file and re-run the code. How do I create multiple Excel workbooks in VBA?Excel VBA macro to create multiple workbooks that are macro free. Loop through a column of selected rows on a sheet in my workbook to get the names for each of the new workbooks to be created (I have this working). Refresh all data within the newly created workbook (I have this working). How do I split an Excel sheet into multiple files using macros?How to split a CSV or Excel file. Open a new file in Excel.. Enable macros.. Open the macro editor.. Copy the text below starting at "Sub" and ending with "End Sub". Paste it into the macro editor.. Return to Excel from the macro editor.. Save the file as a file of type . xlsm.. How do I automatically create multiple sheets in Excel?Start Excel. A new, blank workbook appears. Click the New sheet button at the bottom of the screen. Press and hold the CTRL key, and then click Sheet1, Sheet2, and so on till you finish selecting all your worksheets.
|