PDA

View Full Version : Setting 1 range in 1 Workbook equal to another range in another workbook



Soselo
03-21-2018, 05:16 AM
Hi, I am trying to set a range in Workbook 1 equal to another range in Workbook 2 (arbitrary names)

What am I missing?


Sub Kopierikkelåst2()
Dim aaa As Range


Workbooks("Workbook1.xlsm").Activate
Sheets("AK1").Rows("4:1000").Select
Selection.ClearContents

Workbooks("Workbook2.csv").Activate
Sheets("1").Select
Range("B4:B14") = aaa

Workbooks("Workbook1.xlsm").Activate
Sheets("AK1").Select
Range("A1:A10") = aaa

End Sub

SamT
03-21-2018, 09:01 AM
aaa needs to be Set to some range


Workbooks("Workbook2.csv").Activate
Sheets("1").Select
Set aaa = Range("B4:B14")

Oneliner:

Workbooks("Workbook1.xlsm").Sheets("AK1").Range("A1:A10") = Workbooks("Workbook2.csv").Sheets("1").Range("B4:B14")

My Way

Dim Src As Range
Dim Dest As Range

Set Src = Workbooks("Workbook2.csv").Sheets("1").Range("B4:B14")
Set Dest = Workbooks("Workbook1.xlsm").Sheets("AK1").Range("A1:A10")

Dest.Value = Src.Value

rlv
03-21-2018, 09:24 AM
A range (aaa) is an object and you have to use the keyword "set" when assigning an object.



Sub Kopierikkelåst3()
Dim aaa As Range, bbb As Range




Workbooks("Workbook1.xlsm").Activate
Sheets("AK1").Rows("4:1000").Select
Selection.ClearContents
Workbooks("Workbook2.csv").Activate
Sheets("1").Select
Set aaa = Range("B4:B14")
Workbooks("Workbook1.xlsm").Activate
Sheets("AK1").Select
Set bbb = Range("A1:A10")
aaa.Copy bbb
End Sub


Though the example SamT posted above is a much more elegant method.

Soselo
03-26-2018, 06:55 AM
Thanks, I got this to work before I used the replies:

Sub KopierLKK()
'
' Kopier LKK Makro
'
Dim ee As Variant
Windows("abc.xlsm").Activate
Sheets("LKK").Select
Rows("6:10000").Select
Selection.ClearContents
Windows("cde.xls").Activate
Sheets("LKK").Select
Application.WindowState = xlMaximized
ee = Range("B7:Q4000")
Windows("abc.xlsm").Activate
Sheets("LKK").Select
Range("D6:P5000") = ee
End Sub