sandramac
asked on
Insert data into another workbook using vba
Hello, I keep getting the type mismatch error on this code i have written. What I have is a useform when the command button is clicked it will open another workbook and input data from the current workbook to the workbook that was just opened. The problem i am having is it is not recognizing the Data1 values which comes from my listbox on my userform. The listbox contains names of all the sheets. For example I select "Jan" in the listbox and press the command button, it will recognize the Listbox value of "Jan" but then when it gets to the copying data from "ah13" to c32 it give me the runtime error, any suggestions. THank you
Private Sub CommandButton5_Click()
Set Data1 = ListBox2
Application.ScreenUpdating = False
Workbooks.Open Filename:="\\Flieaircwt1\e library\20 Temp\QA Statistics Beta"
Workbooks("QA Statistics Beta").Sheets(Data1).Range ("C32") = Workbooks("EOM_Beta").Shee ts(Data1). Range("ah1 3")
Workbooks("QA Statistics Beta").Sheets(Data1).Range ("C33") = Workbooks("EOM_Beta").Shee ts(Data1). Range("ah2 5")
Workbooks("QA Statistics Beta").Sheets(Data1).Range ("C34") = Workbooks("EOM_Beta").Shee ts(Data1). Range("ah3 7")
Workbooks("QA Statistics Beta").Sheets(Data1).Range ("C35") = Workbooks("EOM_Beta").Shee ts(Data1). Range("ah4 9")
Workbooks("QA Statistics Beta").Sheets(Data1).Range ("C36") = Workbooks("EOM_Beta").Shee ts(Data1). Range("ah6 1")
Workbooks("QA Statistics Beta").Sheets(Data1).Range ("C37") = Workbooks("EOM_Beta").Shee ts(Data1). Range("ah7 3")
Private Sub CommandButton5_Click()
Set Data1 = ListBox2
Application.ScreenUpdating
Workbooks.Open Filename:="\\Flieaircwt1\e
Workbooks("QA Statistics Beta").Sheets(Data1).Range
Workbooks("QA Statistics Beta").Sheets(Data1).Range
Workbooks("QA Statistics Beta").Sheets(Data1).Range
Workbooks("QA Statistics Beta").Sheets(Data1).Range
Workbooks("QA Statistics Beta").Sheets(Data1).Range
Workbooks("QA Statistics Beta").Sheets(Data1).Range
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is Data1 defined in your macro? And if so in what way?
Is it like
data1 = listbox1.value
where listbox1 is the name of the listbox.
regards,
Jeroen
Is it like
data1 = listbox1.value
where listbox1 is the name of the listbox.
regards,
Jeroen
Just remove the "set" and leave it as:
data1=listbox1
data1=listbox1
Why remove the Set?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I know what set is used for - I was questioning why you would leave it out
I left Dim out of my second example by mistake, but I prefer to use Dim and Set to properly access the object rather than rely on Variants.
Dim data1 As MSForms.ListBox
Set data1 = Me.ListBox2
I left Dim out of my second example by mistake, but I prefer to use Dim and Set to properly access the object rather than rely on Variants.
Dim data1 As MSForms.ListBox
Set data1 = Me.ListBox2
Ok then:
leave out "set" because you don't want to have data1 to be a listbox object but a string.
You may argue that you WANT data1 to be a listbox, but then (besides the fact that this is not what sandramac is doing) data1 would be totally useless since it would just be an alias for litsbox2 (and you could directly write Sheets(listbox2.Value) in place of Sheets(Data1.Value)).
leave out "set" because you don't want to have data1 to be a listbox object but a string.
You may argue that you WANT data1 to be a listbox, but then (besides the fact that this is not what sandramac is doing) data1 would be totally useless since it would just be an alias for litsbox2 (and you could directly write Sheets(listbox2.Value) in place of Sheets(Data1.Value)).
I didn't think or intend my last post to be offensive - sorry if it came accross that way
Its a matter of techique rather than the final outcome that we are debating so we may as well agree to disagree about how we choose to return the value via data 1
Its a matter of techique rather than the final outcome that we are debating so we may as well agree to disagree about how we choose to return the value via data 1
brettdj, I didn't mean to be offensive either (I was was late for something so I tried to be concise, maybe too much..)
ASKER
Thank you both for the help, it helped me out alot. thanks again
Dim Data1 As OLEObject
Set Data1 = ActiveSheet.OLEObjects("Li
Application.ScreenUpdating
Workbooks.Open Filename:="\\Flieaircwt1\e
Workbooks("QA Statistics Beta.xls").Sheets(Data1.Ob
etc
Cheers
Dave