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?

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

Thank you for the assistance. I tried the code for the 2nd routine since I had a select group of worksheets from which I wanted to copy. However, I did encounter an issue at the point with the line with the PasteSpecial. I received an error message for that line stating: Application-defined or Object-defined error.
Can you help me please?

Make sure you copied it accurately and that what is in your workbook looks
exactly like what I posted. You might even copy your code and paste here so
we can see what might have gotten messed up.

The lines that have .PasteSpecial near the end of them are actually
continued on the next line in the code. The " _" (space followed by
underscore) at the very end tells Excel that the statement is continued on
the next line.

When you get the error, what is on the line following the indicated error
line? xlPasteValues or xlPasteFormats?

Double check the spelling of your worksheet names in the Case statement, and
of course the address used in the Set rngToCopy statement.


"Dauntless1" wrote:


Thank you for the assistance. I tried the code for the 2nd routine
since I had a select group of worksheets from which I wanted to copy.
However, I did encounter an issue at the point with the line with the
PasteSpecial. I received an error message for that line stating:
Application-defined or Object-defined error.
Can you help me please?


JLatham;540525 Wrote:
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
-




--
Dauntless1

I went back and looked at the code. I think I made some minor errors. I corrected them and tweaked the code a bit...I also had some formatting issues on the end worksheet that I had to fix, so now it runs extremely well!

Thank you for your time and assistance! It is greatly appreciated.

Very glad to hear that. And that things are working as you need now. Thanks
for letting me know that it works properly now.

"Dauntless1" wrote:


I went back and looked at the code. I think I made some minor errors.
I corrected them and tweaked the code a bit...I also had some
formatting issues on the end worksheet that I had to fix, so now it
runs extremely well!

Thank you for your time and assistance! It is greatly appreciated.


JLatham;541002 Wrote:
Make sure you copied it accurately and that what is in your workbook
looks
exactly like what I posted. You might even copy your code and paste
here so
we can see what might have gotten messed up.

The lines that have .PasteSpecial near the end of them are actually
continued on the next line in the code. The " _" (space followed by
underscore) at the very end tells Excel that the statement is continued
on
the next line.

When you get the error, what is on the line following the indicated
error
line? xlPasteValues or xlPasteFormats?

Double check the spelling of your worksheet names in the Case
statement, and
of course the address used in the Set rngToCopy statement.


"Dauntless1" wrote:
-

Thank you for the assistance. I tried the code for the 2nd routine
since I had a select group of worksheets from which I wanted to copy.

However, I did encounter an issue at the point with the line with
the
PasteSpecial. I received an error message for that line stating:
Application-defined or Object-defined error.
Can you help me please?


JLatham;540525 Wrote: -
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
---




--
Dauntless1
-




--
Dauntless1