hi Joe,

I'm not sure exactly what you're asking so I've made some changes
(removing the sheet deletion code, adjusting With statements etc)...

I am guessing that you want to prevent the potential need for changing
the copyrng in the VBE screen, which is currently coded as
Code:
--------------------
Set CopyRng = sh.Range("L11:V93")
--------------------

How is this range defined...?

- Does it always start in row 11?
- Does it always start in column L?

What defines the endpoint... (last Row & last column, or first blank
row after row 11)?
Could a user select the range easily (once, at the start of the code)?
If so, try:



Code:
--------------------
Option Explicit
Sub ModifiedVersion()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range
Dim CopyRngAddrs As String

CopyRngAddrs = Application.InputBox(prompt:="Please select copy range", Title:="MACRO REQUEST", Type:=8).Address

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'rely on Exception sheet already existing
Set DestSh = ActiveWorkbook.Worksheets("Exceptions")
DestSh.UsedRange.ClearContents

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name Then
'Find the last row with data on the DestSh
Last = LastRow(DestSh)
'Fill in the range that you want to copy
'### Set CopyRng = sh.Range("L11:V93")
'### line added
Set CopyRng = sh.Range(CopyRngAddrs)
With CopyRng
'Test if there enough rows in the DestSh to copy all the data
If Last + .Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If
'This copies all values/formats
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With
End If
Next

ExitTheSub:
'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit
With Application
.GoTo DestSh.Cells(1)
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

--------------------


hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101770