seperating data into specific sheets

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103
Hello,
I am having problems thrying to get rows copied from a detail sheet into other sheets based on criteria in column "f" the data in column 'F" has sheets named after each.

However I found some code that resembles somewhat what I want to do... here is the link

http://www.mrexcel.com/forum/showthread.php?t=259350

Basically the code should look at each row starting at row 19 in the detail sheet. If in row 20 column f has "J1" then the entire row 20 should be copied, hilited green, and pasted in a sheet in the same workbook entilted J1. Then it would continue down the sheet until the end copying and pasting rows based on the data in column f. The sheets have already been created.

thank you
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
WOW...looks great but will it allow me to use spreadshhet that have already been created as there are formulas at the top with sumif and countif?

Thanks

data explosion looks extremely neat!!
 
Upvote 0
Hello,
I am having problems thrying to get rows copied from a detail sheet into other sheets based on criteria in column "f" the data in column 'F" has sheets named after each.

However I found some code that resembles somewhat what I want to do... here is the link

http://www.mrexcel.com/forum/showthread.php?t=259350

Basically the code should look at each row starting at row 19 in the detail sheet. If in row 20 column f has "J1" then the entire row 20 should be copied, hilited green, and pasted in a sheet in the same workbook entilted J1. Then it would continue down the sheet until the end copying and pasting rows based on the data in column f. The sheets have already been created.

thank you
You could try this adaptation of the code in the link you gave. You may have to modify the code a little, or post back for modifications, if any of the following assumptions are incorrect:

1. The sheet containing the detail is called "Detail".

2. The detail sheet data of interest starts on row 19 but has headings (or something) in row 18.

3. The data is to be copied into the other sheets, starting at row 2.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ExtractData()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    lr = Sheets("Detail").Range("F" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>        <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Detail" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Sheets("Detail").Rows("18:" & lr)<br>                .AutoFilter Field:=6, Criteria1:=ws.Name<br>                .Offset(1).Copy Destination:=ws.Range("A2")<br>                .AutoFilter<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>
 
Upvote 0
will this code move the entire row based on the Data on solumn "f". there are 5 different tabs with either J1, to J5

Thanks for your help
 
Upvote 0
Thanks Peter for your help. I have pasted a screen shot of the error code along with a screen shot of the workbook. So just to refresh, cause I think I am driving everyone crazy including myself...
When I select the red command button at the top any rows with J1 in column D should be moved over to J1 from the detail tab. Any rows with J2 in column D should go to J2 from the detail tab. The pasting of rows should start at column 19 for each sheet cause I have formulas and macros from rows 1 to 18

codeerror


workbook
 
Upvote 0
... based on criteria in column "f" ...
Basically the code should look at each row starting at row 19 in the detail sheet.
... start on line 5 of the detail section and look in column "D"
Apart from running several threads on the same topic, is it any wonder there is confusion about what column to look in and what row to start at?

Also, I stated three assumptions in post #4 above and it appears now that assumptions 2 and 3 (at least) were wrong but you did not indicate those errors in any of your responses!

This time, I am making these assumptions:

1. The sheet containing the detail is called 'Detail'.

2. The 'Detail' sheet data of interest starts on row 5 but has headings (or something) in row 4.

3. The data is to be copied into the other sheets, starting at row 19.

4. The column to look for the sheet names is column F.


Try this as you button_click code.

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>lr = Sheets("Detail").Range("F" & Rows.Count).End(xlUp).Row<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>    <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Detail" <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Detail").Rows("4:" & lr)<br>            .AutoFilter Field:=6, Criteria1:=ws.Name<br>            .Offset(1).Copy Destination:=ws.Range("A19")<br>            .AutoFilter<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> ws<br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN></FONT>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top