copying more than one selection at once

[Yeoman.jeremy]

Hi there,
a part of my workbook requires that i copy some data from one workbook to another, but i'm not sure how to copy more than one selection at once.

I've read something somewhere once about a clipboard, where you can copy many selections and paste them all seperately, but i wouldn't know how to use this, let alone in VBA.

At the moment my coding is

Sub DuplicateComplex_mcr()
Dim wbk1 As Workbook
Dim wbk2 As Workbook
strFirstFile = "c:\Documents and settings\Jan\Desktop\On The Job Sheet"
Set wbk1 = ThisWorkbook
 
Set wbk2 = Workbooks.Open(strFirstFile)
wbk1.Sheets("Job Sheet").Range("B2").Copy
wbk2.Sheets("Job Sheet").Range("B2").PasteSpecial xlPasteValues
End Sub

Which basically copies from ONE cell in a workbook, and pastes to the same ONE cell in another.

How can i copy more than one cell at a time? e.g b2, c2, e4 ,g5 etc?

I know that i could do them all seperately, but i don't want the screen to keep tabbing between the workbooks and give my office ladies epilepsy from the excessive tabbing


help would be much appreciated

 

[Von Pookie]

At the moment my coding is

Sub DuplicateComplex_mcr()
Dim wbk1 As Workbook
Dim wbk2 As Workbook
strFirstFile = "c:\Documents and settings\Jan\Desktop\On The Job Sheet"
Set wbk1 = ThisWorkbook
 
Set wbk2 = Workbooks.Open(strFirstFile)
wbk1.Sheets("Job Sheet").Range("B2").Copy
wbk2.Sheets("Job Sheet").Range("B2").PasteSpecial xlPasteValues
End Sub

Which basically copies from ONE cell in a workbook, and pastes to the same ONE cell in another.

...I know that i could do them all seperately, but i don't want the screen to keep tabbing between the workbooks and give my office ladies epilepsy from the excessive tabbing

As long as all of the necessary workbooks are already open, your "office ladies" shouldn't see any screen "tabbing" if you code things correctly.

As you have your code currently, it won't visibly switch between the workbooks yet it will still paste your data.

How can i copy more than one cell at a time? e.g b2, c2, e4 ,g5 etc?

Depending on what you want to do (for example, where you want the results pasted), you can just reference all of them in one go and paste. If each cell needs to be pasted to a very specific place in the other workbook, you're better off just listing each one and pasting it where it needs to go.

 

[Yeoman.jeremy]

oh ok, i just assumed it might, because it tabs between sheets in a workbook when i do copy paste macros there.

Great, thanks - big help