VBA Macro to Copy various data to another cell

[Little_Clubber]

Hi All, please can you help?
I have a spreadsheet which tracks various new parts (Split by columns) and various activities against those parts (Split by rows), I then have several tabs for the relevant Phases according to our project.
In another tab on the same workbook, I have an Action Register - so if something is incomplete in Phase 3 - part number XYZ, action 16 (Which is simply a Yes or No or N/A answer), we might want to create an Action with details of what is required to allow its completion.

So let's say my active cell is Cell C23
I would like to create a macro that when clicked, it takes the Tab Name (In this case, Phase 3), and the Action item from Column A of the same row (So in this case, Cell A23) - in this case "Pre-launch Process Flow Diagram", and combines them with a '-' so that it looks this "Phase 3 - Pre-launch Process Flow Diagram", activates the Tab called "Meeting Minutes" then pastes that text into Column B of the next available row.

Thanks in advance.

 

[iggydarsa]

Sub MeetingMinuteUpdate()
    Dim r As Long
    Dim lr As Long
    Dim s As String
    
    'Get row of active cell
    r = ActiveCell.Row
    
    'Prepare the verbiage
    s = ActiveSheet.Name & " - " & Range("A" & r).Value
    
    'Find the last row in Meeting Minutes tab
    lr = Worksheets("Meeting Minutes").Range("B" & Rows.Count).End(xlUp).Row + 1
    
    'Paste the verbiage on Meeting Minutes
    Worksheets("Meeting Minutes").Range("B" & lr).Value = s
End Sub

 

[Little_Clubber]

Sub MeetingMinuteUpdate()
    Dim r As Long
    Dim lr As Long
    Dim s As String
   
    'Get row of active cell
    r = ActiveCell.Row
   
    'Prepare the verbiage
    s = ActiveSheet.Name & " - " & Range("A" & r).Value
   
    'Find the last row in Meeting Minutes tab
    lr = Worksheets("Meeting Minutes").Range("B" & Rows.Count).End(xlUp).Row + 1
   
    'Paste the verbiage on Meeting Minutes
    Worksheets("Meeting Minutes").Range("B" & lr).Value = s
End Sub
[QUOTE="iggydarsa, post: 6030845, member: 45787"]
[CODE=vba]
Sub MeetingMinuteUpdate()
    Dim r As Long
    Dim lr As Long
    Dim s As String
   
    'Get row of active cell
    r = ActiveCell.Row
   
    'Prepare the verbiage
    s = ActiveSheet.Name & " - " & Range("A" & r).Value
   
    'Find the last row in Meeting Minutes tab
    lr = Worksheets("Meeting Minutes").Range("B" & Rows.Count).End(xlUp).Row + 1
   
    'Paste the verbiage on Meeting Minutes
    Worksheets("Meeting Minutes").Range("B" & lr).Value = s
End Sub

Thank you

Sub MeetingMinuteUpdate()
    Dim r As Long
    Dim lr As Long
    Dim s As String
   
    'Get row of active cell
    r = ActiveCell.Row
   
    'Prepare the verbiage
    s = ActiveSheet.Name & " - " & Range("A" & r).Value
   
    'Find the last row in Meeting Minutes tab
    lr = Worksheets("Meeting Minutes").Range("B" & Rows.Count).End(xlUp).Row + 1
   
    'Paste the verbiage on Meeting Minutes
    Worksheets("Meeting Minutes").Range("B" & lr).Value = s
End Sub

[/QUOTE]

Thank you very much for your reply, unfortunately it doesn't give me what I am after currently.
I pasted the code into VBA and run the code when I was in cell C23 as mentioned in my above scenario and the macro did go to the Meeting Minutes, found and pasted in the last available cell in Column B, but the resulting data pasted was:
"Phase 3 - 22"
The Phase 3 bit is correct, and the -
22 was supposed to be the contents of Cell A23, which in this case should have said "Pre-Launch Process Flow Diagram"


Thanks in advance of an update.

 

[Little_Clubber]

Sub MeetingMinuteUpdate()
    Dim r As Long
    Dim lr As Long
    Dim s As String
   
    'Get row of active cell
    r = ActiveCell.Row
   
    'Prepare the verbiage
    s = ActiveSheet.Name & " - " & Range("A" & r).Value
   
    'Find the last row in Meeting Minutes tab
    lr = Worksheets("Meeting Minutes").Range("B" & Rows.Count).End(xlUp).Row + 1
   
    'Paste the verbiage on Meeting Minutes
    Worksheets("Meeting Minutes").Range("B" & lr).Value = s
End Sub

Thank you very much for your reply, unfortunately it doesn't give me what I am after currently.
I pasted the code into VBA and run the code when I was in cell C23 as mentioned in my above scenario and the macro did go to the Meeting Minutes, found and pasted in the last available cell in Column B, but the resulting data pasted was:
"Phase 3 - 22"
The Phase 3 bit is correct, and the -
22 was supposed to be the contents of Cell A23, which in this case should have said "Pre-Launch Process Flow Diagram"


Thanks in advance of an update.

 

[Little_Clubber]

Sub MeetingMinuteUpdate() Dim r As Long Dim lr As Long Dim s As String 'Get row of active cell r = ActiveCell.Row 'Prepare the verbiage s = ActiveSheet.Name & " - " & Range("A" & r).Value 'Find the last row in Meeting Minutes tab lr = Worksheets("Meeting Minutes").Range("B" & Rows.Count).End(xlUp).Row + 1 'Paste the verbiage on Meeting Minutes Worksheets("Meeting Minutes").Range("B" & lr).Value = s End Sub

Fixed - Just needed to change "Range("A" & r).Value" to "ActiveSheet.Range("A" & r).Value"
"22" from the error, was the contents of cell A23 on the Meeting Minutes tab.
Thanks very much!

 

[smozgur]

Fixed - Just needed to change "Range("A" & r).Value" to "ActiveSheet.Range("A" & r).Value"

The Range is equal to ActiveSheet.Range since the Range object's default parent is ActiveSheet. Therefore, it shouldn't be the fix since both versions return the exact same value. Perhaps something else changed in your data or how you execute the macro.

The provided code in post #2 completes the task as it is requested in the original question. Therefore, I switched the marked solution accordingly.

 

[Little_Clubber]

The Range is equal to ActiveSheet.Range since the Range object's default parent is ActiveSheet. Therefore, it shouldn't be the fix since both versions return the exact same value. Perhaps something else changed in your data or how you execute the macro.

The provided code in post #2 completes the task as it is requested in the original question. Therefore, I switched the marked solution accordingly.

Keen to understand why this works for you but only worked for me when I added the ActiveSheet bit.

You mentioned how I run the macro.
I held Alt and Pressed F11, then pasted your code into Module 1. To run the macro, when my cursor was in my selected cell, I hold Alt and Press F8. Like I said, it did copy Phase 3 - correctly but the contents of A and the row number from the Meeting Minutes tab.
I also run the same Macro when in Phase 4 but the same happened.

Keen to learn, if you figure it out, please let me know.
Thanks again for the code in the first place!