0

So I am currently writing a piece of code that moves a block of data from one spreadsheet to another. Once it is moved the code allocates a numerical reference for the block.

At the moment the code goes to the bottom of the page and work up until it finds the last written line. It reads that reference and adds 1 to it.

I thought this would solve the issue but it's actually created another.

Now it means that if the last row is removed or posted to another sheet, the code will then create a duplicate of that reference.

For example if I had a sheet with 45 rows, going from 10001 to 10045 in references, and I then moved row 10045 to another sheet. The next time I run the code I will end up with a second 10045.

Is there any way I can make this code into a variable that stays at the same amount, regardless of which numbers have been removed?

See the code I've been using below. 

BlankRow = Range("B1000").End(xlUp).Row + 1
Cells(BlankRow, 2).Select
ActiveCell.Value = Selection.Offset(-1, 0) + 1

EDIT: To make it clearer. The movement of data is to another spreadsheet in the same workbook. I am not transferring to another workbook completely

The Information is pulled from another spreadsheet which is a basic mock up of a form. Press a button and it's formatted here.

This is where the references come in (B). Currently it adds one each time. However, one a job is complete the entire row of data is moved through case to another sheet - think of it as a glorified to do list. enter image description here

However, if the bottom row's job (10058) moves to another sheet we get a problem. When the new set of data is being put in the code provided above will see what the last row is numbered and add one. (This would see 10057 and add one, thus making 10058 again). How do I avoid this?

Breakingfaith
  • 63
  • 7
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 07 '19 at 10:08

2 Answers2

2

If you need this value to be persistant (after closing and re-opening the file) you need to save and increase the value anywhere in a cell (variable values will reset to 0 after closing a file).

Best practice would be to use a hidden sheet to save that value.

Eg add a sheet called "Config", hide it and use the following code:

'increase value in config
ThisWorkbook.Worksheets("Config").Range("A1").Value = ThisWorkbook.Worksheets("Config").Range("A1").Value + 1
'use the config value in your sheet
With ThisWorkbook.Worksheets("YourActualSheet")
    .Cells(.Rows.Count, 2).End(xlUp).Offset(1) = ThisWorkbook.Worksheets("Config").Range("A1").Value
End With

So the last used value is always in Worksheets("Config").Range("A1") no matter if you deleted or moved the value from your actual sheet it will increase the value saved in Worksheets("Config").Range("A1") next time you run the code.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
0

Your code can be resumed on this:

With ThisWorkbook.Sheets("NameOfTheSheet")
    .Cells(.Rows.Count, 2).End(xlUp).Offset(1) = .Cells(.Rows.Count, 2).End(xlUp) + 1
End With

Also you should search on how to reference workbooks/worksheets and use the references to avoid problems and the use of .Select .Activate

Damian
  • 5,152
  • 1
  • 10
  • 21
  • I appreciate not using select and offset - this was a piece I wrote before I started the language and I will be going back to update these once I get all functions working. Unfortunately in regards to references I have a select few to use due to the client. – Breakingfaith May 07 '19 at 10:11
  • 1
    there is nothing wrong using `Offset` but there are 0 to few cases where you would need to use `Select`. In this case you want to fill the last row on column B with the value from the previous row + 1, and so does this code without selecting anything. – Damian May 07 '19 at 10:12
  • Thank you! That's awfully helpful :) – Breakingfaith May 07 '19 at 10:14
  • But I still don't quite understand your post. Where is the row deleted/moved from? Which reference is going to get duplicated? Can you show us some examples to see what we are dealing with? – Damian May 07 '19 at 10:14
  • Sure thing, I will add some now – Breakingfaith May 07 '19 at 10:16
  • @MimiBeard then what PeH stated, you should keep the count into a hidden cell/worksheet. – Damian May 07 '19 at 10:39