Hi Experts,
My requirement is explained below
step 1: (Myfile) is active workbook-->Click Button-->open the Required workbook file -->from that file ,save all sheet name in combo-list box
step 2: Now select sheet name from combo box-->selected sheetname will be paste to "myfile" workbook
Mycode is below
sub maincodeButton_click()
Call module1.callsheet
Call module1.tocopysheet
end sub
-------------------------------------------------------------------
'(Module1 code)
Option Explicit
Public filename As Variant, ddlfilename As Variant
-----------------------------------------------------
'To store sheets name in combo box selection
Public Sub callsheet()
Dim myname(1 To 40) As String
Dim icount As Integer
Dim sheetcount As Integer
Sheet2.sheetnameComboBox.Clear
'opening the workbook file
filename = Application.GetOpenFilename(, , "Select Programme")
Workbooks.Open filename
filename = Mid(filename, InStrRev(filename, "\") + 1)
Application.Workbooks(filename).Activate
'store sheet name in the array variable and put into combo box
sheetcount = Sheets.Count
For icount = 1 To sheetcount
myname(icount) = Sheets(icount).Name
With Sheet2.sheetnameComboBox
.AddItem myname(icount)
End With
Next
End Sub
---------------------------------------------------------
'copy that particular tab in another file
Sub tocopysheet()
Windows(filename).Activate
Sheets(Sheet2.sheetnameComboBox.Value).Select
Cells.Select
Selection.Copy
Windows("myfile").Activate
Sheets("sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Windows(filename).Close
End Sub
------------------------------------------------------------
My problem is..
for First time run --> code ran fine
for Second time run--> am getting error "Subscript out of Range"
only if i click reset button I am able to run
This error because Windows(filename).Activate ,here where variable "filename" was initially empty while calling sub maincodeButton_click()
Always for second run I have to Reset macro .....But i dont know how to over come...please solve my problem
Bookmarks