Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination
HI,
The fowling code does not work as designed Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)).Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) However if I changed it to Sheets("Raw Data").Range("B7:F397").Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) It works. MyCol should = 2 because a Find determined that the item I was looking for is in column 2. Any help with this problem would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination
2+4 =6
-- Don Guillett Microsoft MVP Excel SalesAid Software "Charles" wrote in message ... HI, The fowling code does not work as designed Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)).Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) However if I changed it to Sheets("Raw Data").Range("B7:F397").Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) It works. MyCol should = 2 because a Find determined that the item I was looking for is in column 2. Any help with this problem would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination
Hi Charles
Here is an example of how it should work using variables which you can change to suit. Just change the workbook name you are pasting the data to and the variable which gives you the column you require. Take care Marcus Option Explicit Sub Copy() Dim Mycol As Integer Dim MyWkBook As Workbook Dim Myrng As Range Mycol = Cells.Find(What:="Test").Column Set MyWkBook = Workbooks("Book1") 'Change WBook Name Set Myrng = MyWkBook.Sheets("Raw Data").Range("A1") Sheets("Raw Data").Range(Cells(7, Mycol), _ Cells(397, Mycol + 4)).Copy Myrng End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination
If you are running the code from a control on a worksheet or a form then it
will be necessary to qualify your Cells references to the specific worksheet. With Sheets("Raw Data") .Range(.Cells(7, Mycol), .Cells(397, Mycol + 4)).Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) End With Note the dots in front of the Cells keyword that tie the cells directly to the sheet named in the With statement. Otherwise the Cells reference reverts to the object containing the code and will either produce an error or the wrong results. "Charles" wrote in message ... HI, The fowling code does not work as designed Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)).Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) However if I changed it to Sheets("Raw Data").Range("B7:F397").Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) It works. MyCol should = 2 because a Find determined that the item I was looking for is in column 2. Any help with this problem would be appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination
The problem likely lies in the way you are using Cells.
Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)) Here, you are trying create a range on worksheet 'Raw Data' with Sheets("Raw Data").Range but you have not instructed the Cells method which worksheet they are to refer. Cells(7, Mycol) This Cells call does not necessarily point to sheet 'Raw Data'. It might, if Raw Data is the active sheet. But if any other sheet is active, it will point to that sheet, and thus you are attempting to create a range on Raw Data using ranges on another sheet. This obviously won't do, so you get an error. If the code is in one of the Sheet modules, the Cells reference will point to that worksheet, regardless of what sheet is active. A much better way is to use a With statement and qualify all the references to Raw Data with leading periods. With Sheets("Raw Data") .Range(.Cells(7, Mycol), .Cells(397, Mycol + 4)).Copy _ Destination:=whatever End With Pay close attention to the leading periods. They are very important because they point whatever follows them to the object in the With statement. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sat, 13 Feb 2010 12:08:03 -0800 (PST), Charles wrote: HI, The fowling code does not work as designed Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)).Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) However if I changed it to Sheets("Raw Data").Range("B7:F397").Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) It works. MyCol should = 2 because a Find determined that the item I was looking for is in column 2. Any help with this problem would be appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Destination
On Feb 13, 5:39*pm, Chip Pearson wrote:
The problem likely lies in the way you are using Cells. Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)) Here, you are trying create a range on worksheet 'Raw Data' with Sheets("Raw Data").Range but you have not instructed the Cells method which worksheet they are to refer. Cells(7, Mycol) This Cells call does not necessarily point to sheet 'Raw Data'. It might, if Raw Data is the active sheet. But if any other sheet is active, it will point to that sheet, and thus you are attempting to create a range on Raw Data using ranges on another sheet. This obviously won't do, so you get an error. If the code is in one of the Sheet modules, the Cells reference will point to that worksheet, regardless of what sheet is active. A much better way is to use a With statement and qualify all the references to Raw Data with leading periods. With Sheets("Raw Data") * * * * .Range(.Cells(7, Mycol), .Cells(397, Mycol + 4)).Copy_ * * * * * * * *Destination:=whatever End With Pay close attention to the leading periods. They are very important because they point whatever follows them to the object in the With statement. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLCwww.cpearson.com [email on web site] On Sat, 13 Feb 2010 12:08:03 -0800 (PST), Charles wrote: HI, The fowling code does not work as designed Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)).Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) However if I changed it to Sheets("Raw Data").Range("B7:F397").Copy Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng) It works. MyCol should = 2 because a Find determined that the item I was looking for is in column 2. Any help with this problem would be appreciated. Thanks to all who replayed, sorry for the late feedback. I was taken down with the "Flu". Chip's answered my problem, but also learned from the the other replays. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy destination | Excel Programming | |||
Help with copy destination | Excel Programming | |||
VBA .copy destination | Excel Discussion (Misc queries) | |||
Copy Destination fails | Excel Programming | |||
Selection.Copy Destination:= | Excel Programming |