VBA Copy Destination question

[rpmitchell]

Using Excel 2007.

I am trying to copy and paste in a single statement using the Copy Destination:= method. However, I'm not sure how to make it work if I'm trying to copy to an unopened workbook. Right now I'm using:

Range("C30:G30,C36:G36").Select
Selection.Copy
Workbooks.Open Filename:= _
"N:\Financial Statements\AR Days Master file.xlsm"
Sheets("Receiving sheet").Select
Range("B2").Select
ActiveSheet.Paste

I would like to shorten this by simply using the Copy Destination piece. Can anyone show me what this would look like?

 

[cstimart]

Something like this should work...

Dim thisFile As String
Dim thatFile As String
    thisFile = ActiveWorkbook.Name
    thatFile = "AR Days Master file.xlsm"
Workbooks.Open Filename:="N:\Financial Statements\" & thatFile

Windows(thisFile).Activate

Range("C30:G30,C36:G36").Copy (Windows(thatFile).RangeSelection(2, 3))

 

[pedie]

Try

[FONT=Courier New]Dim mybook As Workbook[/FONT]
[FONT=Courier New]Set mybook = Workbooks.Open("C:\Users\Pediez\Desktop\weBook1.xlsm")[/FONT]
[FONT=Courier New]Sheet1.Range("C30:G30,C36:G36").Copy Destination:=mybook.Sheets("Sheet1").Range("B2")[/FONT]

 

[Vladislav]

Re: VBA Copy Destination questio

Something like this...

set wbFrom = thisworkbook
set wbTo = Workbooks.Open(Filename:= "N:\Financial Statements\AR Days Master file.xlsm")
wbFrom.Range("C30:G30,C36:G36").Copy wbTo.Sheets("Receiving sheet".Range("B2")

 

[rpmitchell]

Thanks for the replies! I took the day off today, but will be back at work Monday morning and try them out. Will let you know which ones work.