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