Hi All,
I want to be able to automatically go through each sheet (including hidden ones) and copy the used ranges and paste special values and formats in each.
Is there a code anyone could provide to do this.
Thanks.
Hi All,
I want to be able to automatically go through each sheet (including hidden ones) and copy the used ranges and paste special values and formats in each.
Is there a code anyone could provide to do this.
Thanks.
I do not recommend using usedrange, but here's
That will work, but here's a crack at not using .usedrange. I just can't get the syntax right on three copy/paste lines, denoting the range. Unless someone corrects my syntax though, use the code above (despite its use of .usedrange).Sub paster() Dim ws As Long Dim lastsheet As Long lastsheet = Worksheets.Count For ws = 1 To lastsheet With Worksheets(ws) .UsedRange.Copy .UsedRange.pastespecial Paste:=xlPasteValues .UsedRange.pastespecial Paste:=xlPasteFormats End With Next ws End Sub
Sub pastespecialer() Dim ws As Long Dim lastsheet As Long Dim lastrow As Long Dim lastcolumn As Long lastsheet = Worksheets.Count For ws = 1 To lastsheet With Worksheets(ws) lastrow = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row lastcolumn = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column .Range("A1:lastrow & lastcolumn").Copy .Range("A1:" & lastrow & lastcolumn).pastespecial paste:=xlPasteValues .Range("A1:" & lastrow & lastcolumn).pastespecial paste:=xlPasteFormats End With Next ws End Sub
Last edited by Jbm444; 07-14-2010 at 06:03 PM.
One easy way to remove all formulas from all sheets:
Dim ws As Worksheet For each ws in Worksheets ws.Cells.Value = ws.Cells.Value Next ws
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks