Hi,
it works and it is ok to copy to sheet 1. However I am not able to
define from which sheet to copy from. Could you please indicate in the
code where am I suppose to insert the name of the sheet to copy from.
I didn't see any yellow markings in the code above.
I decided to combine this macro with removing duplicates and I should
have database setup in no time:) thanks to you.
regards
seba
On 27 jan., 11:40, ashish koul <koul.ash...@gmail.com> wrote:
> 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
>
> ...
>
> preberite več »
--
----------------------------------------------------------------------------------
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
