Activate an variable located workbook and copy 2 ranges?

[will-d]

Dear experts,

I have a program need 2 ranges of data from a file that need to be located
by user.I need to copy and paste them into thisworkbook.

Here is the code to located and get the directory and filename of the
source(it does not open that file for now):

Private Sub CommandButton5_Click()
Dim Filter, Title As String
Filter = "Excel Files (*.xls),*.xls, CSV Files (*.CSV), *.csv,"
Title = "Select Files to Calculate"
With Application
Fname5 = .GetOpenFilename(Filter, FilerIndex, Title, , ture)
End With
TextBox5.Value = Fname5
End Sub

Now I start to copy the first range to thisworkbook, (lrow1 is a variant
stores the length of the first range): First open source file, copy the
range, then activate thisworkbook, and paste the first range into a loaction.

Dim Workbook5 As Workbook
Set WookBook5 = Workbooks.Open(Fname5)
Worksheets("Sum_Page").Activate
ActiveSheet.Range("A2:A" & lrow1).Copy
ThisWorkbook.Activate
Sheets("Last_Mth").Activate
ActiveSheet.Paste Destination:=Worksheets("Last_Mth").Range("A2:A" & lrow1)

Now I need to activate the source file again to copy the 2nd range, but i
can not do it without closing and reopening it. following code does not
working:

Workbook5.activate
or
workbooks(workbook5).activate
or
workbooks.open (Workbook5)
or
workbooks(Fname5).activate

What is the right code to re-activate it without openning it twice?

Thanks

Will

 

[Dave Peterson]

What happens when you use:

Workbook5.activate

Dear experts,

I have a program need 2 ranges of data from a file that need to be located
by user.I need to copy and paste them into thisworkbook.

Here is the code to located and get the directory and filename of the
source(it does not open that file for now):

Private Sub CommandButton5_Click()
Dim Filter, Title As String
Filter = "Excel Files (*.xls),*.xls, CSV Files (*.CSV), *.csv,"
Title = "Select Files to Calculate"
With Application
Fname5 = .GetOpenFilename(Filter, FilerIndex, Title, , ture)
End With
TextBox5.Value = Fname5
End Sub

Now I start to copy the first range to thisworkbook, (lrow1 is a variant
stores the length of the first range): First open source file, copy the
range, then activate thisworkbook, and paste the first range into a loaction.

Dim Workbook5 As Workbook
Set WookBook5 = Workbooks.Open(Fname5)
Worksheets("Sum_Page").Activate
ActiveSheet.Range("A2:A" & lrow1).Copy
ThisWorkbook.Activate
Sheets("Last_Mth").Activate
ActiveSheet.Paste Destination:=Worksheets("Last_Mth").Range("A2:A" & lrow1)

Now I need to activate the source file again to copy the 2nd range, but i
can not do it without closing and reopening it. following code does not
working:

Workbook5.activate
or
workbooks(workbook5).activate
or
workbooks.open (Workbook5)
or
workbooks(Fname5).activate

What is the right code to re-activate it without openning it twice?

Thanks

Will

 

[will-d]

Thanks. it is
Runtime error "91"
Object variable or with block variable not set.

Will

 

[Dave Peterson]

If this line is executed successfully:
Set WookBook5 = Workbooks.Open(Fname5)
Then the workbook5 object variable will have been set.

Maybe it's a problem in the code you didn't share.

 

[will-d]

I have this problem for a while, is it possible my version is too old? I am
using Excel 2002 SP3, and VB 6.3

 

[Dave Peterson]

I would bet that your code is wrong.

But without seeing it, I don't think you'll get any really good answers.

I have this problem for a while, is it possible my version is too old? I am
using Excel 2002 SP3, and VB 6.3

 

[will-d]

Thank you Dave for your patience, I changed "Workbook5" into "Wb5" and it
works now, have a great day!