Adding .Find to code

[Noz2k]

Ok, so I have a bit of code which searches through multiple worksheets (based on a date criteria), copies the data in a row to a 'master' worksheet, and then sums the data and posts it into a summary table.

All that works fine for the past couple of months data that I had to work with, however the problem I am now encountering, is that now I have more of the data from before when I go back a few more months there are rows in the table which didn't exist then, and therefore all the rows are shifted up a few places, so my code then refers to the wrong rows in the sheet.

So I think I need to add a .Find to retrieve the row number (using the headings is column A), but could do with a little help in getting my head around it. Also need to pass if the heading cannot be found in a sheet.

anyway here is my code at the moment

Sub GetSheetData()
Dim ws1         As Worksheet, _
    ws2         As Worksheet, _
    wsm         As Worksheet, _
    p           As String, _
    NumLoops    As Long, _
    WeekSNum    As Long, _
    X           As Long, _
    DT          As Date, _
    DF          As Date, _
    YearStart   As Date
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set wsm = Sheets("Master")
ws1.Range("C5") = CDate(Sheets("Sheet1").txtDateFrom.Value)
ws1.Range("G5") = CDate(Sheets("Sheet1").txtDateTo.Value)
DF = (ws1.Range("C5") + 7) - (Weekday((ws1.Range("C5") + 7), 2))
DT = (ws1.Range("G5") + 7) - (Weekday((ws1.Range("G5") + 7), 2))
YearStart = ws1.Range("K30")
 
Y = 6
Do Until Y = 82
With wsm
   .Cells.Clear
End With
NumLoops = (DT - DF) / 7 + 1
''Sets the value for NumLoops based on 2 date values in sheet1
 
    WeekSNum = (DF - YearStart) / 7 + 1
''Sets the value for WeekSNum based on 2 values in sheet1
 
    X = 0
''Sets the start value for X and Y
Do Until X = NumLoops
p = "Week" & WeekSNum + X
'MsgBox p
If Sheets(p).Range("S3") = "Total" Then
    Sheets(p).Range("C" & Y, "Q" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("S" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("U3") = "Total" Then
    Sheets(p).Range("C" & Y, "S" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("U" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("W3") = "Total" Then
    Sheets(p).Range("C" & Y, "U" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("W" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("Y3") = "Total" Then
    Sheets(p).Range("C" & Y, "X" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("Y" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AA3") = "Total" Then
    Sheets(p).Range("C" & Y, "Z" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AA" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AC3") = "Total" Then
    Sheets(p).Range("C" & Y, "AB" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AC" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AE3") = "Total" Then
    Sheets(p).Range("C" & Y, "AD" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AE" & Y).Copy wsm.Range("AE1").Offset(X, 0)
ElseIf Sheets(p).Range("AG3") = "Total" Then
    Sheets(p).Range("C" & Y, "AF" & Y).Copy wsm.Range("C1").Offset(X, 0)
    Sheets(p).Range("AG" & Y).Copy wsm.Range("AE1").Offset(X, 0)
End If
X = X + 1
Loop
 
    Sheets("Sheet2").Range("C1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("C1:C" & X))
    Sheets("Sheet2").Range("E1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("E1:E" & X))
    Sheets("Sheet2").Range("G1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("G1:G" & X))
    Sheets("Sheet2").Range("I1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("I1:I" & X))
    Sheets("Sheet2").Range("K1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("K1:K" & X))
    Sheets("Sheet2").Range("M1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("M1:M" & X))
    Sheets("Sheet2").Range("O1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("O1:O" & X))
    Sheets("Sheet2").Range("Q1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("Q1:Q" & X))
    Sheets("Sheet2").Range("S1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("S1:S" & X))
    Sheets("Sheet2").Range("U1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("U1:U" & X))
    If Sheets("Sheet2").Range("W3").EntireColumn.Hidden = False Then
    Sheets("Sheet2").Range("W1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("W1:W" & X))
    End If
    If Sheets("Sheet2").Range("Y3").EntireColumn.Hidden = False Then
    Sheets("Sheet2").Range("Y1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("Y1:Y" & X))
    End If
    If Sheets("Sheet2").Range("AA3").EntireColumn.Hidden = False Then
    Sheets("Sheet2").Range("AA1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("AA1:AA" & X))
    End If
    If Sheets("Sheet2").Range("AC3").EntireColumn.Hidden = False Then
    Sheets("Sheet2").Range("AC1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("AC1:AC" & X))
    End If
    Sheets("Sheet2").Range("AE1").Offset(Y - 1, 0) = WorksheetFunction.Sum(Sheets("Master").Range("AE1:AE" & X))
Y = Y + 1
 
Loop
 
End sub

 

[Noz2k]

I think what I need to do, is to place in the Do Until X = NumLoops portion of the code, something which finds the value of ws2.Range("A"&Y) in sheet P, returns the row number (as variable rng), and then just replace all the Y's inside that loop with rng?


Does that make sense?

Not having any luck in doing so at the moment though

 

[Noz2k]

Ok, I sort of have it working, however I need to put in a clause

if rng is nothing then


but am unsure how to say, skip to the next loop?