extract data from main sheet to other sheets

[gr8trthanu]

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

 

[Makrini]

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?

 

[Makrini]

if so..

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

 

[gr8trthanu]

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

 

[Diablo II]

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

 

[gr8trthanu]

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

 

[Peter_SSs]

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.

 

[Peter_SSs]

Looks like yet yet another one here!

 

[gr8trthanu]

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

 

[gr8trthanu]

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