Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Error in referencing workbook macro enabled workbook

0

I have an macro enabled excel workbook with the following code 

Option Explicit
Public dTime As Date
Sub ValueStore()
Dim dTime As Date
    Worksheets("Change").Columns(1).Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Range("Change!C3").Value
    Worksheets("Change").Columns(1).Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Range("Change!C4").Value
    Worksheets("Change").Columns(1).Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Range("Change!C5").Value
      Worksheets("Charts").Columns(1).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Range("Charts!D1").Value
    Worksheets("Charts").Columns(1).Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Range("Charts!D2").Value
    Worksheets("Charts").Columns(1).Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Range("Charts!D3").Value
    Worksheets("Charts").Columns(1).Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Range("Charts!D4").Value
    Call StartTimer
End Sub
Sub StartTimer()
    dTime = Now + TimeValue("00:02:00")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

It works perfectly when it is the only open excel woorkbook on my PC.

When I open any other normal or macro enabled excel woorkbook on my pc the error which reflects is

Run-time error '1004'.

Method 'Range' of object' _ Global' failed

I would really appreciate if I can get a solution for this

Thank you

Viral Goradia

excelmacros-vba
MS Office Version: 2007
posted by: ViralGor (rep: 8) on Thu Jul 21, 2016 at 12:24 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

Please put CODE tags around your code. To do that, edit your post, select your code and click the CODE button.
don (rep: 1989) Jul 21, '16 at 12:26 pm
Add to Discussion

Answers

0
Selected Answer

Try this:

Option Explicit

Public dTime As Date

Sub ValueStore()

Dim dTime As Date

    ThisWorkbook.Worksheets("Change").Columns(1).Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ThisWorkbook.Worksheets("Change").Range("C3").Value

    ThisWorkbook.Worksheets("Change").Columns(1).Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ThisWorkbook.Worksheets("Change").Range("C4").Value

    ThisWorkbook.Worksheets("Change").Columns(1).Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ThisWorkbook.Worksheets("Change").Range("C5").Value

    ThisWorkbook.Worksheets("Charts").Columns(1).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ThisWorkbook.Worksheets("Charts").Range("D1").Value

    ThisWorkbook.Worksheets("Charts").Columns(1).Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ThisWorkbook.Worksheets("Charts").Range("D2").Value

    ThisWorkbook.Worksheets("Charts").Columns(1).Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ThisWorkbook.Worksheets("Charts").Range("D3").Value

    ThisWorkbook.Worksheets("Charts").Columns(1).Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Value = ThisWorkbook.Worksheets("Charts").Range("D4").Value

    Call StartTimer

End Sub

Sub StartTimer()

    dTime = Now + TimeValue("00:02:00")

    Application.OnTime dTime, "ValueStore", Schedule:=True

End Sub

Sub StopTimer()

    On Error Resume Next

    Application.OnTime dTime, "ValueStore", Schedule:=False

End Sub

You just needed to reference ThisWorkbook when saying where to put the data. You will notice that change at the beginning of the lines in the ValueStore macro and also after the equals sign.

posted by: don (rep: 1989) on Thu Jul 21, 2016 at 12:41 pm
Discuss

Discussion

Hi Don

I tried it and it's working perfectly.
Thank you so much :)
ViralGor (rep: 8) Jul 22, '16 at 10:01 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login