sourabh_ajmera
New Member
- Joined
- Jul 17, 2014
- Messages
- 36
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![Frown :( :(](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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!!
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!!