VBA error 'Object required'

[kit99]

The code below is returning error: Run-time error '424' Object required, marking line lr = wb_ReportFra.Sheets("g1").Range("A" & Rows.Count).End(xlUp).Row
As far as I can understand I'm pointing out both wb and sh in this line, so what's the problem...
Anyone that can spot what's wrong - and tell me how it should be changed? Or even come up with a better way to find/clearcontents/find/copy/paste like I'm trying to do.

I'm running the code from ThisWorkbook, opening up both wb_reports and wb_groups, copying/pasting data between reports and groups.
First I'm opening up all report files. Then I open up Group1, updates, stores and close down Group1. Then Group2 and so on...
In the end I close Down all report files.

Option Explicit

Sub UpdateAllGroups()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim StartTime As Double
Dim MinutesElapsed As String

StartTime = Timer
    Call OpenUpAllReportFiles
    Call UpdateGr1
    '(several other groups will be here)
    Call CloseDownAllReportFiles
    
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Updates finished in " & MinutesElapsed, vbInformation, "VBA message"
Application.StatusBar = False
End Sub

******************

Private Sub OpenUpAllReportFiles()

Application.StatusBar = "Opening up all report files."
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim fPath, rPath As String
Dim wb_Fra As Workbook
'(several other wb's will be here)

    fPath = ThisWorkbook.Path
        If Right(fPath, 1) = "\" Then
        fPath = Left(fPath, Len(fPath) - 1)
        End If

rPath = "F:\Fast\Ledig\Prod2018\Dash\Lager\Rapporter\"
Set wb_Fra = Workbooks.Open(rPath & "ReportFra.xlsm")
'(several other reports will be here)

Application.ScreenUpdating = False
Application.StatusBar = False
End Sub

*****************

Private Sub UpdateGr1()

Application.StatusBar = "Now updating: g1   (opening file)."
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim fPath As String, lr As Range
Dim wb_gr, wb_ReportFra As Workbook
Dim sh_1_fra, sh_Dash As Worksheet
'(several other wb's and sh's will be here)

    fPath = ThisWorkbook.Path
        If Right(fPath, 1) = "\" Then
        fPath = Left(fPath, Len(fPath) - 1)
        End If

'SET WB OG SH
Set wb_gr = Workbooks.Open(ThisWorkbook.Path & "\Group1.xlsm")
    With wb_gr
        Set sh_1_fra = .Worksheets("1-fra")
        '(several other sheets will be here)
    End With
        
Application.StatusBar = "Now updating: g1   (new report files)."
'UPDATE GR1 WITH NEW REPORTS   (lr is to find LastRow)
        'clear contents in sh/table to paste new data into
        lr = sh_1_fra.Range("A" & Rows.Count).End(xlUp).Row
        sh_1_fra.Range("A4:J" & lr).ClearContents
        
            'get new data from ReportFra.xlsm sh g1, paste to wb_gr sh_1_fra
            [B]lr = wb_ReportFra.Sheets("g1").Range("A" & Rows.Count).End(xlUp).Row[/B]
            wb_ReportFra.Sheets("g1").Range("A5:J" & lr).Copy Destination:=sh_1_fra.Range("A3").PasteSpecial
            Application.CutCopyMode = False
        
'STORE AND CLOSE DOWN GR-FILE
Application.StatusBar = "Now updating: g1 (store and close file)"
Application.Goto sh_Dash.Range("A1"), True
wb_gr.Save
wb_gr.Close False
Application.StatusBar = False
End Sub

******************

Private Sub CloseDownAllReportFiles()

Application.StatusBar = "Closing down all report files."
Application.ScreenUpdating = False
Application.DisplayAlerts = False

            Workbooks("Fra.xlsm").Close savechanges:=False
            '(closing down several other reports here)
            
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = False
End Sub

 

[My Aswer Is This]

Re: Help on vba error 'Object required'

You really have a sheet named: "g1" ?

 

[Norie]

Re: Help on vba error 'Object required'

You never set wb_ReportFra to refer to a workbook in the posted code, so unless you do that elsewhere that could be your problem.

 

[kit99]

Re: Help on vba error 'Object required'

You really have a sheet named: "g1" ?

In workbook "ReportFra.xlsm" I have a sheet named "g1", yes.

 

[kit99]

Re: Help on vba error 'Object required'

You never set wb_ReportFra to refer to a workbook in the posted code, so unless you do that elsewhere that could be your problem.

Changed to:

'get new data from ReportFra.xlsm sh g1, paste to wb_gr sh_1_fra
 [B]Set wb_Fra = Workbooks("Fra.xlsm")[/B]
 lr = wb_ReportFra.Sheets("g1").Range("A" & Rows.Count).End(xlUp).Row
 wb_ReportFra.Sheets("g1").Range("A5:J" & lr).Copy
            
 sh_1_fra.Range("A3").PasteSpecial
 Application.CutCopyMode = False

... and it worked just fine!
Thanks a lot for helping out.