VBA Macro to Copy various data to another cell

Little_Clubber

New Member
Joined
Nov 10, 2017
Messages
32
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
VBA Code:
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
 
Upvote 0
Solution
VBA Code:
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
VBA Code:
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.
 
Upvote 0
VBA Code:
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top