# Many IF's & copy row..



## Dxmiian (Dec 7, 2022)

Hi,

I'm after a formula to copy a row onto another sheet if cells match and criteria are met.

Here's what I'm trying to achieve.

IF A3 AND G1 = THE SAME & C3 = "Backs" copy row. 

I hope this makes sense. 

I've attached a photo to hopefully make more sense. something to note. G1 is "=Today()" and A3 will be text, I don't know if this changes anything but maybe worth noting.

my goal is to have a separate sheet with each day's info on that I can email out rather than the master sheet that has also earlier days information.


----------



## Versonol (Dec 8, 2022)

I've created a code to transfer the data to a specific sheet : 


```
Set ms = Worksheets("Sheet4") 'Change the sheet name into master sheet name
Set ns = Worksheets("Sheet5") 'Change the sheet name into the new sheet you want to copy the data into
lrow = ms.Cells(Rows.Count, "A").End(xlUp).Row 'Assuming there's no empty date in between in row A
NextRow = Application.WorksheetFunction.CountA(ns.Range("A:A")) + 1 '
For i = 3 To lrow 'Assuming data starts from row 3
If ms.Cells(1, "G") = ms.Cells(i, "A") Then ' Checks if the Date from G1 is the same as each cell in loop
    If ms.Cells(i, "C") = "Backs" Then  'Check if the cells in column c is equal to Backs
        ms.Cells(i, "A").EntireRow.Copy ns.Cells(NextRow, "A")
            NextRow = NextRow + 1
    End If
End If
Next i
```


----------



## Versonol (Dec 8, 2022)

If you want it to automatically create a sheet with the current date and transfer the data there try this :


```
Set ms = Worksheets("Sheet4") 'Change the sheet name into master sheet name
todaydt = Format(Date, "dd mmmm yyyy")
Sheets.Add(After:=ms).Name = todaydt
Set ns = Worksheets(todaydt) 'Change the sheet name into the new sheet you want to copy the data into
lrow = ms.Cells(Rows.Count, "A").End(xlUp).Row 'Assuming there's no empty date in between in row A
NextRow = Application.WorksheetFunction.CountA(ns.Range("A:A")) + 1 '
For i = 3 To lrow 'Assuming data starts from row 3
If ms.Cells(1, "G") = ms.Cells(i, "A") Then ' Checks if the Date from G1 is the same as each cell in loop
    If ms.Cells(i, "C") = "Backs" Then  'Check if the cells in column c is equal to Backs
        ms.Cells(i, "A").EntireRow.Copy ns.Cells(NextRow, "A")
            NextRow = NextRow + 1
    End If
End If
Next i
```
This would work if you don't click the button twice on the same day or if there's no sheet with the same name as the current date


----------



## Dxmiian (Dec 8, 2022)

Versonol said:


> I've created a code to transfer the data to a specific sheet :
> 
> 
> ```
> ...


This is perfect, thank you so much for your help !


----------



## Dxmiian (Dec 12, 2022)

Versonol said:


> I've created a code to transfer the data to a specific sheet :
> 
> 
> ```
> ...




Hi, I may be asking for too much and I apologise in advance, this works just as I want it to, but I have 2 problems when I use this for night shift. The working hours include 2 dates, as the shift is 10pm - 6am 

problem 1 would be including yesterday's date B+A as its 1 shift but then.. 
problem 2 would be separating the shifts that have same date C+B should not be together. even though the dates are the same. 
is there a way to stop looking if next cell doesn't = nights? 
shift 1
A:12/12/2022 10pm - 12pm
B:13/12/2022 12pm - 6am
Shift 2 
C:13/12/2022 10pm - 12pm
D:14/12/2022 12pm - 6am

example in the screenshot being Blue is one night shift over 2 dates and green is next shift but same date as some blue. see how "Days" and "Backs" are in between, is there a way to stop it looking for "nights" with same date if there's others in between?


----------



## Dxmiian (Dec 12, 2022)

Dxmiian said:


> Hi, I may be asking for too much and I apologise in advance, this works just as I want it to, but I have 2 problems when I use this for night shift. The working hours include 2 dates, as the shift is 10pm - 6am
> 
> problem 1 would be including yesterday's date B+A as its 1 shift but then..
> problem 2 would be separating the shifts that have same date C+B should not be together. even though the dates are the same.
> ...


----------



## Dxmiian (Dec 12, 2022)

This maybe easier to understand.

if i press "Nights" button i want it to copy rows with todays date (G1) but for nights it would have to include yesterdays date too. (A9) but not blue rows with yesterdays date as thats a seperate shift..


----------



## Versonol (Dec 15, 2022)

Sorry for the late reply , I had a lot of things on.  Anyways I assume that what you want based on the example if that if you press the button "Nights"  the answer would only copy row 9 and row 10 to the other sheet since it contains the Shift "Nights" and involves in the yesterday and todays date. I also assume that the greens would always be beside each other( +- 1 row) . The result would be from this :



To this :



If so then try this code :


```
Set ms = Worksheets("Sheet4") 'Change the sheet name into master sheet name
Set ns = Worksheets("Sheet5") 'Change the sheet name into the new sheet you want to copy the data into
lrow = ms.Cells(Rows.Count, "A").End(xlUp).Row 'Assuming there's no empty date in between in row A
NextRow = Application.WorksheetFunction.CountA(ns.Range("A:A")) + 1 '
For i = 3 To lrow 'Assuming data starts from row 3
If ms.Cells(1, "G") = ms.Cells(i, "A") Then ' Checks if the Date from G1 is the same as each cell in loop
    If ms.Cells(i - 1, "A") = ms.Cells(1, "G") - 1 And ms.Cells(i, "C") = "Nights" Then
        ms.Cells(i - 1, "A").EntireRow.Copy ns.Cells(NextRow, "A")
            NextRow = NextRow + 1
    End If
    If ms.Cells(i, "C") = "Nights" Then  'Check if the cells in column c is equal to Backs
        ms.Cells(i, "A").EntireRow.Copy ns.Cells(NextRow, "A")
            NextRow = NextRow + 1
    End If

End If
Next i
```


----------

