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

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

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