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

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

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

Visit Your Group