1004 Error Setting Range Variable

[stinkingcedar]

Hi everyone,

Just could use some help with the code below:

Option Explicit
Option Compare Text
Private Sub CopyLoadData()


'Variable declaration
Dim StartCellRow As Integer, StartCellColumn As Integer, i As Integer
Dim sht As Worksheet
Dim region As String, RevDate As String
Dim rng As Range, cell As Range, AuditStart As Range


Application.ScreenUpdating = False


'Copies and pastes current REV to previous REV
For Each sht In Sheets(Array("Americas VAR Summary", "International VAR Summary", "Latin America VAR Summary"))


    sht.Range("D4:M38").Copy
    sht.Range("AA4").PasteSpecial (xlPasteValues)
    sht.Range("D41:M71").Copy
    sht.Range("AA41").PasteSpecial (xlPasteValues)
    sht.Range("D74:M104").Copy
    sht.Range("AA74").PasteSpecial (xlPasteValues)
    sht.Range("D107:M137").Copy
    sht.Range("AA107").PasteSpecial (xlPasteValues)
    
Next sht


'Copies and pastes current REV to previous REV
Sheets("Overall VAR Summary").Range("D4:M8").Copy
Sheets("Overall VAR Summary").Range("Z4").PasteSpecial (xlPasteValues)
Sheets("Overall VAR Summary").Range("D12:M46").Copy
Sheets("Overall VAR Summary").Range("Z12").PasteSpecial (xlPasteValues)
Sheets("Overall VAR Summary").Range("D50:M84").Copy
Sheets("Overall VAR Summary").Range("Z50").PasteSpecial (xlPasteValues)
Sheets("Overall VAR Summary").Range("D88:M122").Copy
Sheets("Overall VAR Summary").Range("Z88").PasteSpecial (xlPasteValues)


Sheets("VAR by Discipline").Range("D4:M25").Copy
Sheets("VAR by Discipline").Range("V4").PasteSpecial (xlPasteValues)
Sheets("VAR by Discipline").Range("D29:M50").Copy
Sheets("VAR by Discipline").Range("V29").PasteSpecial (xlPasteValues)
Sheets("VAR by Discipline").Range("D54:M75").Copy
Sheets("VAR by Discipline").Range("V54").PasteSpecial (xlPasteValues)
Sheets("VAR by Discipline").Range("D79:M100").Copy
Sheets("VAR by Discipline").Range("V79").PasteSpecial (xlPasteValues)


Application.CutCopyMode = False


'Clears contents of each data load sheet
For Each sht In Sheets(Array("Americas Data Load", "International Data Load", "Latin America Data Load"))
    
    sht.Range("E4:Z903").ClearContents
    sht.Range("A4:A903").ClearContents
    
Next sht


'Unhides all rows in each summary sheet
For Each sht In Sheets(Array("Overall VAR Summary", "Americas VAR Summary", "International VAR Summary", "Latin America VAR Summary"))


    sht.Cells.EntireRow.Hidden = False
    
Next sht


'Initialization of "i" before loop
i = 0


'Beginning of loop, cycles through each visible (non-hidden) sheet
For Each sht In ThisWorkbook.Worksheets


    If sht.Visible = True Then
    
    'Code is looking for key phrase, will determine which sheets contain it and which don't
    'This is how it differentiates between project detail sheets and other sheets in the workbook
        Set rng = sht.UsedRange.Find("Please DO NOT TOUCH formula driven:", sht.UsedRange.SpecialCells(xlCellTypeLastCell), LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        
            If Not rng Is Nothing Then
            
            'Uses the first project detail tab to pull the REV number and CY
            'Writes this info to the "Overall VAR Summary" sheet
                If i = 0 Then
                
                    RevDate = "REV" & sht.Range("E2") & " CY" & sht.Range("E3")
                    RevDate = Replace(RevDate, 20, "")
                    Sheets("Overall VAR Summary").Range("C2") = RevDate
                    
                End If
                
                '"i" is set to 1 now to make sure only the first detail sheet goes through the loop above
                i = 1
                
                'Code copies the range containing the needed data on each project detail tab
                StartCellRow = rng.Row + 1
                StartCellColumn = rng.Column + 22
                sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy
                
            End If
    
            region = sht.Range("D1").Text
            Set AuditStart = sht.Range(sht.Cells(StartCellRow, StartCellColumn))

You can skim over most of the code in there, the line that is the most important and where I am getting the error message is last line where I attempt to set the range variable AuditStart. It seems like I am just missing something minute but I can't seem to figure it out. Thank you!

 

[RoryA]

You can't pass one Range object as an argument to Range - but you don't need to:

Set AuditStart = sht.Cells(StartCellRow, StartCellColumn)

is sufficient.

Your code currently is trying to pass the value of the cell at sht.Cells(StartCellRow, StartCellColumn) as an address to the sht.Range() property.

 

[JoeMo]

Try replacing the errant line with this:

Set AuditStart = sht.Cells(StartCellRow, StartCellColumn)

 

[stinkingcedar]

Thanks for the replies!

I just tried both of your suggestions and I am still getting the same error

 

[MickG]

Perhaps:-

Set Auditstart = sht.Cells(Startcellrow, Startcellcolumn)

 

[RoryA]

In that case your search value is not being found.

Your code only assigns a value to the row and column variables if it finds the search value, but the rest of the code then tries to use those values even if they were never set. You have a logic problem. I would guess that the rest of the code should all be inside your If...End If block.