# extract data from main sheet to other sheets

#### gr8trthanu

##### Board Regular
I have already placed a siniliar question but I think it fell through the cracks or it was to difficult to understand!! I am losing my mind as the hrs tick down and I can't get this done

I have a detailed sheet called "detail"

I have 5 other sheets called "J1" "J2" "J3" "J4" "J5"

The sheets are exactly the same with headers already in. However "detail" sheet has all the detail and the "J" sheets are blank.

I need a macro in a command box that will start on line 5 of the detail section and look in column "D". if either of the "J"s are in column D then the row will be copied, cell colour turns green, and pasted in one of the 5 "J" sheets depending on the value. This process should continue to loop until there are no more lines left in "detail" to copy

I just can't get my head around how to write a macro that will perform this. I have searched through different threads and tried and manipulated a few but I can't get it to work at all. I think I am so frustrated after 8 hrs that

Any help will be greatly appreciated

Thank you so much in advance

desperate me

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
so...

You basically want to filter everything is column D with the value "J1" in it - and copy those to sheet "J1"? And colour them so you know you have copied them?

Then do the same for all other J's?

if so..

Code:
``````Sub runthis()
For i = 1 To 5
MakeitHappen "J" & i
Next i
End Sub

Sub MakeitHappen(Searcher As String)

i = 5
Set therange = Range("D5:D64000").SpecialCells(xlCellTypeConstants)

For Each Cell In therange
If Cell.Value = Searcher Then Sheets(Searcher).Range("A" & i).EntireRow.Value = Cell.EntireRow.Value
Cell.EntireRow.Interior.ColorIndex = 4
Cell.EntireRow.Interior.Pattern = xlSolid

i = i + 1
Next
End Sub``````

sorry I have the "J" sheets already with sumifs and countifs at the top to help with my next step.

I think this is what I need
start in row 2 of main sheet
if "J1" is found in column d then
copy row
hilite row
select "J1" sheet
paste entire row

keep doing this until all rows from detail have been looked at

Thanks very much for your help

so what you need is if D2 has J1 then copy from D2 to IV2? or copy A2 to IV2? or just D2 cell?

So if D2 has "J1" then the entire row should be copied from the "detail" sheet and pasted on the next available row on the existing sheet 'J1".

If D2 has "J2" then the entire row should be copied fromn the "detail" sheet and pasted on the next available row on the existing sheet "J2"

and so on.....

When pasting the information the first row on all the sheets should start at row 19 as I have formulas above row 19

thanks

I have already placed a siniliar question but I think it fell through the cracks or it was to difficult to understand!!
However, you are best to stick to your original thread and clarify the issues there.

Otherwise you end up with two sets of people working without the full knowledege of what has already been covered. If somebody puts in a fair amount of time to develop a solution, only to find it has already been solved in another thread, they are likely to be less than pleased and less likely to help you in the future.

My sincere apologies for duplicating thread. Thank you however for your time. Unfortunately when I paste your code into my command box nothing happens and there are no error codes either

Here is my code so far but getting error messages

Dim Source As Worksheet
' Sheet containing data
Dim Crit As String
' Defines what sheet has the master data
Set Source = Worksheets("Detail")
' Tells macro not to work on specified sheets of the workbook
If Sh.name = Source.name Then Exit Sub----------------------Getting error code here. Object required
If Sh.name = "J1" Then Exit Sub
If Sh.name = "J2" Then Exit Sub
If Sh.name = "J3" Then Exit Sub
If Sh.name = "J4" Then Exit Sub
Application.ScreenUpdating = False
' Sheet name is data required
Crit = ActiveSheet.name
With Source.Range("A4:U" & Source.Range("A4").End(xlDown).Row)
' Filter for column 4 = sheet name
.AutoFilter Field:=4, Criteria1:=Crit
' Copy data to active sheet, range decides where it goes on the active sheet
.Copy ActiveSheet.Range("A4")
' Trn off Autofilter
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Replies
7
Views
205
Replies
0
Views
257
Replies
5
Views
124
Replies
8
Views
299
Replies
6
Views
556

1,203,693
Messages
6,056,760
Members
444,889
Latest member
ibbara

### 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.

### Which adblocker are you using?

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

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