Workbook.Range Problem

[kylekelsch]

I have a workbook that when I click a button it runs a macro that
allows me to open up another workbook of my choice, then copy a
certain range in the newly opened workbook and paste it into my
workbook that ran the macro. My question is, How can I set a range in
my newly opened workbook as a variable. It works when I do it like
this.

______________________________________________

Windows("MyNewlyOpenedWorkbook").Activate
Range("A1:A5").Select
Selection.Copy

Windows("MyWorkbookWithMacro").Activate
Range("A1:A5").Select
Selection.Paste
______________________________________________

What I would like to be able to do is something like this:


Dim rCopyRange As Range
Dim rPasteRange As Range

Set rCopyRange = MyNewlyOpenedWorkbook.Range("A1:A5")
Set rPasteRange = Range("A1:A5")


Windows(sOpenJobFileName).Activate
rCopyRange.Select
Selection.Copy

Windows("BIDPROG9.xls").Activate
rPasteRange.Select
Selection.Paste

_____________________________________________

So again I ask. How can I name a range on my newly opened workbook?
Any input would relieve this building headache I'm working on.

Thanks

 

[Dave Peterson]

I'd use a variable that represented that newly opened workbook:

Dim myFileName as variant
dim newWkbk as workbook
dim RngToCopy as range
dim DestCell as range

myfilename = application.getopenfilename("Excel files, *.xls")

if myfilename = false then
exit sub 'user hit cancel
end if

set newwkbk = workbooks.open(filename:=myfilename)

set rngtocopy = newwkbk.worksheets("sheet111").range("a1:a5")

set destcell _
= workbooks("MyWorkbookWithMacro.xls").worksheets("sheet999").range("a1")

rngtocopy.copy _
destination:=destcell

 

[kylekelsch]

Thanks Dave that worked great.

Now my worksheet name in the newly opened workbook is different
depending on the workbook opened but there is always only one
worksheet in the workbook. Is there a variable I can use to get the
worksheet name

 

[Dave Peterson]

If it's always the first (and only sheet):

set rngtocopy = newwkbk.worksheets(1).range("a1:a5")

 

[kylekelsch]

Thanks Dave, again that worked great.