Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate an variable located workbook and copy 2 ranges?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate an variable located workbook and copy 2 ranges?
What happens when you use:
Workbook5.activate will-d wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate an variable located workbook and copy 2 ranges?
Thanks. it is
Runtime error "91" Object variable or with block variable not set. Will "Dave Peterson" wrote: What happens when you use: Workbook5.activate will-d wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate an variable located workbook and copy 2 ranges?
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 wrote: Thanks. it is Runtime error "91" Object variable or with block variable not set. Will "Dave Peterson" wrote: What happens when you use: Workbook5.activate will-d wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate an variable located workbook and copy 2 ranges?
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" wrote: 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 wrote: Thanks. it is Runtime error "91" Object variable or with block variable not set. Will "Dave Peterson" wrote: What happens when you use: Workbook5.activate will-d wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate an variable located workbook and copy 2 ranges?
I would bet that your code is wrong.
But without seeing it, I don't think you'll get any really good answers. will-d wrote: 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" wrote: 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 wrote: Thanks. it is Runtime error "91" Object variable or with block variable not set. Will "Dave Peterson" wrote: What happens when you use: Workbook5.activate will-d wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate an variable located workbook and copy 2 ranges?
Thank you Dave for your patience, I changed "Workbook5" into "Wb5" and it
works now, have a great day! "Dave Peterson" wrote: I would bet that your code is wrong. But without seeing it, I don't think you'll get any really good answers. will-d wrote: 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" wrote: 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 wrote: Thanks. it is Runtime error "91" Object variable or with block variable not set. Will "Dave Peterson" wrote: What happens when you use: Workbook5.activate will-d wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Recording a macro to copy into a series of variable ranges | Excel Programming | |||
Activate Macro Button Located In Another File | Excel Programming | |||
Counting variable ranges and auto-summing variable ranges | Excel Programming | |||
Copy variable ranges fron difernte shets | Excel Worksheet Functions |