Efficient way to copy a range in numerous sheets within a workbook
 

Hello,

I am looking for info on creating a macro to take a range say C4:G56 in
(sheet 1) and copy that range to D4:H56 on the same sheet. I do this this
using paste special values because column C has formulas and I'm just
shifting data for the next month. The question I have is that I have many
sheets in a workbook that I do this process for and currently have a macro
that references each sheet and does a copy range and paste special. I would
like to make this long macro more efficient and also more manageable. I tried
grouping the sheets but had a problem with the paste special function. Also
not all sheets in the workbook need to be updated so grouping all of them
doens't work. Here is some of the code that I have come up with to this point
with no success. I'm hoping that all the variables I put in will help
identify what I'm trying to do. Any help will be greatly appreciated.

Dim wks As Worksheet
Dim DontCopy1 As Worksheet
Dim DontCopy2 As Worksheet
Dim DontCopy3 As Worksheet
Dim DontCopy4 As Worksheet
Dim CopyRange As Range
Dim DestCell As Range

Set CopyRange = Range("C4:G56")
Set DestCell = Range("D4")
Set DontCopy1 = Sheets("Instructions")
Set DontCopy2 = Sheets("Summary")
Set DontCopy3 = Sheets("Balances")
Set DontCopy4 = Sheets("DataSheet")

Sheets("Sheet1").Select
For Each wks In Worksheets
If wks.Name = DontCopy1.Name Then
If wks.Name = DontCopy2.Name Then
If wks.Name = DontCopy3.Name Then
If wks.Name = DontCopy4.Name Then
'do nothing
Else
ActiveSheet.Select
Range("C4:G56").Select
Selection.Copy
Range("D4").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1

End If
End If
End If
End If

Next wks

End Sub

Thank you in advance

Steve

Efficient way to copy a range in numerous sheets within a workbook
 

How about this:

Sub test
Dim wks As Worksheet
Dim DontCopy1 As Worksheet
Dim DontCopy2 As Worksheet
Dim DontCopy3 As Worksheet
Dim DontCopy4 As Worksheet
Dim CopyRange As Range
Dim DestCell As Range

Set CopyRange = Range("C4:G56")
Set DestCell = Range("D4")
Set DontCopy1 = Sheets("Instructions")
Set DontCopy2 = Sheets("Summary")
Set DontCopy3 = Sheets("Balances")
Set DontCopy4 = Sheets("DataSheet")

Sheets("Sheet1").Select
For Each wks In Worksheets
If wks.Name = DontCopy1.Name Or _
wks.Name = DontCopy2.Name Or _
wks.Name = DontCopy3.Name Or _
wks.Name = DontCopy4.Name Then

Else
wks.Select
Range("C4:G56").Select
Selection.Copy
Range("D4").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1

End If

Next wks

End Sub

after the "or it is <space<underscore

Wolf



"Steve O" wrote:

Hello,

I am looking for info on creating a macro to take a range say C4:G56 in
(sheet 1) and copy that range to D4:H56 on the same sheet. I do this this
using paste special values because column C has formulas and I'm just
shifting data for the next month. The question I have is that I have many
sheets in a workbook that I do this process for and currently have a macro
that references each sheet and does a copy range and paste special. I would
like to make this long macro more efficient and also more manageable. I tried
grouping the sheets but had a problem with the paste special function. Also
not all sheets in the workbook need to be updated so grouping all of them
doens't work. Here is some of the code that I have come up with to this point
with no success. I'm hoping that all the variables I put in will help
identify what I'm trying to do. Any help will be greatly appreciated.

Dim wks As Worksheet
Dim DontCopy1 As Worksheet
Dim DontCopy2 As Worksheet
Dim DontCopy3 As Worksheet
Dim DontCopy4 As Worksheet
Dim CopyRange As Range
Dim DestCell As Range

Set CopyRange = Range("C4:G56")
Set DestCell = Range("D4")
Set DontCopy1 = Sheets("Instructions")
Set DontCopy2 = Sheets("Summary")
Set DontCopy3 = Sheets("Balances")
Set DontCopy4 = Sheets("DataSheet")

Sheets("Sheet1").Select
For Each wks In Worksheets
If wks.Name = DontCopy1.Name Then
If wks.Name = DontCopy2.Name Then
If wks.Name = DontCopy3.Name Then
If wks.Name = DontCopy4.Name Then
'do nothing
Else
ActiveSheet.Select
Range("C4:G56").Select
Selection.Copy
Range("D4").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1

End If
End If
End If
End If

Next wks

End Sub

Thank you in advance

Steve

Efficient way to copy a range in numerous sheets within a work
 

Thanks for your help!!!! It worked perfectly, I appreciate it.


"Wolf" wrote:

How about this:

Sub test
Dim wks As Worksheet
Dim DontCopy1 As Worksheet
Dim DontCopy2 As Worksheet
Dim DontCopy3 As Worksheet
Dim DontCopy4 As Worksheet
Dim CopyRange As Range
Dim DestCell As Range

Set CopyRange = Range("C4:G56")
Set DestCell = Range("D4")
Set DontCopy1 = Sheets("Instructions")
Set DontCopy2 = Sheets("Summary")
Set DontCopy3 = Sheets("Balances")
Set DontCopy4 = Sheets("DataSheet")

Sheets("Sheet1").Select
For Each wks In Worksheets
If wks.Name = DontCopy1.Name Or _
wks.Name = DontCopy2.Name Or _
wks.Name = DontCopy3.Name Or _
wks.Name = DontCopy4.Name Then

Else
wks.Select
Range("C4:G56").Select
Selection.Copy
Range("D4").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1

End If

Next wks

End Sub

after the "or it is <space<underscore

Wolf