MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying same range from multiple sheet to a new sheet

Posted by Andrea on May 10, 2001 9:25 AM

Does anyone know how to Copy the same range from multiple sheet to a new sheet.
I have to copy for example from a133 to b147 from sheet1,sheet2, etc to a new sheet called total.
Can anyone help me?

Posted by Dave Hawley on May 10, 2001 9:47 AM

Hi Andrea

Here is a Macro that will do the job for you.

Sub CopyRanges()
'Written by Ozgrid Business Applications
Dim rCopyCells As Range
Dim wWsht As Worksheet

On Error Resume Next
Set rCopyCells = Application.InputBox _
(Prompt:="Select a range", Type:=8)

If rCopyCells Is Nothing Then Exit Sub

For Each wWsht In ActiveWorkbook.Worksheets
Select Case wWsht.Name
Case "Sheet1", "Sheet2", "Sheet3"
wWsht.Range(rCopyCells.Address).Copy Destination:= _
End Select
Next wWsht
Set rCopyCells = Nothing
End Sub

To use it:

Push Alt+F11 and go to Insert>Module.
Paste in the code.
Change "Sheet1" etc to the names you want.
Push Alt+Q then Push Alt+F8.
Select "CopyRanges" and click "Options"
Assign a shortcut key.

Now, select any sheet and holding down you Ctrl key select the Sheets you want the range Copied from.
Push the Shortcut key and select the range on the sheet you are on.
Click OK

OzGrid Business Applications

Posted by Andrea on May 14, 2001 6:40 AM

Hi Dave,
I notice that using your macro I miss the last row of each range(except the last sheet selected). Can you help me?


To use it: Push Alt+F11 and go to Insert>Module.

Posted by . on May 14, 2001 5:06 PM

Revised line :-
wWsht.Range(rCopyCells.Address).Copy Destination:= _

Posted by Andrea on May 15, 2001 1:12 AM

Hi Dave,
now it's all OK, Thanks. I have a problem with a workbook with 175 Sheets I changed your line:
Case "Sheet1", "Sheet2", "Sheet3"
Case "xxx" To "yyy",
where xxx and yyy are sample name, unfortunately it doesn't work well.
Can you suggest me a way to change the macro in order I can select the range and the sheets to copy in "Total"?

Thank you for your help Andrea