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