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