[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

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
0
BrdgBldr
Asked:
BrdgBldr
  • 7
  • 5
4 Solutions
 
Rory ArchibaldCommented:
That should be:

Set TargetRange = TargetWs.Range("A" & TargetLastRow & ":N" & (TargetLastRow + SourceLastRow))
0
 
BrdgBldrAuthor Commented:
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

0
 
Rory ArchibaldCommented:
Line 53 is also missing a Set statement:

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

Note: when you write this:
Dim SourceRange, TargetRange As Range

you only declare TargetRange as a Range - SourceRange is declared as Variant. You should use:

Dim SourceRange As Range, TargetRange As Range
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
BrdgBldrAuthor Commented:
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.
0
 
Rory ArchibaldCommented:
Sorry - typo in my post:

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

Open in new window


Note the .Value has been removed from the end.
0
 
BrdgBldrAuthor Commented:
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

0
 
Rory ArchibaldCommented:
Did you change the declaration of SourceWb?
0
 
BrdgBldrAuthor Commented:
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

0
 
Rory ArchibaldCommented:
Declare SourceWb as Variant, or use:
Sub AddDataFromXlsFile()
    
Dim SourceWb As Workbook, TargetWb As Workbook
Dim SourceWs As Worksheet, TargetWs As Worksheet
Dim SourceLastRow As Long, TargetLastRow As Long
Dim SourceRange As Range, TargetRange As Range
Dim strWB as String

' 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}"
        strWb = Application.GetOpenFilename("Excel Files (*.xls), *.xls") 'Allows for user to select file
        
Set SourceWb = Workbooks.Open(strWb) 'Open source workbook

Open in new window

0
 
BrdgBldrAuthor Commented:
SourceWs should be Worksheet, but don't think this will impact this error..
0
 
BrdgBldrAuthor Commented:
works! :o)))
0
 
BrdgBldrAuthor Commented:
Thanks rorya!  :o)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now