0

I want to create a macro which works in each sheet of my excel file. The nth time that macro is used in the mth sheet the macro write something in the nth cell of the mth sheet.

If I only had a sheet, I would use a global variable in my code

Dim cellToBeChanged As String


Sub macro()
    If cellToBeChanged = "" Then  
        cellToBeChanged = "A1"
else
     cellToBeChanged =Range(cellToBeChanged).Offset(1, 0).Address

end if
    ‘write what needs to be written in cellToBeChanged
End Sub

But if I have more than one sheet, this code doesn’t work.

For instance if I used this macro the first time in tab 1, and then in tab 2, and then in tab 3. There is 2 cells in tab1 in a1 and a3 and 1 cell in tab2 in a2 instead of 2 cell in a1, a2 in tab1 and a cell in a1 in tab2

I could a different variable for each sheet. But I will probably create a lot of sheet.

In addition of that what I call the nth row will perhaps not be nthLetterOfTHeAlphabet. I am looking for something more general to store that in the nth Sheet, the Macro has been used mth time

How should I write my macro to make her behave as I would like?

Pierre-olivier Gendraud
  • 1,545
  • 1
  • 8
  • 33
  • Are you just trying to edit the last row? – BigBen Jan 30 '20 at 18:17
  • @BigBen. What I want : If I used the macro 3 times in the first sheet et 1 in sheet 2. I want that a1, a2, a3 in sheet1 and a1 in sheet2 to be modified. To do that i have created a variable to store the last cell that has been modified. But there is several sheets. I must create seveal global variables. – Pierre-olivier Gendraud Jan 30 '20 at 18:20
  • Just [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) each time. Don't use global variables. – BigBen Jan 30 '20 at 18:23
  • @BigBen For the specific situation in my example, it will effectively work, but what i call the nth row will perhaps not be nthLetterOfTHeAlphabet. I looking for something more general to store that in the nth Sheet, the Macro has been used mth time – Pierre-olivier Gendraud Jan 30 '20 at 18:28
  • FWIW that `Dim cellToBeChanged As String` is module-scoped, not global at all. – Mathieu Guindon Jan 30 '20 at 18:29
  • How could the row value not correspond to the number of times run? Are you putting other data in that range? That is not going to work unless you are putting some specific value in. If you are doing that then search for the last instance of that value and then go 1 down from there. – Warcupine Jan 30 '20 at 18:30
  • Have you considered creating a sheet (ideally hidden) called Globals with a row for each sheet and store your last cell change location on it. It would also persist when you closed the workbook if that were needed. – CDP1802 Jan 30 '20 at 20:55

0 Answers0