Hi,

I've got an overflow error that I don't know how to deal with. High
level: I'm clearing all contents and formats from a sheet, and then
copying/pasting all (updated) contents and formats from another
workbook/sheet (first section of code below) The Target worksheet
(the one being cleared and pasted into) has Worksheet_Change code that
I've just added (second bit of code below). When the cells on the
Target sheet are cleared, it triggers the Worksheet_Change event, and
I get the overflow error. Any thoughts as to why, and more
importantly, how to deal with this? Thanks!

With wksSource
wksTarget.Cells.ClearContents
wksTarget.Cells.Interior.Pattern = xlNone
.Cells.Copy wksTarget.Cells(1)
End With 'wksSource


Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub

[more code here, but it dies at the first line]

Frank

Try...

With wksTarget
.UsedRange.Clear
wksSource.UsedRange.Copy .Cells(1)
End With 'wksSource

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Oops.., forgot most important part...

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Hi Garry,

Thanks for this! I plugged it into the Worksheet_Change module, and
I'm getting a Run Timer Error 424 'Object required' error message at
the wksTarget.UsedRange.Clear line. Should this code go somewhere
else?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application


On Sun, 05 Jun 2016 11:13:03 -0400, GS wrote:

Oops.., forgot most important part...

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

Hi Garry,

Thanks for this! I plugged it into the Worksheet_Change module, and
I'm getting a Run Timer Error 424 'Object required' error message at
the wksTarget.UsedRange.Clear line. Should this code go somewhere
else?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

This is based on your original code where I assume you have 'Set' a ref
to wksSource and wksTarget in a standard module procedure. That's where
this code goes, NOT in your Worksheet_Change event! The point of this
is so that sheet event code doesn't fire while you're updating the
contents of the worksheet. Since you didn't post ALL the code, it's
difficult to determine what it's doing. Obviously, you are running a
procedure that clears any content/format in the target sheet before
populating it with content/format of the source sheet. What the event
code does is not clear, nor is why it exists.

In a standard module...

Sub TransferData()
Dim wksSource As Worksheet, wksTarget As Worksheet
Set wksSource = Workbooks("Filename").Sheets("Sheetname")
Set wksTarget = ThisWorkbook.Sheets("Sheetname")

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

On Mon, 06 Jun 2016 00:19:12 -0400, GS wrote:

Hi Garry,

Thanks for this! I plugged it into the Worksheet_Change module, and
I'm getting a Run Timer Error 424 'Object required' error message at
the wksTarget.UsedRange.Clear line. Should this code go somewhere
else?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCol As Long
Dim iRow As Long
Dim myNotes As String

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application

This is based on your original code where I assume you have 'Set' a ref
to wksSource and wksTarget in a standard module procedure. That's where
this code goes, NOT in your Worksheet_Change event! The point of this
is so that sheet event code doesn't fire while you're updating the
contents of the worksheet. Since you didn't post ALL the code, it's
difficult to determine what it's doing. Obviously, you are running a
procedure that clears any content/format in the target sheet before
populating it with content/format of the source sheet. What the event
code does is not clear, nor is why it exists.

In a standard module...

Sub TransferData()
Dim wksSource As Worksheet, wksTarget As Worksheet
Set wksSource = Workbooks("Filename").Sheets("Sheetname")
Set wksTarget = ThisWorkbook.Sheets("Sheetname")

With Application
.EnableEvents = False: ScreenUpdating = False
wksTarget.UsedRange.Clear
wksSource.UsedRange.Copy wksTarget.Cells(1)
.EnableEvents = True: ScreenUpdating = True
End With 'Application
End Sub


Hi Garry. Thanks for the explanation. This worked beautifully. And I
now understand what you are saying, and I see this is a replacement fo
rmy other copy cells code. I've also got a few lines of code that run
at the beginning and end of my larger modules that turn off screen
updating and calculations, and when I saw the EnableEvents and
ScreenUpdating line, I just thought this needed to go there. I'm good
to go for this piece of my puzzle. Thank you!!