copying more than one selection at once

Yeoman.jeremy

Board Regular
Joined
Apr 4, 2011
Messages
90
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

Code:
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 :biggrin:


help would be much appreciated
 
Last edited by a moderator:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
At the moment my coding is

Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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