Hope Someone Can Help

razorblade

New Member
Joined
Oct 9, 2006
Messages
6
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 :oops:


Many thanks in advance

David
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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?
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

New Member
Joined
Oct 9, 2006
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

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

razorblade

New Member
Joined
Oct 9, 2006
Messages
6

ADVERTISEMENT

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

New Member
Joined
Oct 9, 2006
Messages
6
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 :biggrin:
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

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

Code:
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

New Member
Joined
Oct 9, 2006
Messages
6
Now for the stupid question...


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

I suck at Macros, Scripts Etc :oops:
 

Watch MrExcel Video

Forum statistics

Threads
1,112,883
Messages
5,543,017
Members
410,583
Latest member
gazz57
Top