asked on

Excel VBA error: Method 'Range' of object '_Worksheet' failed

I get the method range of object worksheet failed error with this code here... any fix?

Sub AddDataFromXlsFile()
    
Dim SourceWb, TargetWb As Workbook
Dim SourceWs, TargetWs As Worksheet
Dim SourceLastRow, TargetLastRow As Integer
Dim SourceRange, TargetRange As Range


' Disable automatic calculation
Application.Calculation = xlCalculationManual

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


'set the workbooks
Set TargetWb = ActiveWorkbook
        ChDir "c:\data\"
        'Application.SendKeys "mlrtk_2*.xls{RETURN}"
        SourceWb = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 'Allows for user to select file
        Workbooks.Open SourceWb 'Open source workbook
Set SourceWb = ActiveWorkbook

'set the worksheets
Set SourceWs = SourceWb.Sheets("Sheet1")
Set TargetWs = TargetWb.Sheets("RxData")

TargetWb.Activate
TargetLastRow = TargetWs.Range("A" & Rows.count).End(xlUp).Row + 1

SourceWb.Activate
SourceLastRow = SourceWs.Range("A" & Rows.count).End(xlUp).Row - 1

'format, delete and structure source data
SourceWs.Activate
Columns("C:C").Select
    Selection.Replace What:="~**", Replacement:="A", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Columns("A:B").Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    'Selection.AutoFilter
    
'set source and target range and copy data
SourceRange = SourceWs.Range("A2:N" & SourceLastRow).Value
TargetWb.Activate
TargetRange = TargetWs.Range("A" & TargetLastRow & "N" & (TargetLastRow + SourceLastRow))
'TargetWs.Range("A" & TargetLastRow & "N" & TargetLastRow) = SourceWs.Range("A2:N" & SourceLastRow).Value
TargetRange = SourceRange.Value
SourceWb.Close False

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

Open in new window

screenshot.jpg

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrdgBldr

ASKER

Thanks rorya... now I get through this, so that error is fixed, but I get stopped with Run-time error '424': object required and debug mode highlights yellow:

TargetRange = SourceRange.Value

Open in new window

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrdgBldr

ASKER

Thank you very much rorya. Learned again something with the "Dim" stuff

When running it now, I get the run-time error '424: object required error at another line.
debug mode colours

Set SourceRange = SourceWs.Range("A2:N" & SourceLastRow).Value

Open in new window

yellow

I have amended Dim SourceRange As Range, TargetRange As Range according to your advice.

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrdgBldr

ASKER

rorya.... this turns out to become a major debug story... can i increase to 250 and come up with the next part I'm stuck now?... sorry...

In the meantime I amended the set the workbooks section as follows:

'set the workbooks
Set TargetWb = ActiveWorkbook
        ChDir "c:\data\"
        'Application.SendKeys "mlrtk_2*.xls{RETURN}"
        SourceWb = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 'Allows for user to select file
        If SourceWb = False Then
           MsgBox "You didn't select a file! Code will exit now."
           Exit Sub
        Else
           Workbooks.Open SourceWb 'Open source workbook\
        End If

Open in new window

I get stuck with the error Run-time error '91': Object variable or With block variable not set and debug mode colours yellow:

SourceWb = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 'Allows for user to select file

Open in new window

Rory Archibald

Did you change the declaration of SourceWb?

BrdgBldr

ASKER

Dim SourceWb As Workbook, TargetWb As Workbook
Dim SourceWs As Workbook, TargetWs As Worksheet
Dim SourceLastRow As Integer, TargetLastRow As Integer
Dim SourceRange As Range, TargetRange As Range

Open in new window

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrdgBldr

ASKER

SourceWs should be Worksheet, but don't think this will impact this error..

BrdgBldr

ASKER

works! :o)))

BrdgBldr

ASKER

Thanks rorya! :o)