Hope Someone Can Help

[razorblade]

I have a Workbook with several sheets all of which are the same layout and 1 final sheet (Sheet6) which will give a list of all arrivals and departures.

I have dates along the top with Personel down the left side.

I need a macro or something that can look through sheets 1-5 on a specified column (date).

If there is an 'A'(Arrival) i need it to copy Column 'A', 'B', 'C' & 'D' into the same columns on Sheet 6. If it is an 'X'(Departure) i need it to insert Columns 'A', 'B', 'C' & 'D' into 'F', 'G', 'H' & 'I' on sheet 6.


Hope someone can help. I can send a dummy copy of the workbook to anyone if they want to have a play around and understand better.

Sorry for the lame description


Many thanks in advance

David

 

[onlyadrafter]

Hello,

Can you not give a small example of the data here? i.e. Sheet 1 cells A1:C3

also give a small example of Sheet6?

 

[razorblade]

See URLs for samples of Sheet 1-5 and Sheet 6


http://img130.imageshack.us/img130/4388/sheet15ss2.jpg

http://img519.imageshack.us/img519/3836/sheet6xu6.jpg

Hope this helps[/url]

 

[onlyadrafter]

Hello,

OK, so how do you want this to work? can you explain it step be step?

No doubt there will be some questions.

 

[razorblade]

I need something that will look through Sheets 1-5 and if there is an 'A' or 'X' Copy the Name, Title, Shift & Vantage info onto Sheet 6 into the relevant side ('A' - Arrivals, 'X' - Departures)


For Example if you look at Sheet 1-5 Column 'AD' (Thurs 26th) the 1st we come to is 'X' in AD:16.

A:16, B:16, C:16 & D:16 will go into F:6, G:6, H:6 & I:6 on Sheet 6

Because there is an 'A' directly below (in AD:17) then A:17, B:17, C:17 & D:17 will go into A:6, B:6, C:6 & D:6 on Sheet 6


If there is only an 'X' or an 'A' present i.e. AD:37 then the adjacent spaces on sheet 6 would remain empty.
(A:37, B:37, C:37 & D:37 will go into F:#, G:#, H:# & I:# and A:#, B:#, C:# & D:# will remain blank)


I'm not sure if that is possible to get the lining up but even if i could just get a list of all 'X' on the Departures side of Sheet 6 and all 'A' on the Arrivals side of sheet 6 that would be great.



Many thanks

 

[onlyadrafter]

Hello,

But how do you decide which column you are starting in?

 

[razorblade]

Would it be possible to make it a variable so that i could say like i want to find out Arrivals and departures for Friday October 20th so i'd just change the code to Column X then for the 30th i'd change it to AH

 

[razorblade]

Is anyone having any luck with this?

It doesn't really matter about pairing up the results on Sheet 6 i guess just a list of Arrivals and Departures would be just as good.


Thanks Again

 

[onlyadrafter]

Hello,

this is really long winded and duplicates the second of each pair, but how is it for starters?

Sub arrival_departure()
    MY_DATE = Sheets("Sheet6").Range("A2").Value
    For MY_WORKSHEETS = 1 To 5
        Worksheets(MY_WORKSHEETS).Activate
        For MY_MATCH_DATE = 5 To Range("IV4").End(xlToLeft).Column
            If MY_DATE = Cells(4, MY_MATCH_DATE).Value Then
                For MY_ROWS = 5 To Range("A65536").End(xlUp).Row
                    If Cells(MY_ROWS, MY_MATCH_DATE).Value = "X" Or Cells(MY_ROWS, MY_MATCH_DATE).Value = "A" Then
                    MY_CELLS = Cells(MY_ROWS, MY_MATCH_DATE).Value & Cells(MY_ROWS + 1, MY_MATCH_DATE).Value
                    Select Case Left(MY_CELLS, 1)
                    Case "X"
                        If Right(MY_CELLS, 1) = "A" Then
                            Range("A" & MY_ROWS & ":E" & MY_ROWS).Copy
                                Sheets("Sheet6").Range("F65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
                            Range("A" & MY_ROWS + 1 & ":E" & MY_ROWS + 1).Copy
                                Sheets("Sheet6").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
                        Else
                            Range("A" & MY_ROWS & ":E" & MY_ROWS).Copy
                                Sheets("Sheet6").Range("F65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
                            Sheets("Sheet6").Range("A" & Sheets("sheet6").Range("A65536").End(xlUp).Offset(1, 0).Row).Value = "-"
                        End If
                    Case "A"
                        If Right(MY_CELLS, 1) = "X" Then
                            Range("A" & MY_ROWS & ":E" & MY_ROWS).Copy
                                Sheets("Sheet6").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
                            Range("A" & MY_ROWS + 1 & ":E" & MY_ROWS + 1).Copy
                                Sheets("Sheet6").Range("F65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
                        Else
                            Range("A" & MY_ROWS & ":E" & MY_ROWS).Copy
                                Sheets("Sheet6").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
                            Sheets("Sheet6").Range("F" & Sheets("sheet6").Range("F65536").End(xlUp).Offset(1, 0).Row).Value = "-"
                           End If
                    End Select
                    End If
                Next MY_ROWS
            End If
        Next MY_MATCH_DATE
    Next MY_WORKSHEETS
                        
End Sub

 

[razorblade]

Now for the stupid question...


Where do i put this code and how do i run it etc.

I suck at Macros, Scripts Etc