i have not tested it ..... check it
Sub consolidatefromdifferentworkbooks()
Application.DisplayAlerts = False
'On Error GoTo abc
Dim ask As Workbook
Dim ask2 As Workbook
Dim ASK3 As Workbook
Set ASK3 = ActiveWorkbook
Dim i As Long
Dim j, hh As Long
Dim shtname As String
Dim N, z, r, s, k As Long
s = 1
k = 1
Dim x As String
Dim temp As String
Dim noofsheets As Integer
noofsheets = 10
Sheets(1).Select
Range("A65356").Select
Selection.End(xlUp).Select
r = ActiveCell.Row
For hh = 1 To noofsheets
Set ask = Workbooks.Add
For i = 2 To r
'Workbooks(Range("a" & i).Value).Windows(1).Visible = False
ASK3.Activate
Sheets(hh).Select
Workbooks.Open Filename:=Sheets(1).Range("a" & i).Value
shtname = Sheets(hh).Name
Set ask2 = ActiveWorkbook
Sheets(1).Select
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
N = ActiveCell.Row
If N >= 2 Then
Rows("6:" & N).Select
Selection.Copy
'Sheets.Add After:=Sheets(Sheets.Count)
ask.Activate
ask.Sheets(1).Activate
Sheets(1).Select
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
z = ActiveCell.Row + 2
Range("A" & z).Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\" & shtname & ".xlsx"
ask2.Activate
ask2.Close
End If
Next i
Next hh
'abc:
'Exit Sub
Application.DisplayAlerts = True
End Sub
On Wed, Nov 3, 2010 at 12:50 PM, Chidurala, Shrinivas <
shrinivas.chidur...@citi.com> wrote:
> Ashish,
>
> I am doing monthly activities as mentioned below by Hiren, but it is more
> then 1 sheet for each excel files. The format of each excel is same for all
> files. Can you please advice on the same.
>
> Find attached format for your reference.
>
> Regards,
> Shrinivas
> Citi® Global Transaction Services - India
> UB City, Canberra Block, # 24, Vittal Mallya Road,
> Bangalore - 56 00 01.
> Ph - +91-80-4144 6339 / 6340
> Email - shrinivas.chidur...@citi.com
>
>
>
> ------------------------------
> *From:* excel-macros@googlegroups.com [mailto:
> excel-mac...@googlegroups.com] *On Behalf Of *ashish koul
> *Sent:* Sunday, October 31, 2010 6:04 PM
>
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ How to consolidate the data using macro
>
> check the attachment
>
> On Sun, Oct 31, 2010 at 9:20 AM, bhavya khanna <bhavya...@gmail.com>wrote:
>
>> Hi,
>>
>> Can someone sent me that in excel 2003 . because i have no 2007 . please
>> send me that sheet in excel 2003
>>
>> ---------- Forwarded message ----------
>> From: ashish koul <koul.ash...@gmail.com>
>> Date: Thu, Oct 28, 2010 at 8:06 PM
>> Subject: Re: $$Excel-Macros$$ How to consolidate the data using macro
>> To: excel-macros@googlegroups.com
>>
>>
>> try attached file see if it helps .....
>>
>>
>> On Thu, Oct 28, 2010 at 3:46 PM, Hiren Sheth <hiren.sh...@viteos.com>wrote:
>>
>>> Hi Excel Gurus,
>>>
>>>
>>>
>>> I would like to prepare the consolidated file on daily basis using macro.
>>> I am using excel 2010.
>>>
>>>
>>>
>>> My team is working daily on some excel files(around 25-50 files). All
>>> excel files are save in a particular folder on daily basis with name
>>> containing the date stamp. I would like to make new consolidated file
>>> containing all data (below row number 5) from Sheet1 of all files in that
>>> folder. Format of sheet1 in all file are same(number of columns are always
>>> be same and header will always be in row 5 but number of rows keep on
>>> changing).
>>>
>>>
>>>
>>> Attached please find the sample blank file. Data need to be consolidated
>>> from such files(all are in same folder) in a new excel file in the same
>>> folder.
>>>
>>>
>>>
>>> Please help.
>>>
>>>
>>>
>>> Thanks
>>>
>>> Hiren
>>> This message is for the named person's use only. It may contain
>>> confidential, proprietary or legally privileged information. No
>>> confidentiality or privilege is waived or lost by any mis-transmission.
>>> If you receive this message in error, please immediately
>>> delete it and all copies of it from your system, destroy any hard copies
>>> of it and notify the sender. You must not, directly or
>>> indirectly, use, disclose, distribute, print, or copy any part of this
>>> message if you are not the intended recipient.
>>> Viteos Capital Market Services Ltd.and any of its subsidiaries each
>>> reserve the right to monitor all e-mail communications through
>>> its networks. Any views expressed in this message are those of the
>>> individual sender, except where the message states otherwise and
>>> the sender is authorized to state them to be the views of any such entity
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> 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/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>>
>>
>>
>>
>> --
>> *Regards*
>> **
>> *Ashish Koul*
>> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
>> *akoul*.wordpress.com <http://akoul.wordpress.com/>
>> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>>
>>
>> P Before printing, think about the environment.
>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> 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/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> 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/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>
>
>
> --
> *Regards*
> **
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
>
> P Before printing, think about the environment.
>
>
> --
>
> ----------------------------------------------------------------------------------
> 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/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
> --
>
> ----------------------------------------------------------------------------------
> 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/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
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/pages/discussexcelcom/160307843985936?v=wall&ref=ts
