wts wrong with this statement

zikr = "" & TextBox1.Text & ""
Workbooks(zikr).Worksheets(zikr).Range("A1:Z600").Select
Selection.Copy
Workbooks("Trend (read only)").Worksheets("Data").Range("A1:Z600").Paste

or

Workbooks(textbox1.text).Worksheets(textbox1.text).Range("A1:Z600").Select

its coming up with an error, the value of textbox1 is the name of the
worksheet, and i have tried both with and without the quotations..

Jason,

You can't select a range on a sheet unless it is active:

Workbooks(zikr).Activate
Worksheets(zikr).Activate
Range("A1:Z600").Select
Selection.Copy

Would work, except you need to have a "Something.xls" as the string passed as the Workbooks
parameter, so perhaps:

Workbooks(zikr & ".xls").Activate
Worksheets(zikr).Activate
Range("A1:Z600").Select
Selection.Copy

This, of course, assumes that you have a workbook that contains a worksheet of the same name.

But, there is no reason to select the range:

Workbooks(zikr & ".xls").Worksheets(zikr).Range("A1:Z600").Copy

should work as well.

However,

Workbooks("Trend (read only)").Worksheets("Data").Range("A1:Z600").Paste

won't work - the paste method works on a worksheet object, not a range object. Something more along
the lines of:

Workbooks(zikr & ".xls").Worksheets(zikr).Range("A1:Z600").Copy _
Workbooks("Trend (read only).xls").Worksheets("Data").Range("A1:Z600")

would work.

HTH,
Bernie
MS Excel MVP


"Jason" <[email protected]> wrote in message
news:[email protected]...
>
>
> zikr = "" & TextBox1.Text & ""
> Workbooks(zikr).Worksheets(zikr).Range("A1:Z600").Select
> Selection.Copy
> Workbooks("Trend (read only)").Worksheets("Data").Range("A1:Z600").Paste
>
> or
>
> Workbooks(textbox1.text).Worksheets(textbox1.text).Range("A1:Z600").Select
>
> its coming up with an error, the value of textbox1 is the name of the
> worksheet, and i have tried both with and without the quotations..
>

thnks, the sheets are already activated, and the textbox has some parameter
to pass on, its a problem with another statement but for some reason it kept
highlighting another line which made me think that i had a problem with the
above statement. but anyway, the point you raised on the last statement about
the pasting, yes thats true, that was my other error. thanks for your
responce, i appreciate it.

"Bernie Deitrick" wrote:

> Jason,
>
> You can't select a range on a sheet unless it is active:
>
> Workbooks(zikr).Activate
> Worksheets(zikr).Activate
> Range("A1:Z600").Select
> Selection.Copy
>
> Would work, except you need to have a "Something.xls" as the string passed as the Workbooks
> parameter, so perhaps:
>
> Workbooks(zikr & ".xls").Activate
> Worksheets(zikr).Activate
> Range("A1:Z600").Select
> Selection.Copy
>
> This, of course, assumes that you have a workbook that contains a worksheet of the same name.
>
> But, there is no reason to select the range:
>
> Workbooks(zikr & ".xls").Worksheets(zikr).Range("A1:Z600").Copy
>
> should work as well.
>
> However,
>
> Workbooks("Trend (read only)").Worksheets("Data").Range("A1:Z600").Paste
>
> won't work - the paste method works on a worksheet object, not a range object. Something more along
> the lines of:
>
> Workbooks(zikr & ".xls").Worksheets(zikr).Range("A1:Z600").Copy _
> Workbooks("Trend (read only).xls").Worksheets("Data").Range("A1:Z600")
>
> would work.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jason" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > zikr = "" & TextBox1.Text & ""
> > Workbooks(zikr).Worksheets(zikr).Range("A1:Z600").Select
> > Selection.Copy
> > Workbooks("Trend (read only)").Worksheets("Data").Range("A1:Z600").Paste
> >
> > or
> >
> > Workbooks(textbox1.text).Worksheets(textbox1.text).Range("A1:Z600").Select
> >
> > its coming up with an error, the value of textbox1 is the name of the
> > worksheet, and i have tried both with and without the quotations..
> >
>
>
>