excel macro help please

Soldato
Joined
14 Dec 2005
Posts
5,046
I have this working, in a fashion

Sub Macro19()
'
' Macro19 Macro
'
Sheets("List").Select
Selection.Offset(1, 0).Select
'
Sheets("Temp").Select
Selection.Copy
Sheets("List").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I want the macro to copy paste from the cells then move to the next available row so that when new data is entered in the cells its copying from it will create a log/list (each time the macro is ran)

just now it moves down 1 row, but is reliant on where the cursor is on the 'list' page, I want it to create a row starting from column A each time (or whatever column the start of the data is set to)
just now if you accidentally move the cursor then it wil just start pasting the cells in a row from that cell
 
Last edited:
I started putting some code together for you but I ran out of time. I'll give you a little pointer though:

Code:
Worksheets("worksheetname").Rows.Count
This finds how many rows there are in total in a worksheet

Code:
Worksheets("worksheetname").Cells(Worksheets("worksheetname").Rows.Count,1)
This is the cell in column A in the last row

Code:
Worksheets("worksheetname").Cells(Worksheets("worksheetname").Rows.Count,1).End(xlUp)
Starting from that bottom cell, this moves up until it finds a cell with data. (Equivalent to pressing Ctrl+Up in Excel)
 
Great thanks. Just new to this (took me hours to get that working last night...30mins to access the developer tab!)
So don't mind trying to figure it out!
 
I'll try and walk you through an example. I want to record changes to the "Data" worksheet in the "Log" worksheet. Here are my two worksheets:

ExcelMacro1.PNG


ExcelMacro2.PNG


First - right click the "Data" worksheet and click "View Code". This will open the VBA window for that particular sheet. Notice the blue cross, this is code specific to this sheet:

ExcelMacro3.PNG


By clicking the dropdown boxes (see the red crosses) I selected "Worksheet" in the first box and then several options are available in the second box. These options trigger code to run depending on what you've selected. In the picture above I selected "Change". If any cells change in the "Data" worksheet then where it says "Your code goes here" will run.

So, you make a change to the "Data" worksheet and the macro is triggered. The "Target" variable (pink underline) tells you which cell or cells have changed. Because more than one cell may have changed we'll need to loop through each of them like this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

[COLOR="Yellow"]    Dim Rng As Range
    
    For Each Rng In Target[/COLOR]
        'Your code goes here
[COLOR="Yellow"]    Next[/COLOR]

End Sub

Next I want to record some information to put into my log:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
[COLOR="Yellow"]    Dim TimeAndDate As String
    Dim CellAddress As String
    Dim CellValue As Variant[/COLOR]
    
    For Each Rng In Target
        
[COLOR="Yellow"]        TimeAndDate = Format(Now, "hh:mm:ss dd/mm/yyyy")
        CellAddress = Rng.Address
        CellValue = Rng.Value[/COLOR]
        
    Next

End Sub

Now we need to start adding things to the log. First I want to find the next available row in the log. I'm using a different method to my previous post, it's a little less efficient but hopefully a little more intuitive. Using "Do" I'm looping through each cell in the first column until I come across a blank cell and then stopping:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim TimeAndDate As String
    Dim CellAddress As String
    Dim CellValue As Variant
 [COLOR="Yellow"]   Dim LogRow As Long[/COLOR]
    
    For Each Rng In Target
        
        TimeAndDate = Format(Now, "hh:mm:ss dd/mm/yyyy")
        CellAddress = Rng.Address
        CellValue = Rng.Value
        
[COLOR="Yellow"]        LogRow = 0
        Do
            LogRow = LogRow + 1
        Loop Until Sheets("Log").Cells(LogRow, 1) = ""[/COLOR]
    Next

End Sub

Now I know which row in the log is free I can start adding my data to it:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim TimeAndDate As String
    Dim CellAddress As String
    Dim CellValue As Variant
    Dim LogRow As Long
    
    For Each Rng In Target
        
        TimeAndDate = Format(Now, "hh:mm:ss dd/mm/yyyy")
        CellAddress = Rng.Address
        CellValue = Rng.Value
        
        LogRow = 0
        Do
            LogRow = LogRow + 1
        Loop Until Sheets("Log").Cells(LogRow, 1) = ""
        
[COLOR="Yellow"]        Sheets("Log").Cells(LogRow, 1).Value = TimeAndDate
        Sheets("Log").Cells(LogRow, 2).Value = CellAddress
        Sheets("Log").Cells(LogRow, 3).Value = CellValue[/COLOR]
        
    Next

End Sub

Notice how I used .value = to set the values. This is much more robust that using copy & paste. Suppose you have a macro running and you copy some text in another program, that could cause the macro to fall over. Similarly, defining specific cells (e.g. Sheets("Log").Cells(3, 2)) is much more robust than using .Select and Selection.

If you click in the margin immediately to the left of the code the line will turn red and the code will pause at that point. (Typing the word Stop in the code will do the same thing). You can then use F8 to step through each line of the code in turn. That will help a lot with understanding what's happening at each step.

