extract data from main sheet to other sheets

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
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

Well-known Member
Joined
May 22, 2007
Messages
1,035
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
 

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103
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

Well-known Member
Joined
Sep 28, 2008
Messages
538

ADVERTISEMENT

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

Board Regular
Joined
Nov 14, 2008
Messages
103
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103
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

Board Regular
Joined
Nov 14, 2008
Messages
103
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

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
Top