Hi Raj,
I have copied the following from a site earlier (dont remember the URL) i
had a requirement identical to yours.... Ensure that the consolidation
master file(this macro is present) and the other files are saved in a single
folder....
Other details are self explanatory.... Hope the below code solved your
issue.
==========================
Unfortunately, "consolidate" can mean a lot of different things. The most
common definition I have come across is to copy all the data-filled rows
from the source worksheets to a worksheet in the master workbook. It is
usually presumed that all the files have a one-row header, and this header
should only be copied once to the master sheet. Of course, each source
worksheet's rows should be pasted after the existing rows of the master
worksheet so that data are not overwritten.
If this is the definition you mean, then the following code should be
helpful to you. This is a macro that loops through all the Excel files in
the current working folder, adding each one to the master worksheet. The
master worksheet is presumed to be the first worksheet in the workbook
containing the macro.
Here's the code:
Sub ConsolidateAll()
' This macro opens all Excel files in the working (default) directory,
' one at a time, and and copies all filled rows from the first worksheet
' of each to the first worksheet in this workbook (the workbook containing
' this macro), only copying the header row 1 once.
Dim Filename As String
Dim ConsolWS As Worksheet 'The worksheet where the data are
consolidated
Dim NextRow As Long 'Next available row in ConsolWS worksheet
Set ConsolWS = Worksheets(1)
NextRow = 1
'Look for all files ending with .xls or .xls + any character
'Can include entire path if desired. This example assumes working
'directory so no path specified.
Filename = Dir("*.xls?")
Do While Filename <> ""
If Filename = ThisWorkbook.Name Then GoTo SkipThis
Workbooks.Open Filename
Application.StatusBar = Filename & " added to New workbook."
'Base count of rows in each workbook on the last filled cell
'in column A
Dim LastRow As Long 'Last row in source workbook
LastRow = Range("A65536").End(xlUp).Row
If NextRow = 1 Then
'copy all rows including header (row 1)
Range(Rows(1), Rows(LastRow)).Copy
Destination:=ConsolWS.Rows(NextRow)
NextRow = NextRow + LastRow 'increment nextrow by number of
rows copied
Else
'copy all rows except row 1
Range(Rows(2), Rows(LastRow)).Copy
Destination:=ConsolWS.Rows(NextRow)
NextRow = NextRow + LastRow - 1 'increment nextrow by number of
rows copied
End If
ActiveWorkbook.Close
Application.StatusBar = Filename & " closed."
SkipThis:
'read next filename
Filename = Dir()
Loop
Application.StatusBar = False 'reset statusbar
End Sub
This code should be placed in a standard macro module. This code determines
the number of data-filled rows in each file by looking only at column A. If
column A is empty or does not have data all the way to the last row you want
to consolidate, you should change Range("A65536") to refer to the column
that correctly represents the number of data-filled rows in the source
files.
You mentioned calling this via a button. If you use an ActiveX button (i.e.,
from the Controls Toolbox toolbar), then you can simply call this macro from
the button's Click event, like this:
Private Sub CommandButton1_Click()
ConsolidateAll
End Sub
If you use a Forms button (i.e., from the Forms toolbar) then you can simply
right-click on the button, and use the Assign Macro option to assign it to
the ConsolidateAll macro.
When you run the macro it is important that the folder containing the files
you want to consolidate is the current working folder. If you are not sure
whether it is, or you know it isn't and want to set it, simply use the Excel
File > Open menu to browse to the desired folder. Once there, close the
dialog using the Cancel button. Even though you have cancelled the file
open, the working folder is now set to the folder you just browsed to.
Feel free to follow up if you meant something different by "consolidate"
than what I assumed.
Keep Excelling.
On Fri, Aug 27, 2010 at 6:11 PM, rajasekhar praharaju <
rajasekhar.prahar...@gmail.com> wrote:
> Hi All,
>
> Please assist in this concern i wanted to prepare excel macro where in i
> can consolidate different excel workbooks data into
> one excel workbook. so please send me if it is possible through vba
> macros.if any one of the group is having the script or macro
> for performing this activity please send to this mail id.
>
> rajasekhar.prahar...@gmail.com
>
>
> Regards,
> Raj
>
> --
>
> ----------------------------------------------------------------------------------
> 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
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
Thanks and Regards
Vinod N
http://cavinod.blogspot.com/
--
----------------------------------------------------------------------------------
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
