Forum Discussion
VBA Split Data by Columns + Formatting
I'm looking for VBA macro code that can split a table into numerous worksheets by column value and keep formatting.
- Have an input to select which column needs to be split by data in that column
- Each new worksheet should be named the name of the value from the original column that is being split
- Copy formatting of table into new sheets (borders, column width, etc)
- The table does not always start in cell A1. For example, if table starts in C4 (would require some input, hopefully selecting the cell), copy the columns to the left, and rows above to paste on all new pages (information not including, but surrounding the table)
I posted an example of me doing the process manually below. Original is the original table with Result1 and Result2 being the worksheets created after the macro is run.
Compl9x From looking at your screen shots, it seems that this will be very easy to do with Power Query, and then you'll simply be able to press Refresh All whenever you have new data that you want to split into A and B sheets.
The general process is:
1. Select a cell in the source table, then go to the Data tab and press the From Table/Range button to bring the data into Power Query.
2. Set up the query to get rid of any rows that you don't need, such as empty rows.
3. Promote the top row to headers, if needed.
4. Right-click on the query and choose "Reference". This will create a new query that gets data from the first query.
5. In the 2nd query, set it to filter the Project Group column so it keeps only A values.
6. Repeat step 4, then set the 3rd query to filter for the B values.
7. Rename the queries as appropriate.
8. Click the Close and Load button.
You should end up with 2 new sheets. One with the A records, and one with the B records. Apply the formatting to those sheets so they have the look you want.
When you have new data on the Original sheet, you can just go to the Data tab and press Refresh All to get the new data split onto the other sheets.
- Yes; however, due to the amount of tables I have, I was just looking for VBA code to reduce the amount of manual work each time. I used to have a VBA that split data by column without formatting, but it stopped working and I can no longer find it.
