Opening new file sheets into current sheet

aditi_lic

New Member
Joined
Feb 2, 2013
Messages
25
Hello, Thank You for giving me your precious time.
I have following code to open new files through dialog box and add all sheets from them into current workbook. It is working fine as a macro but when I save it as Add-In and select multiple workbooks to open, it opens only sheets from one workbook.

Sub opensheets()
Dim openfiles
Dim crntfile As Workbook
Set crntfile = ActiveWorkbook
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
openfiles = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls;*.xlsx),*.xls;*.xlsx", _
MultiSelect:=True, Title:="Select Excel files!")


If TypeName(openfiles) = "Boolean" Then
MsgBox "You need to select atleast one file"
GoTo ExitHandler
End If


x = 1
While x <= UBound(openfiles)
Workbooks.Open Filename:=openfiles(x)
Sheets().Move After:=crntfile.Sheets(crntfile.Sheets.Count)

x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub


ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi,

How many files did you try to open at the same time? 2?
I thought X should start at 0, not at 1.

But I might be wrong.
 
Upvote 0
If a code is working as a macro that should also work as an Add-In. Anyways try this code:

Dim wb as Workbook
Dim wa as Workbook
While x <= UBound(openfiles)
Set wa = Application.ActiveWorkbook
wb.Worksheets().Copy After:=wa.Sheets(wa.Sheets.Count)

x = x + 1
Wend

Hope it helps!


ExcelBee
 
Upvote 0
If a code is working as a macro that should also work as an Add-In. Anyways try this code:

Dim wb as Workbook
Dim wa as Workbook
While x <= UBound(openfiles)
Set wa = Application.ActiveWorkbook
wb.Worksheets().Copy After:=wa.Sheets(wa.Sheets.Count)

x = x + 1
Wend

Hope it helps!


ExcelBee

@ Excelbee sorry its not giving any error but opening a single file only.
@DeBeuz its not working with x=0. Any other idea?
 
Upvote 0
Hi,

Sorry, you are correct x should start at 1.
I verified your code and at my place with Excel 2007 it works and opens all files I marked to be openened.
Did you try to debug it and step throug the code?

Do you get any error messages?
Do you see anything happen?
Are the files to be opened protected or normaly accessible?
 
Upvote 0
Hi,

Sorry, you are correct x should start at 1.
I verified your code and at my place with Excel 2007 it works and opens all files I marked to be openened.
Did you try to debug it and step throug the code?

Do you get any error messages?
Do you see anything happen?
Are the files to be opened protected or normaly accessible?

Thank you DeBeuz, As I mentioned earlier it works fine as a macro but does not work as an Add-In. It would be great If you can try it as an Add-In in in your system. Me too using 2007.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top