1

I'm pretty new to VBA and am trying to see if I can create a code for a certain process. I have a spreadsheet with a few unique identifiers and company names in the first row (Company A, Company B, etc). In the following columns, there are a few other columns such as location, key contact, etc, that correspond to each company. Lastly, there is a column for "comments". These comments get updated fairly regularly.

What I'm trying to do is create a macro that will allow me to find the comment for the unique company, copy (or cut) it and paste it in a "historical comments" sheet in the same workbook, so that I can maintain a record of the past comments. Is there a way to create a macro to do this? I've created something that, if i put in the exact Cell Name , it will copy that comment and paste it but I wanted to see if I could designate one cell where I could type in the Company Name, and the macro would look at what is in that cell and then copy the corresponding comment, paste it in the back sheet, and then clear the cell so that I can input a new comment. I have no idea if this is even remotely possible, but any help would be greatly appreciated! 

 Sub Range_copy()
 Dim cellwant As Variant
 Dim cellhistory As Variant
 Dim LRow As Variant
 Dim Account As Variant


 Worksheets("AAG").Select

 Worksheets("AAG").Range("I3").Select
 cellwant = Selection.Value
 FindString = Sheets("AAG").Range("B5:B65").Value

 cellwant = Selection.Value

 Worksheets("AAG").Range(cellwant).copy
 Worksheets("Sheet2").Activate

 Worksheets("Sheet2").Range("A1").Select
M. Primrose
  • 15
  • 3
  • 2
    I recommend reading through [how to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). This will show you how to set up variables to hold your data, instead of relying on selecting them. – BruceWayne Sep 29 '16 at 16:12
  • Since you are new to VBA I will give you another tip that may save you from hours of debuging: At the begining of your scripts always write `Option Explicit`. This will ensure you will never set values to variables with typos since it will first verify if all of your variables are declared. For more info https://msdn.microsoft.com/en-us/library/y9341s4f.aspx – RCaetano Sep 29 '16 at 16:22

1 Answers1

0

The question's a little vague about what a 'corresponding comment' would be for a company name. However, I think what you're looking for could be done using the Worksheet_Change event, which will trigger automatically whenever a change is made on a given worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const csCommentCol As Integer = 5 'The column that contains the comments
    Const csTarget As String = "Sheet2" 'The worksheet with the record of comments
    Const csTargetCol As String = "A" 'The column on the sheet with the list

    Dim shtTarget As Worksheet
    Dim lngLast As Long
    Dim strOldComment As String
    Dim strNewComment As String

    Application.EnableEvents = False 'Prevent this procedure from triggering repeatedly

    If Target.Column = csCommentCol Then 'Check if it's the comment column that's being changed
        Set shtTarget = Sheets(csTarget) 'Define our target sheet. Only here for clarity later
        lngLast = shtTarget.Range(csTargetCol & Rows.Count).End(xlUp).Row + 1 'Find the first empty row
        strNewComment = Target.Value    'Copy the newly entered comment into a variable for safekeeping
        Application.Undo    'Undo the change to return to the old value
        strOldComment = Target.Value    'Copy the old value into a string for future use
        Target.Value = strNewComment    'Restore the new comment
        shtTarget.Range(csTargetCol & lngLast).Value = Target.Value 'Copy the value over
        Target.Select
    End If

    Application.EnableEvents = True
End Sub

Put this code in the Sheet object (not a module) of the worksheet that will contain the comments. Replace the constants at the top as needed. It will automatically run every time a change is made; it checks to see if the change is being made in our specified column (that can be a specified cell if you wish); if the change is there, it finds the first empty cell in our record sheet and duplicates the value in the cell over there; then it clears the target cell for a new entry and reselects it.

Werrf
  • 1,138
  • 6
  • 14
  • thank you so much! I think this is putting me in the right direction. What I meant by corresponding comment is that the record sheet has the same list of companies that exists in the first Sheet, so I want it to copy the comment next to the corresponding company in the back sheet. The only thing about the code you sent was that I want to be able to copy the comment from the first sheet into the record sheet BEFORE I change it, if that makes sense. I don't want to copy the new comment because that wouldn't need to be in the "record sheet" yet, if that makes sense. Thank you again! – M. Primrose Sep 29 '16 at 17:32
  • Unfortunately there isn't a very elegant way to get the value of the comment from before the change was made - there's no Before Change event that can run code. It's the actual change that triggers this macro. There's no elegant way...but there is a way. I've updated my answer to reflect this option. – Werrf Sep 29 '16 at 17:46
  • Thank you!! This is great. Last question, is there a way for it to paste the comment next to the corresponding company in that back sheet, instead of the next empty row? Each sheet has the same amount of rows, I just want to hopefully paste it in the right place as opposed to the next open row. I'm not sure if this is at all possible in VBA but I figured I would ask. Thank you ! – M. Primrose Sep 30 '16 at 14:43