Copy and Paste Special in every sheet

hannous

New Member
Joined
Jul 10, 2006
Messages
27
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?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
686
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

New Member
Joined
Jul 10, 2006
Messages
27
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

Well-known Member
Joined
Nov 11, 2003
Messages
686
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top