About Us

Ron de Bruin

Hi Krista

See
http://www.rondebruin.nl/copy3.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Krista" wrote in message ...
Any suggestions on how I could consolidate 20 workbooks that all have the
same layout. I need to have 1 master file with all data and have this
updated each week.

"M John" wrote:

The output from this macro is a single sheet containing links to all selected
(and copied) cells from the source/original sheets. As such, the links can
be updated via the "Links..." option at or near the bottom of the "Edit"
menu. So, yes, this macro will allow for updating without having to manually
copy and paste the information each time.

MJohn

"Krista" wrote:

I have 20 workbooks that are used for Forecasting across NA. I need to
consolidate them into one summary. Does this marco update automatically? I
need to pull a summary each week and the field could add more records to
their files each week, will the macro take that into account.

"Bernie Deitrick" wrote:

MJohn,

Select the sheets first, then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub CreateLinkedSummary()
Dim SNames() As String
Dim myAdd As String
Dim myRange As Range
Dim mySS As Worksheet
Dim i As Integer
Dim SCnt As Integer
Dim myCol As Integer

SCnt = ActiveWindow.SelectedSheets.Count

If SCnt = 1 Then
If MsgBox("Are you sure - only one sheet?", vbYesNo) _
= vbYes Then
GoTo ShtOK
Else
MsgBox "Select the sheets and re-run the macro."
Exit Sub
End If
End If

ShtOK:

ReDim SNames(1 To SCnt)

For i = 1 To SCnt
SNames(i) = ActiveWindow.SelectedSheets(i).Name
Next i

Set myRange = Application.InputBox( _
"Select Range to link from", Type:=8)
myAdd = myRange.Address

Set myRange = Application.InputBox( _
"Select sheet and range to link to.", Type:=8)

Set mySS = myRange.Parent
myCol = myRange(1).Column
Worksheets(SNames(1)).Range(myAdd).Copy
mySS.Select
myRange.Select
mySS.Paste Link:=True

For i = 2 To SCnt
Worksheets(SNames(i)).Range(myAdd).Copy
mySS.Cells(mySS.Rows.Count, myCol).End(xlUp)(2).Select
mySS.Paste Link:=True
Next i

myRange.Select
Application.CutCopyMode = False
End Sub

"M John" wrote in message
...
Is there a way to copy data from the same range for all selected worksheets
in a workbook? The preferred order of operations would be: select a range
of cells, select desired worksheets to copy from, select destination
worksheet, and then paste special (links) on this target worksheet. Macro
for this?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying multiple cells out of multiple worksheets at same time.	tom	Excel Discussion (Misc queries)	1	April 2nd 10 09:03 PM
copying multiple worksheets to a new workbook	Shabbir	Excel Discussion (Misc queries)	2	July 13th 06 01:45 AM
Copying a formula to multiple worksheets	RobHan	Excel Worksheet Functions	1	February 16th 06 05:21 PM
Copying multiple Worksheets	Dor474c	Excel Discussion (Misc queries)	0	June 29th 05 10:10 PM
Copying from multiple worksheets	GMP	Excel Discussion (Misc queries)	4	May 6th 05 12:59 AM

Menu

About Us