Option Explicit Sub merge_multiple_workbooks() ' DECLARE ALL VARIABLES AND ARRAYS Dim fldpath Dim fld, fil, FSO As Object Dim WKB As Workbook Dim wks As Worksheet Dim shtnames() Dim Paste Dim j As Long, w As Long Dim stcol As String, lastcol As String
stcol = "A"
lastcol = "iv"
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next
fldpath =
Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"
Exit Sub
End If
shtnames = Array("Climate Data", "Product Data", "Salary Details") '\ add
or remove sheets
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.StatusBar = True
Application.StatusBar = "Please wait till Macro merge all the files"
Set FSO = CreateObject("scripting.filesystemobject")
Set fld = FSO.getfolder(fldpath)
For Each fil In fld.Files
If UCase(Right(fil.Path, 4)) = UCase(".xls") And fil.Name <>
ThisWorkbook.Name Then
Set WKB = Workbooks.Open(fil.Path)
For j = LBound(shtnames) To UBound(shtnames)
For Each wks In WKB.Sheets
If wks.Name = shtnames(j) Then
w = WKB.Sheets(shtnames(j)).Range("a65356").End(xlUp).Row
If w >= 2 Then
WKB.Sheets(shtnames(j)).Range(stcol & "2:" & lastcol & w).Copy _
Destination:=ThisWorkbook.Sheets(shtnames(j)).Range("a65356").End(xlUp).Offset(1,
0)
End If
Exit For
End If
Next
Next
WKB.Close
End If
Next
MsgBox "Done"
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
On Thu, Aug 30, 2012 at 7:20 PM, dguillett1 <dguille...@gmail.com> wrote:
> WithOUT looking at your file(s) something like this pseudo code
>
> ‘open each file
> for i=1 to 3
> sheets(i).usedrange copy
> workbooks(“masterfile.xls”).sheets(i).cells(rows.count,1).end(xlup)(2)
> next i
> ‘close each file
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>
> *From:* Krishnaraddi V. Madolli <krishnaraddi.mado...@asia.xchanging.com>
> *Sent:* Thursday, August 30, 2012 8:37 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Macro to consolidate multiple sheets.
>
>
> Hi Experts,****
>
> ****
>
> I have 5 excel workbooks each consisting of 3 sheets called: Climate Data,
> Product Data & Salary Details****
>
> All these 5 excel workbooks are stored in single folder.****
>
> ****
>
> I wanted one consolidated workbook which will be consisting of 3 sheets
> Climate Data, Product Data & Salary Details and data from all 5 workbooks
> has to be consolidated in this workbook.****
>
> ****
>
> ****
>
> I tried but able to extract 1 sheets at a time but not getting all 3
> sheets consolidated at a time, I have attached macro I tried.****
>
> ****
>
> I have almost 300 excel workbooks each consisting of nearly 20 tabs.****
>
> ****
>
> ****
>
> Regards,****
>
> ****
>
> Krishnaraddi V Madolli.****
>
> Data Analytics Team****
>
> Sedgwick Claims Management Services, Inc.
> Xchanging Towers, SJR iPark, ****
>
> EPIP Area, Whitefield ****
>
> Bangalore - 560 066. India.****
>
> Direct Line: +1.800.920.9657 Extn 1915****
>
> Switchboard: +90-(0)80-30540000 Extn 1915****
>
> Email: krishnaraddi.mado...@asia.xchanging.com ****
>
> krishnaraddi.mado...@sedgwickcms.com****
>
> www.sedgwickcms.com<https://outlook.us.xchanging.com/exchweb/bin/redir.asp?URL=http://www.sedgwickcms.com>|
> *The leader in innovative claims and productivity management solutions*
>
> ****
>
> Success is never permanent, Failure is never final, so always do not stop
> effort until your victory makes a history.****
>
> *[image:
> http://images.coolchaser.com/themes/t/404137-i311.photobucket.com-albums-kk467-volleycutiegirl-icon.png]
> **Please consider the environment before printing this message***
>
> ****
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
>
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com.
>
>
>
--
*Regards*
* *
*Ashish Koul*
*Visit*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/*
P Before printing, think about the environment.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
<<image001.png>>
