Copy & Paste across two work books

Question

Evening.

I am attempting to cleanup my code in VBA scripts and have read :-

"Two Main reasons why .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook etc... should be avoided" by Siddharth Rout

I have broken all the rules with the following scripting.

What I am trying to achieve is accomplished by the script. My difficulty in converting the scripts is that the workbook name "CMF Export.xlxs" changes depending on what branch of the business it is sent from.

'PARTNO & FULLDESC copy paste
Windows("HIDEDISPLAY.xlsx").Activate
Range("B:B,I:I").Select
Range("I1").Activate
Selection.Copy
Windows("CMF Export.xlsx").Activate
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I have used the following script get the name of the work book I want to copy to.

Function GetBook() As String
GetBook = ActiveWorkbook.Name
End Function

I just not have any clue on how I can combine this into one working script.

Thanks Mark

Tim Williams · Answer 1 · 2014-01-30T22:51:55.743

1

If there are specific sheets you want to copy from/to:

Workbooks("HIDEDISPLAY.xlsx").Sheets("Sheet1").Range("B:B,I:I").Copy

'copy to named workbook...
Workbooks("CMF Export.xlsx").Sheets("Sheet2").Range("C1").PasteSpecial _
           Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False

'or copy to active workbook...
'If copying to the workbook with the macro, use "ThisWorkbook"
ActiveWorkbook.Sheets("Sheet2").Range("C1").PasteSpecial _
           Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False

edited Jan 30 '14 at 22:51

answered Jan 30 '14 at 22:16

Tim Williams

154,628
8
97
125

@ Tim I will always be copying from "HIDEDISPLAY.xlsx" The workbook that I copy to will be different and is always my active workbook. – Mark Jan 30 '14 at 22:19
See my edit - I would advise using `ThisWorkbook` if the workbook with the macro is the one you're copying to. – Tim Williams Jan 30 '14 at 22:52

score 0 · Accepted Answer · answered Jan 31 '14 at 10:29

@Tim Williams

Thanks for the help. Eventualy got to this that works.

Workbooks("CMF Export.xlsx").Sheets("Sheet1").Range("M:M").Copy
sFile = ActiveWorkbook.Name
'MsgBox "" & ActiveWorkbook.Name
Set wbSource = ActiveWorkbook
ActiveWorkbook.Sheets("Sheet1").Range("BB1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Copy & Paste across two work books

2 Answers2