How to use a variable in another sheet/workbook

[sourabh_ajmera]

Hello everybody,

I have been working on a task where I have to copy some data from cell A1 and A2 in sheet2 of a workbook to a macro that uses it later.
I have declared some variable and the values present in sheet2, cell A1 & A2 is assigned to them.

But it gives me "Runtime error 9 : Script out of Range".

Here's my code:

Sub DepositsMacro()

Dim ws As Worksheet, flg As Boolean
For Each ws In Worksheets
If ws.Name Like "Definition*" Then flg = True: Exit For
Next
If flg = True Then
MsgBox "Definition sheet exists"
Else
Sheets.Add.Name = "Definition"
End If

Dim Response As Integer
' Displays a message box with the yes and no options.
Response = MsgBox("Have you filled in the details required in Definition tab in this workbook? Choose No if you would like to do it now", vbQuestion + vbYesNo)
' If statement to check if the yes button was selected.
If Response = vbNo Then
Exit Sub
End If

Dim strSource As String
Dim strReport As String

strSource = ThisWorkbook.Sheets("Definition").Range("A1") ---> Here's where I get the error
strReport = ThisWorkbook.Sheets("Definition").Range("A2")
.
.
.
Windows(strReport).Activate
Sheets("Summary").Select
Sheets("TD").Visible = True
Windows(strSource).Activate
Sheets("TD").Select
Sheets("TD").Copy Before:=Workbooks(strReport).Sheets(1)
.
.
Is my code correct?? Have I declared and used the variables properly??

Please help me out on this 1. Its a little urgent. I am beginner so please let me know even if its a small mistake.
Thank you in advance!!

 

[Jerry Sullivan]

Hi sourabh_ajmera,

It's not apparent from your code alone whether the ActiveWorkbook when you run the macro is the same one that has the macro code.

ThisWorkbook refers to the workbook that has the code so, if you've added the sheet "Definition" to the ActiveWorkbook and it is not the workbook with the macro code, then this line will error because it is referencing the wrong workbook...

strSource = ThisWorkbook.Sheets("Definition").Range("A1")

If that's the case then this should work...

strSource = ActiveWorkbook.Sheets("Definition").Range("A1").Value

 

[sourabh_ajmera]

Many thanks Jerry! It worked

I have another small doubt. The values are actually 'file names' which are used in the code below. Please let me know how to switch windows/files using the file names passed form those variables??
I want to switch files to that I can copy paste data.

Windows(strReport).Activate
Sheets("Summary").Select
Sheets("TD").Visible = True
Windows(strSource).Activate
Sheets("TD").Select
Sheets("TD").Copy Before:=Workbooks(strReport).Sheets(1)



Thanks in advance...cheers!!

 

[sourabh_ajmera]

Its correct..sorry my bad..something is worng with my Excel...it worked after sometime

Anyways thanks alot for the help above!!
Have a good day everyone