Let’s check out how this works in the example of our dataset imported from Pipedrive. This could be useful if you need to partition your dataset into sheets or files with a fixed number of rows. I also promised to demonstrate the horizontal data split, i.e., split Excel sheet into multiple sheets based on rows. How to split Excel sheet into multiple worksheets based on rows ![]() In my case, these are three separate workbooks based on the column value. When I run the macro, it will split the Excel sheet into multiple files. I added the following script and saved it: Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn() I inserted a new module – go to Visual Basic, right-click on Modules, then select Insert => Module. VBA script to split Excel sheet into multiple files based on column valueĪs an alternative, I decided to tune this script, so it will split an Excel sheet into multiple files, i.e. ![]() Note: If you want to apply this script for your needs, make sure to tweak the variables of a column to split by and range of your dataset. NNextRow = objSheet.Range("A" & ).End(xlUp).Row + 1Īfter I run the macro, it will return the following:Įach sheet will contain rows specific to a certain status of Pipedrive deals: lost, won, and open. If CStr(objWorksheet.Range("A" & nRow).Value) = CStr(varColumnValue) Then Set objSheet = objExcelWorkbook.Sheets(1) If objDictionary.Exists(strColumnValue) = False Thenįor i = LBound(varColumnValues) To UBound(varColumnValues) StrColumnValue = objWorksheet.Range("A" & nRow).Value Set objDictionary = CreateObject("Scripting.Dictionary") NLastRow = objWorksheet.Range("A" & ).End(xlUp).Row Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn()
0 Comments
Leave a Reply. |