Open the workbook where the results are to be copied in. The macro adds maps
and shipment sheets so there should be no such sheets in the workbook. Press
the Alt+F11 keys, this opens the VB Editor window. Now, keep in mind that I
use a French version of Excel, so the words I use may not be Exactly those
of an English version. In the left part of the window, look for a project
that has the same name as your workbook. Click on it; click on the “Insert”
menu, then click on “Module”. Paste the below macro in the right part of the
screen and you are done !
Now, running the macro depends of your version of Excel; the simplest way to
do it is to click into the macro and press the F5 key. Please, tell which
version you use and I tell you how to run it from the Excel window.
Sub Consolidate()
'this macro should be placed in the result workbook
Dim inCalculationMode As Integer
Application.ScreenUpdating = False
inCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Const strPath As String = _
"C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\" 'change the path
Dim strFile As String, shMaps As Worksheet, shShipment As Worksheet
Dim arrSheets
arrSheets = Array("maps", "shipment")
Sheets.Add.Name = "maps"
Sheets.Add.Name = "shipment"
'guess headers are in row 1
'guess all columns are equal length
'guess there is data in column A
strFile = Dir(strPath & "*.xls*")
Do While strFile <> ""
Workbooks.Open strPath & strFile
For Each sh In arrSheets
With Sheets(sh)
If ThisWorkbook.Sheets(sh).Range("A1") = "" Then
.Range(.[A1], .Cells(Rows.Count,
1).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(sh).Range("A1").PasteSpecial
xlPasteValues
Else
.Range(.[A2], .Cells(Rows.Count,
1).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(sh).Cells(Rows.Count,
1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
End With
Next sh
ActiveWorkbook.Close False
strFile = Dir
Loop
Application.Calculation = inCalculationMode
Application.ScreenUpdating = True
End Sub
Regards.
Daniel
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de Sara Lee
Envoyé : dimanche 31 juillet 2011 14:03
À : excel-macros@googlegroups.com
Objet : Re: $$Excel-Macros$$ urgent macro required pls
i would like to add rows of respective sheets to be added below one another.
i have never run a macro. Can you please give step by step instruction.
On Sun, Jul 31, 2011 at 5:27 AM, Daniel <dcolarde...@free.fr> wrote:
Hi,
Do you want to add the rows of each sheet below the preceding ones, or do
you intend to consolidate the sheets like you'll do it with Data,
Consolidate menus ?
Regards.
Daniel
-----Message d'origine-----
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de lee
Envoyé : samedi 30 juillet 2011 19:56
À : MS EXCEL AND VBA MACROS
Objet : $$Excel-Macros$$ urgent macro required pls
hello
i have 15 files in one folder. i need to combine two sheets called as maps
and shipment of all the 15 files into one consolidated file with
2 sheets( with respective data). Structure and columns are same in sheets
for all the files.
Can you pls give a macro.. also can you please let m e know on how to run
the macro.... i never ran it.. like do i have to open the first spreadsheet
while running macro
thanks
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip 2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
