ExcelBanter - Copy Destination

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Copy Destination (https://www.excelbanter.com/excel-programming/439517-copy-destination.html)

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.

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

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.

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.

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.