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!!