Hi Roopesh,
Here is the code.
I am assuming that whenever a workbook is opened, the default sheet as data,
that with Header row. If there is no header row, then you just need to add
comment " ' " before a line.
ub mymacro()
Dim path As String
Dim i As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
pasteworkbook = ActiveWorkbook.Name
path = InputBox("Enter the path of MIS files", MIS_consolidation)
With Application.FileSearch
.LookIn = path
.FileType = msoFileTypeExcelWorkbooks
.Execute
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
End If
For i = 1 To .FoundFiles.Count
Application.Workbooks.Open .FoundFiles(i)
copyworkbook = ActiveWorkbook.Name
Range("A1").Select ' Header row if its not, just before start of
line put '
ActiveCell.EntireRow.Delete 'Deleting the first row as its
header, if no Header row then, just before start of line put '
Selection.CurrentRegion.Select
Selection.Copy
Windows(pasteworkbook).Activate
Range("A1").Activate
If Range("A1").Value = "" Then
ActiveSheet.Paste
Else
Range("A1").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
Windows(copyworkbook).Activate
ActiveWorkbook.Close
Next i
End With
MsgBox "Data uploaded into the file", vbOKOnly
Range("A1").Activate
End Sub
Thanks.
Mukesh Chouhan.
Avaya India,
Pune.
On Thu, Mar 5, 2009 at 7:54 AM, Roopesh Kapur <[email protected]>wrote:
>
> Hi Everyone,
>
> I need some help from you all, my requirement is that there are 11 people
> in
> my team and each one uses a different workbook as theri MIS at the end of
> the day I have to combine data from each ones MIS into a master MIS. Few
> Points.
>
> 1) All the workbooks are saved in the same location.
> 2) The format is a standard
>
> Can this be automated, such that at the end of the day, I tell the macro
> the
> path of the folder where the MIS' are saved and then it goes to that
> folder,
> opens each file, copies data from a particular sheet, copies the data in my
> master MIS, closes the MIS and then opens the next one repeats the cycle.
>
> This way I will save lot of time and will get the MIS of all my team one
> below the other.
>
> -----Original Message-----
> From: [email protected] [mailto:[email protected]]
> On Behalf Of Peter Jorgensen
> Sent: Wednesday, March 04, 2009 10:17 PM
> To: [email protected]
> Subject: $$Excel-Macros$$ Re: Find next available row
>
>
> If the next available row is the first blank row on the spreadsheet you can
> use this:
>
> iNextRow = Worksheet("MySheetName").Range("A65535").End(xlUp).Row + 1
>
> Worksheet("MySheetName").Range("A65535").End(xlUp).Row returns the row
> number for the last row in column A with data in it (you can change the
> range to use a different column) and then adding 1 will give you the next
> available row. You can use this then like so:
>
> Worksheet("MySheetName").Range("A" & iNextRow).Value = Date
>
> This will put today's date in the next available row.
>
> -----Original Message-----
> From: [email protected] [mailto:[email protected]]
> On Behalf Of iwasinnihon
> Sent: Wednesday, March 04, 2009 8:39 AM
> To: MS EXCEL AND VBA MACROS
> Subject: $$Excel-Macros$$ Find next available row
>
>
> I am writing a macro and would like to have it find the next available
> row on a sheet, enter the date, do a vlookup, and enter a running
> sum. I can do everything except find the next available row. How
> would I do this?
>
>
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected] or
Ashish Jain @ [email protected]
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
