Trying to Copy and Paste multiple Ranges

[DocAElstein]

Hello,
I’m trying to speed up a bit of regular file updating (that involves simply copying various bits of one workbook and pasting them in the same places / ranges in another workbook) using the VBA Range stuff.. My first attempt did not work. The second attempt did. I think I have an idea wot the problem is. However I am still learning VBA and would be grateful if a profi could tell me if I am on the right lines with my explanation of why the first code didn’t work and the second code did.,


I will demonstrate the problem as follows: I have opened 2 workbooks (Mappe1) and (Mappe2) and put some arbitrary numbers in the first few cells in the first sheet (Tabelle1) of Mappe1. For the sake of argument say I want to copy the ranges A1:C1 and A3:C3 from Mappe1 to the corresponding ranges in sheet1 (Tabelle1) of the second workbook Mappe2

This first attempt did not work

Sub CopyandPasteRangesFromMappe1ToMappe2_DontWork()  [COLOR=#008000]'_____________________________________________-
'    Copy and Paste Range A1 to C1[/COLOR]
Dim RangeA1toC1 As Range, RangeA3toB3 As Range
 
Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Activate
Set RangeA1toC1 = Range("A1:C1")
Set RangeA3toC3 = Range("A3:C3")
RangeA1toC1.Select
Application.CutCopyMode = False
Selection.Copy
Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Activate
RangeA1toC1.Select   [COLOR=#008000] ' Error comes here!!--"Can't do the select method of the range object"[/COLOR]
ActiveSheet.Paste
[COLOR=#008000]'
'    Copy and paste Range A3 to C3[/COLOR]
Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Activate
RangeA3toC3.Select
Application.CutCopyMode = False
Selection.Copy
Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Activate
RangeA3toC3.Select
ActiveSheet.Paste
'
End Sub [COLOR=#008000]'CopyandPasteRangesFromMappe1ToMappe2_DontWork()'_______________________[/COLOR]

This second attempt did work

Sub CopyTotalRangesFromMappe1ToMappe2_Works()  [COLOR=#008000]'_____________________________________________-
'    Copy and Paste Range A1 to C1[/COLOR]
Dim RangeA1toC1 As Range, RangeA3toB3 As Range
Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Activate
Set RangeA1toC1 = Range("A1:C1")
Set RangeA3toC3 = Range("A3:C3")
RangeA1toC1.Select
Application.CutCopyMode = False
Selection.Copy
Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Activate
Set RangeA1toC1 = Range("A1:C1")
RangeA1toC1.Select
ActiveSheet.Paste
[COLOR=#008000]'
'    Copy and paste Range A3 to C3[/COLOR]
Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Activate
RangeA3toC3.Select
Application.CutCopyMode = False
Selection.Copy
Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Activate
Set RangeA3toC3 = Range("A3:C3")
RangeA3toC3.Select
ActiveSheet.Paste
'
 
 
End Sub[COLOR=#008000] 'CopyTotalRangesFromMappe1ToMappe2_Works()[/COLOR]

My attempt at the explanation was the following:

By using Set Range I am only defining that range in the workbook that is at that time activated. So I have to “set” it again after activating the second Workbook. Have I got the right idea??
Thanks,
Alan Elston.

P.s. 1) As in the practice I am copying lots of different ranges, I tried to do something clever using the Union stuff in order to copy the entire ranges simultaneously and then pasting them simultaneously. That did not work! Am I right in saying that that was most likely because the Clipboard stores everything as a single long sequential list and so by pasting is confused by having multiple ranges selected?

P.s.2) In the practice my actual Macro usually does not work the first time I “Play” it? But usually works the second time that I “play” it. I could not unfortunately reproduce this problem in the example code above. Could it be that I have missed out some important initialising command etc? I would be happy to send the files over, but they are a bit big and not particularly clear or “user friendly” as I’m new to VBA and am working privately on a very complicated private project

P.s.3) Here are Files / Workbooks, Mappe1.xlsx and Mappe2.xlsx

FileSnack | Easy file sharing

FileSnack | Easy file sharing

… And here is a third File / workbook , Mappe3.xlsm , with the Macros in

FileSnack | Easy file sharing

 

[wigi]

Your explanation is right.
Your coding can be improved a great deal.

Sub wigi()

    Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("A1:C1").Copy Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Range("A1:C1")
    Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("A3:C3").Copy Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Range("A3:C3")

End Sub

 

[wigi]

Or:

Sub wigi2()    
    With Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("A1:C1")
        .Copy Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Range("A1:C1")
        .Offset(2).Copy Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Range("A3:C3")
    End With


End Sub


Sub wigi3()


    Set rngSource = Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("A1:C1")
    Set rngTarget = Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Range("A1:C1")
    
    rngSource.Copy rngTarget
    rngSource.Offset(2).Copy rngTarget.Offset(2)


End Sub

 

[DocAElstein]

Hi
. Thanks, great! I tried all your Codes out (-obviously they worked!!). Your first code, wigi1() looks a bit like some code I was racking my brains out trying to remember / find. I think / thought it looks something like……

[    Copy what:- Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("A1:C1") _
    Destination:- Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Range("A1:C1")/CODE]
……but I couldn’t get the syntax right??
 
.   The offset trick in wigi2() and wigi3() was a neat idea (for when the ranges are the same size and shape), Thanks. I’m continually impressed how many ways there are to do things. I’m grateful for the Improvements. - Those sort of simplifications are what I always try to achieve… But I’m new to VBA and lack the experience just yet..  
 
.   To understand your third code wigi3() fully, I need to brush up on what   Set   is as opposed to    Let    - I would have written it as        
 
[CODE]    Sub wigi3Alan()
 
  Dim rngSource As Range, rngTarget As Range
 
    Let rngSource = Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Range("A1:C1")
    Let rngTarget = Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Range("A1:C1")
   
    rngSource.Copy rngTarget
    rngSource.Offset(2).Copy rngTarget.Offset(2)
 
 
End Sub

…. Which doesn’t work!!. ( Maybe the answer is that Let defines a variable, and Set defines an Object????)

Thanks again
Alan Elston,
Bavaria
Germany

 

[DocAElstein]

. I think I just about get it now.

1) Wigi3() (apart from the clever offset stuff) wasn’t too far off what I was trying to get at originally. I didn’t see that because Excel (especially newer versions) probably has the experience to guess often what you want, so you can leave bits out. Experienced users know that and so keep everything as simple and short as possible. I tend to keep everything in for now while I’m getting the point of the OOP hierarchy stuff.
2) Set and Let are a bit (or lots!?) different. You “set” something up, for example an Object. Maybe Let sort of allows or enables or gives it something, like a property!?. (There’s probably a bit more to it than that so I’ll keep reading stuff! ). Maybe the last bit of the following code shows my way of thinking (at the moment!)

[COLOR=#0000FF]Sub[/COLOR] wigiAlan4()
[COLOR=#008000]' Define type, allocate Memory space[/COLOR]
 [COLOR=#0000FF] Dim[/COLOR] rngSource [COLOR=#0000FF]As[/COLOR] Range, rngTarget [COLOR=#0000FF]As[/COLOR] Range
 
[COLOR=#008000]  ' Give the things something specific... "Set" them up!![/COLOR]
    [COLOR=#0000FF]Set [/COLOR]rngSource = Excel.Application.Workbooks("Mappe1.xlsx").Worksheets("Tabelle1").Cells(1, 1).Range("A1:C1")
  [COLOR=#0000FF]  Set[/COLOR] rngTarget = Excel.Application.Workbooks("Mappe2.xlsx").Worksheets("Tabelle1").Cells(1, 1).Range("A1:C1")
 
 
[COLOR=#008000]  ' The working Code:-[/COLOR]
    rngSource.Copy Destination:=rngTarget
    rngSource.Offset(2, 0).Copy Destination:=rngTarget.Offset(2, 0)
[COLOR=#008000]    ' The " 0 " will be assumed (the default) if you do not type it
    ' The "Destination:=" will be assumed (the default) if you do not type it[/COLOR]
   
   
[COLOR=#008000]' Now Clear the cells demonstrating    Let     as alternative to      .Value[/COLOR]
    MsgBox ("Ready to clear the cells in Mappe2?")
  [COLOR=#0000FF]  Let[/COLOR] rngTarget = ""     [COLOR=#008000]' Old fashioned Basic- "stick the value in"!![/COLOR]
    rngTarget.Offset(2, 0).Value = ""  [COLOR=#008000]'  OOP.. VBA..   "Dot" Hierarchy stuff!   "Give the property Value of RngTarget Object one of it's possible properties"  (in this case nothing, that is to say something… that something is ... empty [/COLOR]‹(•¿•)›    !?
 
 
[COLOR=#0000FF]End Sub[/COLOR] [COLOR=#008000]'  wigiAlan4()[/COLOR]

 

[wigi]

Hello

Good that you try to learn something! But in your last post, you're missing out on a few points...

Excel.Application is really superfluous. Leave it out, unless you fancy some (more) RSI.
Cells(1, 1): leave it out as it can be wrong if you change 1, 1. For example, in a blank sheet, run this:

MsgBox Cells(1, 1).Range("A1:C1").Address

then run this:

MsgBox Cells(10, 5).Range("A1:C1").Address

Do you see?

You can leave out "Destination:=" if you want.

Your last two statements.. I don't follow. If you want to clear the memory allocated to the Range variables:

Set rngSource = Nothing
Set rngTarget = Nothing

Do not use Let.

rngTarget.Offset(2, 0).Value = ""

will clear cells on the sheet. If it will NOT see a range to Nothing and remove from memory.

 

[DocAElstein]

Hi Wigi, thanks for reply..

1. “Excel.appication” RSI stuff !!

….. yeah, I suppose Excel.applicastion is overdoing it.. I suppose you must be in the application of Excel to be there writing the code in the first place!?. Then maybe you only need something in that form like Word.Application when you start playing around with the applications of other Office stuff. I haven’t got that far yet!! (so I’ll cut down on hand wonking and get a bit more “real” there)

2. Your 2 MsgBox Codes:-

……..I don’t see what’s wrong with those 2 codes … I tried them…they give me the right answer. (Or wot I in my ignorance think is right!?!) The first time I get $a$1:$c$1 which is range A1:C1 starting at ( 1,1 ). The second gives me $e$10:$g$10 which is the address of Range A1:C1 staring at ( 10, 5 ) ?!? Maybe I got it wrong again… I thought it’s one of these two things:

Either​

you have a Range object of a worksheet where you then are referencing the coordinates A, B , C etc. written up there on the worksheet and 1 , 2 , 3 etc.. Written there on the left of the worksheet., (Like the co ordintes on a map , (just the wrong way around)) and then a range object of a cell where the co ordinates are similarly written but are relative to the cell

Or​

You only have a range object of a cell… if you don’t include a cell, it assumes the defaualt cell (1,1)
(one of the nice things about participating in this forum is I sometimes realize and answer simultaneously some of my other problems--… It just me reminded me that I had some inconsistent codes that looked Ok but only sometimes worked…. I found a bodge by trial and error that made the codes consistently work…. That bodge was to select cell ( 1,1 ) from time to time!! So maybe there were places where Cells( 1 , 1) should have been included to sort of “initialize it”)


3. The “Nothing" stuff:
……..I was only trying to clear the cell on the sheet, ( and just for fun I did it with the two methods a)“putting it" to “” with Let.. b) C - “giving the “property” of the cell object as empty… I think because I’m new to the VBA lanquage I worded it badly.. I should not have used the word “Nothing” as thanks to your reply I see now that in VBA the “nothing” thing means something specific, that is to say clear the memory allocated.
So I learnt something again.
Thanks
Alan

 

[wigi]

Your point 2:

People generally don't use relative referencing. The second code with the MsgBox is not clear and you would use E10:G10 instead. Or, use .Resize(1, 3) to go from 1 cell to 3 cells (next to each other).

 

[DocAElstein]

Your point 2:

People generally don't use relative referencing. The second code with the MsgBox is not clear and you would use E10:G10 instead. Or, use .Resize(1, 3) to go from 1 cell to 3 cells (next to each other).

Hi (Again!!) Wigi.

Point 2:-
. So wot your saying is that I might be technically right, but in the practice it looks confusing “so don’t do it”

Thanks again for the benefit of your experience!! I appreciate it

Alan.

 

[wigi]

Your welcome, people like me love to share the knowledge and experience.