Mark,

apologies I poster this answer to the wrong question. too much NYE alcohol I
think.

Mike

"Mark Campbell" wrote:

Mike,

This isnt really what i want to do as - I have other data on sheet 2 which I
dont want to be affected - and also other parts of my workbook rely on data
from these 2 sheets so changing the names would only confuse matters.

"Mike" wrote:

You could put a name change button on each sheet that calls this macro

Sub sheetnamechange()

ActiveWorkbook.Unprotect
Message = "Enter new sheet name"
newname = InputBox(Message)
If newname = "" Then ActiveWorkbook.Protect: End
ActiveSheet.Name = newname
ActiveWorkbook.Protect


End Sub

Mike

"Mark Campbell" wrote:

I am a beginner to VBA and am looking for some help writing a Macro.

I have approx 900 rows of data in sheet1 of a workbook. Row A contains the
column headings for the data which is contained in columns A - I . Col A
contains a unique reference code for each row of data. Col B a descrition and
the other columns contain dates, times and percentages.

I update the data in sheet 1 weekly by first deleting the old data and
pasting in the updated data which is sourced from a project management
software programme.

I am seeking a macro which will copy the data from sheet 1 into sheet 2 when
I activate a control button. The Macro needs to perform the following:

a) For each row of data in sheet 1 - look up sheet 2 to see if the record
already exists (using the col A unique reference code)

b) If the record does not exist - paste the new record into the next free
row in sheet 2.

c) If the record does exist - update columns B - I in sheet 2 with the new
data from sheet1. (Where the new data contains empty cells - I do not wish to
over write existing data in sheet 2)

The following conditions are also present:
a) Sheet 1 contains a number of blank rows - i can remove these if necessary
to make the macro work.
b) Sheet 2 contains some conditional formating which I dont want to lose.
c) Some of the columns in Sheet 2 into which data is to be copied may be
hidden.
d) Autofilters may also be applied to sheet 2 prior to running the macro.

Apologies for the lenght of this post - I hope I have explained by
requirements.
I have looked at Ron's site on this topic but am still a unsure as to the
code needed.
Thanks in advance for any help offered.
Regards
Mark