Create new Excel Worksheets named with the unique values (and the # of rows) from a column & copy the rows to the proper worksheet.
Need to create new tabs in excel based on column (B in this example)
The pictures should be fairly self-explanatory.
Thank you for your time in advance!
First image is current excel workbook
Second image is desired future state
I also attached the Excel file.
EE2.png
Sample-EE.xlsx
The pictures should be fairly self-explanatory.
Thank you for your time in advance!
First image is current excel workbook
Second image is desired future state
I also attached the Excel file.
EE2.pngSample-EE.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes it does. Try out the code and see if the results is what you requested.
ASKER
Okay thank you Rodney.
It has been running for about 10 minutes so far. It looks like it is analyzing the rows (perhaps more specifically column B) as it has yet to make a single tab. The spreadsheet has 29,000 rows. I might need to stop it and take a look in 15 minutes when I get home.
Again thank you for this!
K.B.
It has been running for about 10 minutes so far. It looks like it is analyzing the rows (perhaps more specifically column B) as it has yet to make a single tab. The spreadsheet has 29,000 rows. I might need to stop it and take a look in 15 minutes when I get home.
Again thank you for this!
K.B.
No problem K.B.
You can also put these additions to the code:
Sub EE_CreateSheetsfromCells()
Application.ScreenUpdating
......{leave existing code here}......
Application.ScreenUpdating
End Sub
It may speed up the process.
You can also put these additions to the code:
Sub EE_CreateSheetsfromCells()
Application.ScreenUpdating
......{leave existing code here}......
Application.ScreenUpdating
End Sub
It may speed up the process.
ASKER
Thank you .
Sorry for the delay.. I just kicked it off with your amended code about 10 minutes ago. I will let you know when it completes.
Sorry for the delay.. I just kicked it off with your amended code about 10 minutes ago. I will let you know when it completes.
ASKER
it returned an error that i typed an invalid name for a worksheet. when i clicked Debug... it highlighted this line:
ActiveSheet.Name = sht.Name & "-" & sht.UsedRange.Rows.Count
It also filled my clipboard full on entire rows ... which is no big deal but maybe it offers insight somehow.
It created all the necessary tabs - named correctly... but it did not include the row count along with the name.
All tabs had one row of correct data - the rest of the rows were blank.
Pretty close I imagine.
Thank you again for your efforts. I really do appreciate it.
K.B.
ActiveSheet.Name = sht.Name & "-" & sht.UsedRange.Rows.Count
It also filled my clipboard full on entire rows ... which is no big deal but maybe it offers insight somehow.
It created all the necessary tabs - named correctly... but it did not include the row count along with the name.
All tabs had one row of correct data - the rest of the rows were blank.
Pretty close I imagine.
Thank you again for your efforts. I really do appreciate it.
K.B.
Hi K.B.,
I adjusted to code in this attached file. Let me know if this works better for you.
I tested the code out on 25,000 rows of data and it processed in under 5 minutes.
This shortcut opens the Visual Basic Editor (ALT + F11 key). The VBA code is in 'ThisWorkbook' (Microsoft Excel Objects).
Sample-EE.xlsb
I adjusted to code in this attached file. Let me know if this works better for you.
I tested the code out on 25,000 rows of data and it processed in under 5 minutes.
This shortcut opens the Visual Basic Editor (ALT + F11 key). The VBA code is in 'ThisWorkbook' (Microsoft Excel Objects).
Sample-EE.xlsb
ASKER
It seems I am having the same issue ..
Does it matter that there is no data in column A from the source sheet?
I have data in column B through column J
The results were fairly similar...
Only one row of data appears on each newly created tab.
Each tab is named the correct name & the number "1".
So, Apples-1, Oranges-1 etc...
Does it matter that there is no data in column A from the source sheet?
I have data in column B through column J
The results were fairly similar...
Only one row of data appears on each newly created tab.
Each tab is named the correct name & the number "1".
So, Apples-1, Oranges-1 etc...
I ran the code on the Sample data and it worked successfully.
The macro is working off the data in Column B (creating new worksheets & transferring data to those new worksheets).
If there is no data in Column A, it does make a difference. You will have to adjust 1-line of code:
If IsEmpty(Cells(Rows.Count, 1).End(xlUp).Value) = True Then
TO this line
If IsEmpty(Cells(Rows.Count, 2).End(xlUp).Value) = True Then
Basically, changing the number '1' to a '2'. When you update the line, it should work better for you.
I updated the code in this attached file.
Sample-EE.xlsb
The macro is working off the data in Column B (creating new worksheets & transferring data to those new worksheets).
If there is no data in Column A, it does make a difference. You will have to adjust 1-line of code:
If IsEmpty(Cells(Rows.Count, 1).End(xlUp).Value) = True Then
TO this line
If IsEmpty(Cells(Rows.Count, 2).End(xlUp).Value) = True Then
Basically, changing the number '1' to a '2'. When you update the line, it should work better for you.
I updated the code in this attached file.
Sample-EE.xlsb
ASKER
Worked perfect! thank you for your patience!
Glad to assist, K B! Happy coding...
ASKER
Does it also name the tabs with the unique names + number of rows?