I have two workbooks. One I know the name of. The other one I don't because they are emailed to me.
I NEED TO SET ACTIVE THE ONE I DONT KNOW THE NAME OF.

wb1.xls - name never changes

?.xls - this name will be changing all the time since I have to work with many workbooks.

I have a macro built to copy/paste from ?.xls to wb1.xls so in order to copy and paste I have to make the ?.xls the active one. How can I code it so when I run the macro, it either makes whatever name ?.xls has the active wk or prompts me for the name of the ?.xls and passes it to the macro as the active wk?

This is the code I have and even thou it worked, it just started failing at the *** indicated below and I can't seem to be able to fix it.

Sub PasteRanges()
'MsgBox "make sure the ACTIVE sheet is the SOURCE sheet!", vbOKCancel
Const rng1 = "D9"
Const rng2 = "O6:O8"
Const rng3 = "A17:A25"
Const rng4 = "G18:G25"
Const rng5 = "I16:AC16"
Const rng6 = "I21:AC25"
Const rng7 = "AG17"
Const rng8 = "AG21:AG26"
'
If ActiveWorkbook.Name <> "Forecast By Cost ALL Master Macro 5-31-06.xls" Then
'Assume ACTIVE sheet is SOURCE sheet
With ThisWorkbook.Worksheets(ActiveSheet.Name) ***** FAILS HERE
.Range(rng1).Value = ActiveSheet.Range(rng1).Value
.Range(rng2).Value = ActiveSheet.Range(rng2).Value
.Range(rng3).Value = ActiveSheet.Range(rng3).Value
.Range(rng4).Value = ActiveSheet.Range(rng4).Value
.Range(rng5).Value = ActiveSheet.Range(rng5).Value
.Range(rng6).Value = ActiveSheet.Range(rng6).Value
.Range(rng7).Value = ActiveSheet.Range(rng7).Value
.Range(rng8).Value = ActiveSheet.Range(rng8).Value
'.Range(rng9).Value = ActiveSheet.Range(rng9).Value
End With
Else: MsgBox "Click the other workbook.!", vbOKOnly
Exit Sub
'
End If
End Sub

thanks for any help you can give
:(