Copy and Paste Special in every sheet

[hannous]

Hi All,
I have a 56 workbooks with about 25 sheets each.

In each workbook I want to copy all the data in Column D and paste the values in Column E.
Is there a way I can do this that's not as cumbersome as copying and paste special in each?

 

[JimM]

You could save this macro in your personal workbook and run it for each workbook



Sub Macro1()

For Each ws In ActiveWorkbook.Sheets

ws.Activate

Columns("D:D").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Next ws

End Sub

 

[hannous]

Thanks.
Would there be a way to exempt a sheet? For example, if there's a sheet titled "Smith" and another titled "Frank" that I don't need to conduct the copy/paste in. How could I exempt those? Again, they would be titled the same in each workbook.

Thanks,

 

[JimM]

Probably not the best bit of code but





Sub Macro1()

For Each ws In ActiveWorkbook.Sheets

ws.Activate

If LCase(ws.Name) <> "frank" Then
If LCase(ws.Name) <> "smith" Then


Columns("D:D").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End If
End If


Next ws

End Sub