copying from multiple worksheets
 

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?

copying from multiple worksheets
 

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?

copying from multiple worksheets
 

THAT is B-YOOTY-FULL.

Wow! Exactly what I was hoping for.

Most appreciated.

"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?

copying from multiple worksheets
 

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?

copying from multiple worksheets
 

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?

copying from multiple worksheets
 

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?

copying from multiple worksheets
 

This macro works for creating one consolidated sheet from a number of sheets
within a single workbook. The origin and destination sheets don't have to be
in the same workbook, but all the origin sheets do...so, I don't think this
macro will work for the 20 workbooks you are dealing with. I'm a macro
novice myself, but maybe you can contact Mr. Deitrick about how to modify the
code for your situation.

MJohn

"Krista" wrote:

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?

copying from multiple worksheets
 

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?