can't activate open workbook based on cell value

**jseufert** · 08-20-2009, 04:25 PM

I have a spreadsheet that opens an excel file based on the value in a given cell. This works fine. However, I need to pass data from my form to this newly opened file. I can't seem to get this file to become the active workbook so I can complete my paste action. I mind you, the file is already open...so I'm rather puzzled why this isn't working.

I have to call the file based on a variable, as the file names always change.

sub macro23()

Windows("Recon Builder Beta2.xls").Activate
Sheets("Purchase Recon").Select
 Range("L3:L4").copy
 
     purchfile = Range("k1").Value
    
    Windows.Activate Filename:=purchfile
    Sheets("Recon").Select
 
    Range("L3:L4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

end sub

Thanks, JS

**Paul** · 08-20-2009, 04:35 PM

You shouldn't have to activate the workbook to copy or paste the data, as long as it's already open. Perhaps try:

Sub macro23()
purchfile = Workbooks("Recon Builder Beta2.xls").Sheets("Purchase Recon").Range("K1").Value
Workbooks("Recon Builder Beta2.xls").Sheets("Purchase Recon").Range("L3:L4").Copy
Workbooks(purchfile).Sheets("Recon").Range("L3:L4").PasteSpecial Paste:=xlPasteValues
End Sub

**jseufert** · 08-21-2009, 11:25 AM

Thanks. Even that gets stuck at the same place, the paste function. It's selecting the range from the first workbook and doing the copy. But then it goes to debug mode when it goes to the line where the paste is, samething as with my original code.