Copy/paste data between Wkbks without Copy/Paste

[Joshua0317]

Hello. I am trying to speed up macros that were written by people before me. I have basically just removing the Copy/Paste code and using

Sheets(destination).Range(putithere)=Sheets(source).Range(data).value

This works fine for data assignments within a single workbook, but when it comes to getting items from a data workbook and putting the information into a report workbook, the procedure doesn't work. A sample of the original code is below:

Sub Open_Indics()
'
'
'
    Dim h, i, j As Variant
    h = Range("filename")
    i = Range("IndicationWKBK")
    j = Range("IndicationsWKBK2")
    Workbooks.Open Filename:=i, UpdateLinks:=False, ReadOnly:=True
    Windows(h).Activate
    Range("Stateno").Copy
    Windows(j).Activate
    Sheets("States").Select
    Range("O1").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
 
End Sub

I would like to get rid of the 'Activates' and 'Copy' procedures. Something like this:

Sub Open_Indics()
'
'
'
    Dim h, i, j As Variant
    h = Range("filename")
    i = Range("IndicationWKBK")
    j = Range("IndicationsWKBK2")
    Workbooks.Open Filename:=i, UpdateLinks:=False, ReadOnly:=True
 
Windows(j).Sheets("States").Range("O1") = Windows(h).Range("Stateno").Value  
 
End Sub

 

[Weaver]

If you're defining ranges, use set

set i = range("A1:B200")

for example

or if it's the name of a workbook

set i = workbooks("myworkbook.xls")

assuming it's already open

 

[Joshua0317]

I've actually tried to use 'Set' and it still says that the method doesn't work. I've also tried using the 'Workbook' object instead of 'Windows' and that also doesn't work. The message says that the method/property is invalid.

Unless you mean for each range, rename it as a variable, and then attempt to assign one to the other. I haven't tried that but that seems that it wouldn't make any difference seeing the variable would have to have the same properties as the original objects.

Does the workbooks/windows have to be activated in order to set one range as the values of another range?