Copy and paste from one sheet to another based on a cells value

[shina67]

Hi All,

I have a spreadsheet that populates the employees time and attendance.
I am trying to create a new tab that will only pull the data for lateness's from the main tab.
[TABLE="width: 1500"]
<tbody>[TR]
[TD="class: xl70, width: 76"]Area[/TD]
[TD="class: xl71, width: 52"]Badge[/TD]
[TD="class: xl71, width: 67"]Payroll No.[/TD]
[TD="class: xl71, width: 226"]Name[/TD]
[TD="class: xl71, width: 47"]Basic[/TD]
[TD="class: xl71, width: 47"]x.1.25[/TD]
[TD="class: xl71, width: 47"]x1.5[/TD]
[TD="class: xl71, width: 47"]x2[/TD]
[TD="class: xl71, width: 47"]O/T[/TD]
[TD="class: xl71, width: 45"]Total[/TD]
[TD="class: xl72, width: 64"]Late[/TD]
[TD="class: xl73, width: 64"]Holiday[/TD]
[TD="class: xl73, width: 64"]Sickness[/TD]
[TD="class: xl73, width: 64"]Bank Holiday[/TD]
[TD="class: xl73, width: 64"]04 Unpaid Holiday[/TD]
[TD="class: xl73, width: 64"]05 Unpaid Sickness[/TD]
[TD="class: xl73, width: 64"]Other absences[/TD]
[TD="class: xl80, width: 75"]Date[/TD]
[TD="class: xl81, width: 75"]Day[/TD]
[TD="class: xl78, width: 64"]Week[/TD]
[TD="class: xl76, width: 73"]Month[/TD]
[TD="class: xl78, width: 64"]Year
[/TD]
[/TR]
</tbody>[/TABLE]

The above is the headers on the main tab.
I am wanting if the column with header "Late" is greater than zero then to copy over the following info:-
Area Badge Payroll No. Name Late Other Absences Date Day Week Month Year

The problem I have is that each day is on a different tab so this would have to look at multiple tabs at the same time and pull the data over.

The aim of this is to capture repeated lateness's from the same employees so that appropriate actions can be taken.

I hope this makes sense and that someone can help.
I thought that Index & Match would work but somehow cannot get it to work.

Thanks in advance.

 

[Yongle]

The problem I have is that each day is on a different tab so this would have to look at multiple tabs at the same time and pull the data over.

Yes - that makes analysing data much harder!

1. How many rows are there in every sheet(approx)?
- fewer than 100?, hundreds?,thousands?
2. How many daily sheets (max) are in the workbook?

 

[shina67]

Hi,

There are approx 120 rows in each sheet and there is approx 170 sheets

 

[Yongle]

For the future - consider using ONE worksheet instead of 170 and add ONE extra column (to contain the current 170 sheet names)
120 rows X 170 sheets = 20,400 rows
One worksheet can handle 1,048,576 rows (65,536 rows before Excel 2007)
It will be easier for you to analyse and summarise data if everything is in one sheet

Summarising LATE
Info from main tab:
Area \ Badge \ Payroll No. \ Name \ Late \ Other Absences
- Is Area in column A ?
- Is 04 Unpaid Holiday in column O ?

Info from daily sheets:
Are sheet names totally consistent in style? (need to understand this to get day,week, month, year)
What is the sheet name for 1st July 2018 ?
Is every sheet except Main tab a daily sheet?
What is the name of main tab?

Day - is this Monday,Tuesday etc OR day of the week (day 1 day 2 etc)
Week - is this calendar week? When did week 1 begin (exact date)?
Month - is this calendar month (in sheet name?)?
Year - is this calendar year (in sheet name)?
(The above questions are asked because some companies use accounting periods NOT calendar months)

Which version of Excel are you using?

 

[Yongle]

If you do not want to use VBA, then this is not for you

Answers to questions asked in post#4 are not required - see below for assumptions made to allow a first stab
If VBA is not consolidating correctly, then I just need to know what is incorrect in my assumptions - thanks

This method consolidates data into a one sheet (original sheets are left intact)
- allowing user to filter by LATE, date, Area etc

Test on a COPY of your workbook

It is assumed that
- sheet "Main" is the FIRST sheet in the workbook, and is empty EXCEPT for headers as as per post#1
- all other sheets (starting at the 2nd sheet) are daily sheets with EXACTLY the same structure as sheet "Main"
- headers are in row 1 in every sheet

What VBA does
- loop through all daily sheets
- copy 15 used columns ("AREA"..."04 Unpaid Holiday") + 1 unused column (= total 16 columns)
- paste to sheet "Main"
- sheet name added to column 16 (column P) in sheet "Main"

What next?
- after getting everything into a single sheet the sheet name in column P can be used to determine Day\Week\Month\Year

Paste code into a standard module
{ALT}{F11} to enter VBA window, followed by {ALT} I M (Insert Module)
{ALT}{F11} to go back to Excel
Save workbook as macro enabled

Sub ConsolidateSheets()
    Dim s As Long, Rng As Range, RngMain As Range
    Optimise (True)
    For s = 2 To ThisWorkbook.Worksheets.Count
        Set Rng = Sheets(s).Range("A1").CurrentRegion.Offset(1)
        Set Rng = Rng.Resize(Rng.Rows.Count - 1, 16)
        Set RngMain = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count, 16)
        Rng.Copy RngMain
        RngMain.Columns(16) = Sheets(s).Name
    Next s
    Optimise (False)
End Sub

Private Sub Optimise(TrueFalse As Boolean)
    With Application
        .ScreenUpdating = Not TrueFalse
        .Calculation = xlCalculationManual
        If TrueFalse = False Then .Calculation = xlCalculationAutomatic
    End With
End Sub

 

[shina67]

For the future - consider using ONE worksheet instead of 170 and add ONE extra column (to contain the current 170 sheet names)
120 rows X 170 sheets = 20,400 rows
One worksheet can handle 1,048,576 rows (65,536 rows before Excel 2007)
It will be easier for you to analyse and summarise data if everything is in one sheet

Summarising LATE
Info from main tab:
Area \ Badge \ Payroll No. \ Name \ Late \ Other Absences
- Is Area in column A ?
- Is 04 Unpaid Holiday in column O ?

Info from daily sheets:
Are sheet names totally consistent in style? (need to understand this to get day,week, month, year)
What is the sheet name for 1st July 2018 ?
Is every sheet except Main tab a daily sheet?
What is the name of main tab?

Day - is this Monday,Tuesday etc OR day of the week (day 1 day 2 etc)
Week - is this calendar week? When did week 1 begin (exact date)?
Month - is this calendar month (in sheet name?)?
Year - is this calendar year (in sheet name)?
(The above questions are asked because some companies use accounting periods NOT calendar months)

Which version of Excel are you using?

Hi Yongle,

Thanks for your help on this.
In answer to your questions:-
- Is Area in column A ? Yes
- Is 04 Unpaid Holiday in column O ? Yes
Are sheet names totally consistent in style? (need to understand this to get day,week, month, year) Yes
What is the sheet name for 1st July 2018 ? 1-07-18
Is every sheet except Main tab a daily sheet? Yes
What is the name of main tab? Late
Day - is this Monday,Tuesday etc OR day of the week (day 1 day 2 etc) Yes
Week - is this calendar week? When did week 1 begin (exact date)? Yes
Month - is this calendar month (in sheet name?)? Yes
Year - is this calendar year (in sheet name)? Yes