How to create macro to copy select data, or row into newly created tabs

[Dariusd7]

Ok So thanks to Judgeh59 The first part of my project is resolved and macro now creates new tabs with specified names.

Now i need to be able to copy select data from one work sheet and fill into the newly created tabs.

so I have a column that has MSO numbers , these numbers identify which region the part repair belong to...
so for example..
anything starting with 100 belongs to VA
..starts with 200 belongs to Il..

so here are the numbers and regions
the column is called MSO and its in column B
100 = VA
200 = IL
300 = CA
400 = NJ
500 = PR
600 = TX
900 = Warranty
CA, OR, TX = BSVG

The numbers in the MSO are 9 digits long. but the first 3 numbers identify the region.. The tabs named as above, Va, Il, CA, NJ, PR, TX, Warranty, BSVG..

Thanks for your help..

[judgeh59]

are you moving the entire row to the new WS or just pieces of the row?....are you moving or copying?...will these new WS have headers already and/or will you copy the headers from your master WS to the new WS as you create them?....inquiring minds want to know....

[Dariusd7]

yes headers will need to be copied over, the data is to be copied not moved, and the whole row needs to be copied over..
The columns go from a to Y

Thanks.. also keep in mind the code you gave me to create each tab.. that is the code I will run first to create the tabs.

Thanks.

[judgeh59]

this should get you started....I was sure about the whole...CA,TX,OR = BSVG....but we can work that out later....see if this works....

Please Login or Register  to view this content.

[Dariusd7]

Ok it only created the first tab for VA..and does not fill in data in the new worksheet.. I'm not sure but could it be the range in the formula..because the column that has the MSO numbers is in Column B... Not column A and the data needed to be Copied starts in Column B.. so sorry on the detail above.. the column go from B to Y...

[Dariusd7]

I made the following changes to the program. It now create the first tab called VA, and copies the header row.. and that is it..does not create the other tabs, nor copy and fills data into the new worksheet..

[code] Dim x As Integer
Dim LastRow As Single, TempLastRow As Single, y As Single

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Names = Array("VA", "100", "IL", "200", "CA", "300", "NJ", "400", "PR", "500", "TX", "600", "Warranty", "900")

For x = 0 To UBound(Names) Step 2
Range("b2:Y2").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Names(x)
Range("b2").Select
ActiveSheet.Paste
Sheets("Master").Activate
Next x

LastRow = Cells(65000, 1).End(xlUp).Row

For x = 0 To UBound(Names) Step 2
For y = 2 To LastRow
If Left(Cells(y, 1).Value, 3) = Names(x + 1) Then
TempLastRow = Sheets(Names(x)).Cells(65000, 1).End(xlUp).Row
Range(y & ":" & y).Select
Selection.Copy
Sheets(Names(x)).Activate
Cells(TempLastRow + 1, 1).Select
ActiveCell.PasteSpecial
Sheets("Master").Activate
End If
Next y
Next x

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub[code]

[Dariusd7]

Sorry I couldn't find a faq on how to post a section as code...

[judgeh59]

that last code should have a / in it, like this, without the quotes "[/code]
I take it the headers are in row 2....can you upload a copy of the data?....click "Go Advanced" and scroll down to see attachments area...without the data, I'm only guessing....you can remove any company confidential stuff....thanks

[Dariusd7]

File attached..Thanks

[judgeh59]

okay, I'm sneaking up on this puppy....do you want to move only rows that have a 1 in column A?

[judgeh59]

so, the biggest issue was the column differences and the I named my test WS Master and you had Sheet1...it got confused...this works on the data set you sent...and it only copies rows with a "1" in column A....if you don't want that remove everything past the AND and before the THEN in the "y" FOR/NEXT Loop....

Please Login or Register  to view this content.

[Dariusd7]

Sorry I was at school when you posted this.. No not the items with 1. I am so sorry I wasnt clear.. None of the rows with a 1 will get moved..only the rows with 0 get moved.. The rows with 1 were are row that needed to be separated from the data before it's parsed into each seperate region..

[Dariusd7]

thanks Judgeh59 That worked, What I did was changed the 1, to a 0 between the and and then.. and it worked.. what I am going to do about the other 3 which is regions ca1, or1, tx1 = BSVG, is create an if macro that will mark those 3 regions with a 3, then Look at your code to see how I can then use that mark of 3 to pull the data into one page.. once i get it to work that way. I will then see how i can just add it to the same module you just built for me.. Judgeh59 you the man.. you the man..

[judgeh59]

excellent....glad you worked out the change....glad I could help....have fun in MacroLand

[judgeh59]

Reminder - don't forget to set this thread to Solved....