I wasn't sure if all worksheets involved are in the same workbook or not, the
code below assumes that they are. First routine will copy same range from
all sheets except the master/destination sheet into that master/destination
sheet. The second routine lets you specify a list of sheet names in the
workbook to copy from if you don't want to copy same range from each and
every other sheet in the book.

Sub CopySameRange()
'name of sheet to copy to, change as needed
Const dSheet = "Sheet1"
Dim rngToCopy As Range
Dim copyToRow As Long
Dim anyWS As Worksheet

copyToRow = 4 ' initialize
For Each anyWS In Worksheets
If anyWS.Name < dSheet Then
'some other sheet, do the copy
'change range address to what you need
Set rngToCopy = anyWS.Range("A5:D10")
rngToCopy.Copy
' I don't think you want formulas, so won't use xlPasteAll
Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial _
xlPasteValues
Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial _
xlPasteFormats
copyToRow = copyToRow + rngToCopy.Rows.Count
End If
Next
Application.CutCopyMode = False
Worksheets(dSheet).Activate
Set rngToCopy = Nothing

End Sub

Sub CopySameRange2()
'name of sheet to copy to, change as needed
Const dSheet = "Sheet1"
Dim rngToCopy As Range
Dim copyToRow As Long
Dim anyWS As Worksheet

copyToRow = 4 ' initialize
'this allows selective copying from just
'specific sheets within the workbook, not All others
For Each anyWS In Worksheets
Select Case anyWS.Name
Case "Sheet2", "Sheet4", "sheet 15", _
"sheet 29"
Set rngToCopy = anyWS.Range("A5:D10")
rngToCopy.Copy
' I don't think you want formulas, so won't use xlPasteAll
Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial _
xlPasteValues
Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial _
xlPasteFormats
copyToRow = copyToRow + rngToCopy.Rows.Count
Case Else
'do nothing
End Select
Next
Application.CutCopyMode = False
Worksheets(dSheet).Activate
Set rngToCopy = Nothing

End Sub


"Dauntless1" wrote:


Hello. I am trying to copy a range of cells from multiple worksheets to
an existing worksheet in my workbook. The range is always the same.
When the range is pasted into the single workbook it should keep the
same values and formats and move to the next available row, starting
with the 4th row. Can anyone please help me with the coding for a
macro?




--
Dauntless1