Stumped on VB
Solved/Closed
weenie
-
Nov 28, 2010 at 05:18 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 20, 2010 at 08:10 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 20, 2010 at 08:10 AM
Related:
- Stumped on VB
- Vb select case - Guide
- Vb editor download - Download - IDE
- Vb code maker aljzazy - Download - Other
- Vb round up - Guide
- Vb color codes list - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Dec 9, 2010 at 10:04 AM
Dec 9, 2010 at 10:04 AM
Hi Weenie,
Start by creating sheets with their appropriate names.
Then create headers for each sheet.
If this is the same for all sheets, select all sheets by holding the CTRL button to apply the header to all sheets at once.
Then use the following code structure:
Now copy/paste the part that starts with "If" and ends with "End If".
Then replace XXN or XXR with one of your other search criteria's three times.
Make sure you run the code when sheet1 is activated.
Don't hesitate to ask if something is unclear!
Best regards,
Trowa
Start by creating sheets with their appropriate names.
Then create headers for each sheet.
If this is the same for all sheets, select all sheets by holding the CTRL button to apply the header to all sheets at once.
Then use the following code structure:
Sub test() Set MR = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)) For Each cell In MR If (Left(cell.Value, Len("XXN")) = "XXN") Then Range(Cells(cell.Row, "A"), Cells(cell.Row, "O")).Copy Sheets("XXN").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial End If If (Left(cell.Value, Len("XXR")) = "XXR") Then Range(Cells(cell.Row, "A"), Cells(cell.Row, "O")).Copy Sheets("XXR").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial End If Next Application.CutCopyMode = False End Sub
Now copy/paste the part that starts with "If" and ends with "End If".
Then replace XXN or XXR with one of your other search criteria's three times.
Make sure you run the code when sheet1 is activated.
Don't hesitate to ask if something is unclear!
Best regards,
Trowa
THANK YOU SO MUCH!!!
It worked beautiful. I do have a question. I am currently at 600,000 rows but when I tested the code I only used 80,000 rows of data (a quickie test of code). It took about ~15-20 mins just to go thru this amount. So, when I do apply this code to rest of 600,000 rows I imagine it will take a few hours to run this. Is there a way to make this code sort this faster?
Thanks,
Weenie
It worked beautiful. I do have a question. I am currently at 600,000 rows but when I tested the code I only used 80,000 rows of data (a quickie test of code). It took about ~15-20 mins just to go thru this amount. So, when I do apply this code to rest of 600,000 rows I imagine it will take a few hours to run this. Is there a way to make this code sort this faster?
Thanks,
Weenie
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Dec 20, 2010 at 08:10 AM
Dec 20, 2010 at 08:10 AM
Sorry Weenie, don't have an answer for that.
Guess you can split your data into multiple sheets or run the code overnight, since it's probably a one time action.
Best regards,
Trowa
Guess you can split your data into multiple sheets or run the code overnight, since it's probably a one time action.
Best regards,
Trowa