Senin, 01 Desember 2014

Re: [ExcelVBA] macro to aggregate worksheets from many workbooks

 

I think it would be as follows but I have not tested it so copy the whole directory before trying it.

You will need to open a new workbook and paste this into a module, make a few changes to the parameters and then run it.

Option Explicit

Sub fred()
Dim wbk As Workbook
Dim wsht As Worksheet
Dim tr As Long
Dim sr As Long
Dim ts As Worksheet

Dim startrow As Long
Dim endrow As Long
Dim dirpath As String
Dim endcolumn As String

Dim File As String

startrow = 1 'set these for the start and end of the source range
endrow = 500
endcolumn = "K"

dirpath = "C:\data\sourcefiles\" ' set this for your directory

tr = 1 ' where the list starts in the target wbk
Set ts = ThisWorkbook.Sheets(1)

For Each File In fso.directory(dirpath)
File = Dir(dirpath & "*")
    While (File <> "")
        Workbooks.Open File
        Set wbk = ActiveWorkbook
      For Each wsht In wbk(Worksheets)
        For sr = startrow To endrow
            ts.Cells(tr, 1) = wbk.Name
            ts.Cells(tr, 2) = wsht.Name
           wsht.Range("A" & sr, encolumn & sr).Copy ts.Name & "C" & tr
           tr = tr + 1
         Next sr
       next wsht
     File = Dir
    Wend

End Sub

Regards
David Grugeon

Regards
David Grugeon


On 1 December 2014 at 18:19, Dr John C Bullas john.bullas@gmail.com [ExcelVBA] <ExcelVBA@yahoogroups.com> wrote:
Anyone point me towards a macro to drag all the worksheets from a
number of workbooks in a directory sequentially into a single
worksheet with the source filename and source sheet name in the first
two columns

the data from the sheet would then be inserted line by line with the
same prefix column

the range of data to be grabbed can be fixed in terms of a range

I am trying to aggregate a large number of sheets within a large
number of workbooks where the originators have kindly produced
sequential blocks of data split between worksheets using what should
have been column data as the tab name!!!

Argh!

Many thanks

Dr B

--



===========================
http://uk.linkedin.com/in/drjohnbullas


--


------------------------------------
Posted by: Dr John C Bullas <john.bullas@gmail.com>
------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/ExcelVBA/join
    (Yahoo! ID required)

<*> To change settings via email:
    ExcelVBA-digest@yahoogroups.com
    ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/us/yahoo/utos/terms/



__._,_.___

Posted by: David Grugeon <yahoo@grugeon.com.au>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

Yahoo Groups
Test Campaign
subject line for test

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

.

__,_._,___

Tidak ada komentar:

Posting Komentar