Cell Address Variable using "Copy"

hawk771960

New Member
Joined
Jun 23, 2015
Messages
10
This should be simple. I am having problems using a variable containing a cell address and 'Copy". Please point out my syntax issue.

Sub CopyDataShip()
Dim Ship As Workbook
Dim mcdb As Workbook
Dim FilePathFileName As String

Dim rKGN As Range
Dim rOSW As Range
Dim sKGN As String
Dim sOSW As String


Application.AskToUpdateLinks = False
Application.CutCopyMode = False
Application.DisplayAlerts = False

FilePathFileName = "\\kgnsrv02\kgn-common\MCDB\MCDB SOP.xlsb"
Set mcdb = ActiveWorkbook
Set Ship = Workbooks.Open(FilePathFileName, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)


Set rKGN = Ship.Sheets("Ship By Loc").Range("A1:Z1000").Find("NNA-Kingston", lookat:=xlPart).Offset(0, 1)
Set rOSW = Ship.Sheets("Ship By Loc").Range("A1:Z1000").Find("OSW-Scrap SP", lookat:=xlPart).Offset(0, 1)

MsgBox "KGN Ship Total " & rKGN.Address & " OSW Ship Scrap " & rOSW.Address

Ship.Sheets("Ship By Loc").Range(rKGN).Copy /***************** Here is my problem ***********************/
mcdb.Sheets("Data Ship").Range("B2").PasteSpecial xlPasteValues

Ship.Sheets("Ship By Loc").Range(rOSW).Copy
mcdb.Sheets("Data Ship").Range("B3").PasteSpecial xlPasteValues

Application.DisplayAlerts = True
Application.CutCopyMode = True
Application.AskToUpdateLinks = True

Ship.Saved = True
Ship.Close


End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
rKGN is a range object in the workbook Ship and the sheet "Ship By Loc" so you don't need or want to qualify it. Try replacing this line:

Ship.Sheets("Ship By Loc").Range(rKGN).Copy

with this:

rKGN.Copy
 
Upvote 0
This is the error message I get. Should have posted this as well. Run-time error 438 Object doesn't support this property or method.
 
Upvote 0
If rKGN definitely contains a value that can be used as a cell/range address try using rKGN.Value.
Code:
Ship.Sheets("Ship By Loc").Range(rKGN.Value).Copy /***************** Here is my problem ***********************/

If you want to copy the range rKGN refers to try this.
Code:
rKGN.Copy
 
Last edited:
Upvote 0
I'm impressed with how quickly you both replied. That was the correct adjustment. What I did not understand, was the variable I declared and populated contains more than the address. It also has information about the worksheet and workbook. That was an important little lesson. Thank you very much.

David
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top