 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008sheets
I am trying to get a name of a sheet from a cell then go to that sheet to put data into a range of cells. I get an error on the following code: Set b = Sheets(a(4)).Range(Cells(33, 3), Cells(33, 8)) 'b' is a range and 'a' is an array(1 to 8) as string What am I doing wrong? Thanks A range object can not span multiple sheets. The object hierarchy is the application contains workbooks, workbooks contain sheets, and sheets contain ranges. In the same way that a sheet object can not span multiple workbooks a range object can not span multiple sheets.  SignatureHTH... Jim Thomlinson > I am trying to get a name of a sheet from a cell then go to that sheet to put > data into a range of cells. I get an error on the following code: [quoted text clipped - 6 lines] > > Thanks What I am trying to do is copy a range of values in cells to a range in different worksheet. The name of the worksheet is in one of the cells. I thought i could make an array to collect the values in the cells then copy them to another sheet. The array I called 'a'. 'a(4)' contains the name of the sheet that I want to copy the values to. Does this not work: Set b = Sheets(a(4)).Range(Cells(33, 3), Cells(33, 8)) I am trying to set 'b' as the range to copy to. > A range object can not span multiple sheets. The object hierarchy is the > application contains workbooks, workbooks contain sheets, and sheets contain [quoted text clipped - 11 lines] > > > > Thanks The range b can only exist on one sheet at a time. It can not span multiple sheets in the same way that one sheet can not span multiple workbooks. What you need to do is to copy your range of cells and then paste those to each target sheet one at a time... Are you only looking to copy the values??? If so then the code is really very easy as you can just set the values of the copied range to the destination range something like this... with sheets("sheet1").range("A1:B1") 'Range to copy sheets("sheet2").range("A1:B1").Value = .value sheets("sheet3").range("A1:B1").Value = .value sheets("sheet3").range("A2:B2").Value = .value end with SignatureHTH... Jim Thomlinson > What I am trying to do is copy a range of values in cells to a range in > different worksheet. The name of the worksheet is in one of the cells. I [quoted text clipped - 21 lines] > > > > > > Thanks Thanks for your help I'll give that a try > The range b can only exist on one sheet at a time. It can not span multiple > sheets in the same way that one sheet can not span multiple workbooks. What [quoted text clipped - 34 lines] > > > > > > > > Thanks Move the "Sheets(a(4))." part inside in front of the "cells(..." like this, Set b = Range(Sheets(a(4)).Cells(33, 3), Sheets(a(4)).Cells(33, 8)) or With Sheets(a(4)) set b = range(.cells(33,3),.cells(33,8) End With then, of course, a(4) in the a array must contain a string corresponding to an existing Sheet in the workbook. The problem with your code is that the Cells(33,3) refers to the active worsheet, even though you stated the sheet in front of your range. In principle, what you have been writing is Set b = Sheets(a(4)).Range(ActiveSheet.cells(33, 3), ActiveSheet.Cells(33, 8)) Your code actually works if you set a(4) to the active workbook before setting b to your range. regards, Lazzzx >A range object can not span multiple sheets. The object hierarchy is the > application contains workbooks, workbooks contain sheets, and sheets [quoted text clipped - 14 lines] >> >> Thanks Hi if = 1 as string It looks like your trying to select "Sheet14. If so try this Set b = Sheets("sheet" & a & "4").Range(Cells(33, 3), Cells(33, 8)) Mike > I am trying to get a name of a sheet from a cell then go to that sheet to put > data into a range of cells. I get an error on the following code: [quoted text clipped - 6 lines] > > Thanks Take a look at this: http://www.rondebruin.nl/copy2.htm I believe you can modify it (slightly) for your purpose. Regards, Ryan-- SignatureRyGuy > Hi > [quoted text clipped - 14 lines] > > > > Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.