Excel 2007 VBA to search and then copy row to relevant tab in workbook
I have attached the example work book that I am using, Basically what happens is that this workbook will collect data over a weekend and then be sent by e mail on a Monday to be sorted into the relevant sections.
What I need is a macro that will add a tab for each name in range J2:J7 and then within the main data tab carry out a search for the words that match each tab just created and copy the relevant row into the relevant tab that it relates to.
Any help will be much appreciated.
Thanks
Dave
You can try the above. It assumes the values in J2:J7 that you are searching by are the unique values in column A. So when the code finds a unique value in column A it copies the row A:K to the created worksheet. Let me know what happens.
Please Login or Register to view this content.
Thanks for the speedy response.
I am getting the following error when I run the code "Run time error 1004" Application defined or object defined error.
and the code seems to have a problem with the following line, I changed the name of the main tab to what my tab is called.
Worksheets.Add().Name = Range("J" & x).Value
It will add the first tab which is Continental Travel but does not add the other names in J3,J4.J5,J6,J7
Try this instead of the line giving the error.
Please Login or Register to view this content.
I made the alterations but the same run time error came up and it says it is failing on the line
Worksheets.Add().Name = temp_String2.
Just a Question as well, will this macro also search for the main data for matching words to each tab and then copy it within that relevant worksheet, e.g It will search for Continental travel within the main page and if it finds a cell reference with that it will copy the row to the Continental travel tab.
Here is the full code I have now
Your help is much appreciated.Sub copy_To_Tab()
Dim x As Long
Dim last_Row As Long
Dim temp_String As String
Worksheets("Example").Activate
last_Row = Range("A65536").End(xlUp).Row
For x = 2 To 7
Dim temp_String2 As String
temp_String2 = Range("J" & x).Value
Worksheets.Add().Name = temp_String2
Next x
For x = 1 To last_Row
temp_String = Range("A" & x).Value
Range("A" & x, "K" & x).Select
Selection.Copy
Worksheets(temp_String).Activate
last_Row = Worksheets(temp_String).Range("A65536").End(xlUp).Row
Range("A" & last_Row + 1).Paste
Worksheets("Example").Activate
Next x
End Sub
Last edited by dave1983; 04-17-2012 at 02:18 PM.
Any ideas from anyone would be really helpfull
http://www.excelforum.com/excel-prog...html?p=2762991
Try
Please Login or Register to view this content.
Just incase this helps I came across a website that was really usefull.
http://www.techonthenet.com/excel/ma...for_string.php
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks