S
safe
hello all (my first post )
first of all, i'm delighted to have found this forum, having taugh
myself as much as i can of Excel VBA (& enjoyed doing so) its good t
find others with a similar interest
anyway, the reason i got here is, i'm having a problem & woul
appreciate some assistance
i'm trying to use vba to open a workbook, (which i don't know the nam
of so the workbook name is a variable) once open i'm copying a shee
from this workbook into my workbook (so my master workbook is active
the variable workbook isn't) I then wish to close the "variable
workbook so i need to re-activate this workbook to close
the problem i'm having is that i've tried this 2 ways & neither wil
work. i either can't set the variable as a workbook, or i can't cal
the workbook i've set as a variable
the code is as below, this may explain what i'm doing better than i ca
*version 1* - declaring variable as workbook, won't allow me to set th
variable (i get an "object required" error at line 2)
Sub import1()
Dim myfile1 As Workbook
Set myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1
Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Before:=Workbooks("master").Sheets(1)
myfile1.Activate
ActiveWorkbook.Close False
End Sub
*version 2* not delaring variable allows the file to be opened & th
sheet to be imported into the master sheet, but fails at line 7 when
try to re-activate my variable workbook (subscript out of range error
I've tried workbooks(-name-).activate also
Sub import1()
myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1
Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Before:=Workbooks("master").Sheets(1)
Windows(myfile1).Activate
ActiveWorkbook.Close False
i suspect i've made a basic mistake, but as i've said, i'm self taugh
so there are a lot of gaps in my knowledge!
any & all suggestions gratefully received - thank
first of all, i'm delighted to have found this forum, having taugh
myself as much as i can of Excel VBA (& enjoyed doing so) its good t
find others with a similar interest
anyway, the reason i got here is, i'm having a problem & woul
appreciate some assistance
i'm trying to use vba to open a workbook, (which i don't know the nam
of so the workbook name is a variable) once open i'm copying a shee
from this workbook into my workbook (so my master workbook is active
the variable workbook isn't) I then wish to close the "variable
workbook so i need to re-activate this workbook to close
the problem i'm having is that i've tried this 2 ways & neither wil
work. i either can't set the variable as a workbook, or i can't cal
the workbook i've set as a variable
the code is as below, this may explain what i'm doing better than i ca
*version 1* - declaring variable as workbook, won't allow me to set th
variable (i get an "object required" error at line 2)
Sub import1()
Dim myfile1 As Workbook
Set myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1
Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Before:=Workbooks("master").Sheets(1)
myfile1.Activate
ActiveWorkbook.Close False
End Sub
*version 2* not delaring variable allows the file to be opened & th
sheet to be imported into the master sheet, but fails at line 7 when
try to re-activate my variable workbook (subscript out of range error
I've tried workbooks(-name-).activate also
Sub import1()
myfile1 = Application.GetOpenFilename("excel files, *.xls")
Workbooks.Open FileName:=myfile1
Sheets("data").Select
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("data").Select
Sheets("data").Move Before:=Workbooks("master").Sheets(1)
Windows(myfile1).Activate
ActiveWorkbook.Close False
i suspect i've made a basic mistake, but as i've said, i'm self taugh
so there are a lot of gaps in my knowledge!
any & all suggestions gratefully received - thank