I hope that helps. You can download the workbook here
 
Sheets. refers to the active workbook and that can break the macro if you have more than one workbook open and change active workbooks when the macro is running.

You could use thisworkbook.sheets rather than sheets., but i would go one step further and rename the "Log" worksheet object to something like wsLog:

t3Stljz.png


(Ctrl+G if you don't have that pane)

.. then you can use wsLog.cells instead which will always refer to the correct workbook.worksheet regardless of the active workbook and it will work even if the sheets are renamed in Excel.

I would also write the date straight to the cell rather than convert it to a string as its more straight forward and it also allows you to run comparisons later. ie, time between dates etc. Just format the entire column as a time / date instead.

I would also remove the two strings and the variant and write the values directly since it's unlikely you'll do anything else with those

Additionally, I would also use wsLog.Range("A1").End(xlDown).Row rather than the do loop, no real need to loop through cells to find a blank.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim LogRow As Long
   
    For Each Rng In Target
        LogRow = wsLog.Range("A1").End(xlDown).Row +1
        
        wsLog.Cells(LogRow, 1).Value = Now [COLOR="PaleGreen"]' // Write the time and date[/COLOR]
        wsLog.Cells(LogRow, 2).Value = Rng.Address [COLOR="PaleGreen"]' // Write the Cell Address[/COLOR]
        wsLog.Cells(LogRow, 3).Value = Rng.Value [COLOR="palegreen"]' // Write the Cell Value[/COLOR]
    Next

End Sub

Not that you need to do this for a simple routine but you could go one step further and use a single statement to write an array to a range of cells:

Code:
Private Sub Worksheet_Change2(ByVal Target As Range)

    Dim Rng As Range
    Dim LogRow As Long
    
    For Each Rng In Target
        LogRow = wsLog.Range("A1").End(xlDown).Row + 1
        With wsLog
            [COLOR="PaleGreen"]' // wslog.range(start cell, end cell).value = array[/COLOR]
            .[COLOR="Yellow"]Range(.Cells(LogRow, 1), .Cells(LogRow, 3)).Value = Array(Now, Rng.Address, Rng.Value)[/COLOR]
        End With
    Next

End Sub


and if you really want to be fancy then you could define the array first and use ubound([array name]) to find the number of array elements to write:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim LogRow As Long
    [COLOR="Yellow"]Dim arr() As Variant[/COLOR]
        
    For Each Rng In Target
        LogRow = wsLog.Range("A1").End(xlDown).Row + 1
        [COLOR="yellow"]arr = Array(Now, Rng.Address,Rng.Value)[/COLOR]
        With wsLog
            [COLOR="PaleGreen"]' // wslog.range(start cell, end cell).value = array[/COLOR]
            .Range(.Cells(LogRow, 1), .Cells(LogRow, [COLOR="yellow"]UBound(arr) + 1[/COLOR])).Value = arr
        End With
    Next

End Sub

This means you can write additional information by just adding another element to arr = Array(. For example, arr = Array(Now, Rng.Address, Rng.Value, Environ("Username")) would also write the current users name in the next column without you having to change anything else. Useful if you have multiple users accessing the document etc.

Also concider looking at Excel's built in track changes feature: https://support.office.com/en-gb/ar...workbook-951bdf89-9ee6-4777-b31e-33ad0f594d18

This allows you to see what the previous values were and accept/reject changes.
 
Last edited:
[img ]https://i.imgur.com/t3Stljz.png[/img]

.. then you can use wsLog.cells instead which will always refer to the correct workbook.worksheet regardless of the active workbook and it will work even if the sheets are renamed in Excel.

Excellent tip. Didn't know that one!

Additionally, I would also use wsLog.Range("A1").End(xlDown).Row rather than the do loop, no real need to loop through cells to find a blank.
I would suggest using wsLog.Cells(wsLog.Rows.Count,1).End(xlUp).Row. If the log is empty then using xlDown will find the very bottom cell and adding 1 will make the macro fall over when it tries to write to a non-existent row.

Another suggestion would be to define which cells you want the macro to run for e.g. suppose you only want to record in the log changes to the range B2:D5.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim LogRow As Long
    Dim arr() As Variant
    
    [COLOR="Yellow"]If Intersect(Target, Me.Range("B2:D5")) Is Nothing Then Exit Sub[/COLOR]
    
    For Each Rng In [COLOR="Yellow"]Intersect(Target, Me.Range("B2:D5"))[/COLOR]
        LogRow = [COLOR="Yellow"]wsLog.Cells(wsLog.Rows.Count, 1).End(xlUp).Row + 1[/COLOR]
        arr = Array(Now, Rng.Address, Rng.Value)
        With wsLog
            .Range(.Cells(LogRow, 1), .Cells(LogRow, UBound(arr) + 1)).Value = arr
        End With
    Next

End Sub
 
Back
Top Bottom