Macro sheet find

[fari1]

Hi,
I'm having problem with following vba code

Sub Macro1()
'
' Macro1 Macro
'
'
Range("Q2:T12").Select
Selection.Cut
Sheets("Sheet2").Select
Range("G2").Select
ActiveSheet.Paste
Dim SheetNameToFind As String
SheetNameToFind = ActiveSheet.Range("A1").Value
Dim SheetToFind As Worksheet
Set SheetToFind = Sheets(SheetNameToFind)
SheetToFind.Select 'or SheetToFind.activate
ActiveWorkbook.Names.Add Name:="dynamic_range", RefersTo:="=offset($b$2,0,0,counta($b:$h),5)"
ActiveSheet.Range("dynamic_range").Copy
Sheets("Sheet1").Select
Range("a2").Select
Sheets("Sheet1").Paste
Sheets("UI").Select
Range("A1").Select
End Sub


This macro is triggered by a command button (assign macro)
but when i press the button, it gives runtime error and highlights the following line
Set SheetToFind = Sheets(SheetNameToFind)


Regards

 

[Datsmart]

What is in cell A1 of Sheet2?

Your code is setting that cell value as a Worksheet name.

Does a worksheet with that name exist?

 

[fari1]

What is in cell A1 of Sheet2?

Your code is setting that cell value as a Worksheet name.

Does a worksheet with that name exist?

Hi,
Thanks alot for your prompt response.
In the cell A1, there is stock quote, which is dynamic, when the command button is pressed the code is placed in this cell, and yes there's is sheet name with that cell, which is created by my below function. Actually these are two different functions and i merged them both

i want the command button to trigger my another function as well i-e
Dim SheetToFind As Worksheet
Set SheetToFind = Sheets(SheetNameToFind)
SheetToFind.Select 'or SheetToFind.activate
ActiveWorkbook.Names.Add Name:="dynamic_range", RefersTo:="=offset($b$2,0,0,counta($b:$h),5)"
ActiveSheet.Range("dynamic_range").Copy
Sheets("Sheet1").Select
Range("a2").Select
Sheets("Sheet1").Paste


Regards

 

[Datsmart]

and yes there's is sheet name with that cell, which is created by my below function.

What I was asking, is there a worksheet by the name of the "dynamic" stock quote?
That is what your code is trying to do, is activate a worksheet by that name.

By the way, you can shorten your code by not using "Copy", Select" and "Paste".
These four lines of code:

ActiveSheet.Range("dynamic_range").Copy
Sheets("Sheet1").Select
Range("a2").Select
Sheets("Sheet1").Past

can be replaced by this:

ActiveSheet.Range("dynamic_range").Copy Sheets("Sheet1").Range("A2")

 

[fari1]

Thanks for the tip
I dun have a sheet with "dynamic" name.
How to solve my problem now?

 

[Datsmart]

Can you explain what it is that you want to happen?
I am guessing as to what you want to accomplish.

And if you post code, please include all of it with-in Code tags.

Code Tag Instructions:
http://www.mrexcel.com/forum/misc.php?do=bbcode#code

 

[fari1]

There's no option activated in my profile to send you any attachment, in that case, the scenerio would have been clear to you.
let me try to further explain it to you.
My code first copies a certain range in a sheet and paste it into sheet2
This code is run by a command button(not active X control, forms control, using assign macro)

Range("Q2:T12").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("G2").Select
    ActiveSheet.Paste
    Dim SheetNameToFind As String

Then i use this range in offset function to put value in the sheet1 cell $A$1.

and here is my another code, which sees the value inside cell $A$1 of this sheet1(e.g goog) and finds the sheet with that name in my workbook and then create a range using offset function in that sheet and paste it in sheet1

SheetNameToFind = ActiveSheet.Range("A1").Value
Dim SheetToFind As Worksheet
Set SheetToFind = Sheets(SheetNameToFind)
SheetToFind.Select   'or  SheetToFind.activate
ActiveWorkbook.Names.Add Name:="dynamic_range", RefersTo:="=offset($b$2,0,0,counta($b:$h),5)"
ActiveSheet.Range("dynamic_range").Copy Sheets("Sheet1").Range("A2")

but it is giving me the runtime error in this line of code

Set SheetToFind = Sheets(SheetNameToFind)

Hope now you've understood the situation

 

[mikerickson]

The cross post has an attached workbook.

http://www.ozgrid.com/forum/showthread.php?t=154517

 

[Datsmart]

Still have the same question as in Post #2.

What is in cell A1?
Are there characters that are not allowed in a worksheet name?
Can you post an example of this cell value?

 

[Datsmart]

Thanks Mike,

I have downloaded the workbook.

fari1,
Your code takes what is in Worksheet "UI", range "Q2:T12" Cuts and pastes it to Sheet2 cell G2.
If there is nothing in Sheet UI cell Q2, that is what gets pasted to Sheet2 G2. The formula in Sheet1 A1 gets its' value from G2, which was "Pasted" with "nothing".
Your code can not go to a Worksheet name of "nothing".