hi
try this code if the sheets postion or names are fixed from which data is to
be merged in all workbooks
then simply change below yellow marked line currenty it will copy on sheet
1 from all the workbooks.
2nd - once you get all the file names to be merged you can manually delete
that row, from macro workbook , then macro will not copy the data from that
workbook.
other way is that you can remove duplicates at end using advance filter or
remove duplicates options
we can still find other alternatives then if none of above works
let me know if any other questions.
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 As Long
Dim N, z, r, s, k As Long
s = 1
k = 1
Dim x As String
Dim temp As String
Sheets(1).Select
Range("A65356").Select
Selection.End(xlUp).Select
r = ActiveCell.Row
Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range("b2").Value
Set ask = ActiveWorkbook
For i = 2 To r
'Workbooks(Range("a" & i).Value).Windows(1).Visible = False
ASK3.Activate
Sheets(1).Select
Workbooks.Open Filename:=Sheets(1).Range("a" & i).Value
Set ask2 = ActiveWorkbook
Sheets(1).Select
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
N = ActiveCell.Row
If N >= 2 Then
Rows("1:" & 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.Save
ask2.Activate
ask2.Close
End If
Next i
'abc:
'Exit Sub
Application.DisplayAlerts = True
End Sub
On Thu, Jan 27, 2011 at 2:01 PM, Seba <sebastjan.hri...@gmail.com> wrote:
> Hi Ashish,
>
> I can't thank you enough for this great solution:)
> I'd hate to be a pain in the neck but I do have a couple of questions
> more.
>
> Would it be possible to specify only one sheet to be merged? My
> workbooks contain several, but only one is relevant for the database.
> And, if I run this macro once per month I guess there would be double
> or triple entries and so forth for the same workbook in the database.
> I guess one workaround is to manually delete the files that have
> already been merged from the list the macro makes.
> But can this be done automatically?
>
> Thank you.
>
> regards,
> seba
>
>
> On 27 jan., 07:07, ashish koul <koul.ash...@gmail.com> wrote:
> > 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 As Long
> > Dim N, z, r, s, k, d As Long
> > s = 1
> > k = 1
> > Dim x As String
> > Dim temp As String
> >
> > Dim sht As Worksheet
> >
> > Set ask2 = ActiveWorkbook
> > Sheets(1).Select
> > Range("A65356").Select
> > Selection.End(xlUp).Select
> > r = ActiveCell.Row
> >
> > Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range("b2").Value
> > Set ask = ActiveWorkbook
> >
> > For i = 2 To r
> > 'Workbooks(Range("a" & i).Value).Windows(1).Visible = False
> >
> > ASK3.Activate
> > Sheets(1).Select
> > Workbooks.Open Filename:=Sheets(1).Range("a" & i).Value
> > Set ask2 = ActiveWorkbook
> >
> > For d = 1 To ask2.Sheets.Count
> >
> > Sheets(d).Activate
> > Sheets(d).Select
> > Range("A1").Select
> >
> > ActiveCell.SpecialCells(xlLastCell).Select
> >
> > ' Selection.End(xlToRight).Select
> > '
> > '
> > 'temp = ActiveCell.Address
> > 'x = Mid(temp, 2, (InStr(2, temp, "$") - 2))
> > '
> > '
> > '
> > ' Range("A65356").Select
> >
> > ' Selection.End(xlUp).Select
> > N = ActiveCell.Row
> > If N >= 2 Then
> >
> > Rows("1:" & 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.Save
> > ask2.Activate
> > End If
> > Next d
> > ask2.Activate
> > ask2.Close
> >
> > ask.Activate
> > ask.Sheets(1).Activate
> >
> > ActiveWorkbook.Save
> >
> > Next i
> >
> > 'abc:
> > 'Exit Sub
> > Application.DisplayAlerts = True
> > End Sub
> >
> >
> >
> > On Thu, Jan 27, 2011 at 11:23 AM, Squall <squall.l...@gmail.com> wrote:
> > > Hi guys,
> >
> > > Could you please share us the coding especially when helping... it
> really
> > > help to improve those being helped (especially me) to understand the
> vba
> > > macro/coding. Sometimes when I try to open the module/code, it's
> protected
> > > by password :(
> >
> > > Nonetheless, thanks for the help.
> >
> > > On 1/26/2011 10:33 PM, ashish koul wrote:
> >
> > > try this macro see if it helps
> >
> > > On Wed, Jan 26, 2011 at 4:22 PM, Seba <sebastjan.hri...@gmail.com>
> wrote:
> >
> > >> Hello,
> >
> > >> how can I upload the file, as I see in the notification, this is no
> > >> longer possible.
> >
> > >> However, if you can imagine my situation:
> >
> > >> I have data in columns from A to O and in rows from 1 to 100. The data
> > >> is in the same rows and columns in all workbooks.
> > >> Now I need the data to be copied from all this workbooks (and all new
> > >> ones I create) to a new workbook serving as a database.
> >
> > >> However all columns allways contain some data, whereas the rows may
> > >> not. It could be the case that only the 1st row contains any data.
> >
> > >> I hope this helps a bit.
> >
> > >> Thank you for the help.
> >
> > >> Best regards,
> >
> > >> seba
> >
> > >> On 17 jan., 18:38, ashish koul <koul.ash...@gmail.com> wrote:
> > >> > send us the sample workbook
> >
> > >> > On Mon, Jan 17, 2011 at 12:10 AM, Seba <sebastjan.hri...@gmail.com>
> > >> wrote:
> > >> > > Hello all,
> >
> > >> > > I have a question regarding building a database. I have a workbook
> for
> > >> > > each of my projects. I would like to have a macro, which would
> > >> extract/
> > >> > > copy certain data (always in the same rows and columns) to a new
> > >> > > workbook, which would serve as a database.
> >
> > >> > > Could anyone please help?
> >
> > >> > > Thank you in advance.
> >
> > >> > > Best regards,
> >
> > >> > > seba
> >
> > >> > > --
> >
> > >>
> ----------------------------------------------------------------------------------
> > >> > > 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 athttp://www.excel-macros.blogspot.com
> > >> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > >> > > 5. Excel Tips and Tricks athttp://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
> >
> > >> > --
> > >> > *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 athttp://www.excel-macros.blogspot.com
> > >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > >> 5. Excel Tips and Tricks athttp://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
> >
> > > --
> > > *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 athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://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 athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://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
> >
> > --
> > *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.
> >
> > NEW_merge_from_different_workbooks_for_all_sheets.xlsm
> > 27 1KPrikažiPrenesi
>
> --
>
> ----------------------------------------------------------------------------------
> 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
>
--
*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/discussexcel
