Thread: change macro code to read cell M20 intead of M21

Hi

Please could someone advice what i need to change to make my code work.

User enters "Date" in cell M19 and "Name" in cell M21.

Below code works fine for cell M19 & M21 but i want to change the cell from M21 to M20 what part of code do i change to make it work.

    With Sheets("Main Menu") 'part of date & name
        LR = .Range("M" & Rows.Count).End(xlUp).Row
        For i = 19 To LR - 2 Step 4
            With .Range("M" & i)
                .Copy Destination:=Sheets("Consumables Used From AM Rack").Range("A65535").End(xlUp).Offset(1, 0)
                .ClearContents
                With .Offset(2)
                    .Copy Destination:=Sheets("Consumables Used From AM Rack").Range("D65535").End(xlUp).Offset(1, 0)
                    .ClearContents
                End With
            End With
        Next i
    End With

Please can someone assist me.

Currently the code read M19 & M21. I have change the design of sheet moveing the M21 to M20.

These cell will stay static as user enter date (m19) and name (m20) cell.

Loop should only do this two cell. Current codeing work but it looks at cell M21 which i want to change it to M20.

So you ONLY want the macro to run on cells M19 and M20?
I ask because that is not what the current macro is written to do...

It is written to find the last row used in column M, and then operate on pairings such as:

M19 and M21
M23 and M25
M27 and M29
...
...
M(lastrow-2) and M(lastrow)
So keep going until the second 'M' row is the last row of the spreadsheet.

To confirm, are you saying you don't want it to do this, you just want it to do M19 for date, and M20 for name, and then stop?

If so this is the modification necessary (removed lines in red, added code in blue):

    With Sheets("Main Menu") 'part of date & name
'        LR = .Range("M" & Rows.Count).End(xlUp).Row
'        For i = 19 To LR - 2 Step 4
'            With .Range("M" & i)
            With .Range("M19")
                .Copy Destination:=Sheets("Consumables Used From AM Rack").Range("A65535").End(xlUp).Offset(1, 0)
                .ClearContents
'                With .Offset(2)
                With .Offset(1) ' Offset 1 row, (ie. from M19 to M20)
                    .Copy Destination:=Sheets("Consumables Used From AM Rack").Range("D65535").End(xlUp).Offset(1, 0)
                    .ClearContents
                End With
            End With
'        Next i
    End With