Looking to update one of our logs to auto increment the next number and link it

[cubangt]

So we have a Log that we track some task and project information. Simple workbook, 2 sheets
Sheet1 = Highlevel information about the task or project..
Sheet2 = Specifics and details if any

So currently we have column A that has a task # (simply 1 thru xx ) but currently, whomever enters a row for their task, has to enter that, not a huge deal, but looking to improve the overall process a little.
we currently have little over 120+ rows already and just looking to automatically increment that number when someone starts to fill in the row.

Secondly, sheet2 needs to contain a reference to each row(linked by the number on sheet1) for any additional or more specific details and comments..

Steps that currently happen:
#1 I open the file and scroll down to the bottom of the list, and add my information starting in column B and over ( i then manually add the new row number )
#2 I then copy the values in column A and column B into sheet2
#3 I create a link from sheet 1 to sheet 2 using the number as the reference.

Id like to automate all or most of that, so that once i enter details on sheet1 column B, column A gets the next available number, and copies the value to sheet 2 and creates the link so that the user filling this in, doesnt have to think about or remember to create that link and copy the data over into the 2nd sheet.

I tried this within the ThisWorkbook object but doesnt do anything

Please Login or Register  to view this content.

I even moved it into the Sheet1 object and locks up excel when i make any change within the worksheet

I get a Compile error
Procedure declaration does not match description of event or procedure having the same name

[cubangt]

Well i moved it to the Sheet1 object and it worked, but it literally filled the entire column with numbers and then threw up an error and closed excel.


This is what i have now, but fills the entire column and then errors out with the error below:

Please Login or Register  to view this content.

Run-time error 1004
Method Range of Object _Worksheet failed

I only need to add the next row number as new rows are added, not fill the entire column.

[cubangt]

I have read a few examples on triggering based on 1 cell change or a range of cells. but this would need to be fully dynamic, since the rows will new rows, need to execute on change of the row below the last row..
Attachment 727624


So in this example, if i start typing into any cell on the next row under 2, the event should be triggered and the #3 should appear next to the new data
Then once cell B is populated, copy that data and the new number and add it into sheet2 with a hyperlink between sheet1 and sheet2

[cubangt]

Made a few tweaks and this seems to be working, still testing, but may be what is needed for the incrementing

Please Login or Register  to view this content.

[cubangt]

The only catch on the linking is need to make sure that if an update is made to an already existing row in both sheets, that a new row and link is not added.
A new row and link should only be created if one doesnt already exist. If i make a change to say row 28, the only thing that should be updated in sheet2 is the value of B28

[cubangt]

Ok, got things working, but not completely working as expected..
This code works, when i add anything in column B on sheet1 it increments and adds the number and automatically links it and copies over to sheet2.

The issue is, initial entry, the data copied to sheet2 is data only(perfect as expected) if i change the value in column b since the link has already been created, it then copies the link over to sheet2 and just points to itself, so not a show stopper, but there is no need for a link period, if we manually paste as value only it works, but how can my copy line be updated to do that?

Please Login or Register  to view this content.

This is what is recorded when using the paste value only:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

How can i replace this line with that logic?
' COPY NEW DATA OVER TO STATUS TAB
Sheets("Sheet1").Range("A" & Target.Row & ":B" & Target.Row).Copy (Sheets("Sheet2").Range("A" & Target.Row))