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

 

[DocAElstein]

Hello,
. The stuff about the relative referencing is discussed further in Thread / Link http://www.mrexcel.com/forum/excel-questions/785490-add-new-rows-visual-basic-applications-2.html ( Andrew Poulsom Sunday 22.04.2014 ) 03:03 AM )
Alan