Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 24th 06, 12:09 PM posted to microsoft.public.excel.worksheet.functions
Jason
 
Posts: n/a
Default 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..


  #2   Report Post  
Old February 24th 06, 02:12 PM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default wts wrong with this statement

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" wrote in message
...


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..



  #3   Report Post  
Old February 24th 06, 02:26 PM posted to microsoft.public.excel.worksheet.functions
Jason
 
Posts: n/a
Default wts wrong with this statement

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" wrote in message
...


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..






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SET statement tutorial Daminc Excel Discussion (Misc queries) 13 January 17th 06 04:47 PM
If Statement linked to cell with VLOOKUP problem - getting wrong v Mike R. Excel Worksheet Functions 4 January 14th 06 02:16 PM
What is wrong with this IF statement? need help. AC man Excel Discussion (Misc queries) 4 January 11th 06 03:29 PM
Do I need a sumif or sum of a vlookup formula? PeterB Excel Worksheet Functions 0 June 1st 05 12:23 PM
Logic statement returns wrong answer. Tony Excel Worksheet Functions 2 December 2nd 04 05:07 AM


All times are GMT +1. The time now is 05:54 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017