$$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
RE: $$Excel-Macros$$ urgent macro required pls
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
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 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
RE: $$Excel-Macros$$ urgent macro required pls
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 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.quic
Re: $$Excel-Macros$$ urgent macro required pls
Thank You Daniel. I really appreciate it.
On Sun, Jul 31, 2011 at 10:52 AM, Daniel wrote:
> 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 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:
