Concatenate text from two sheets

annyn

New Member
Joined
Apr 24, 2015
Messages
4
Going round in circles here but I feel I am over thinking this or the right syntax just eludes me.

I have a Master spreadsheet with a Notes column and a another sheet with today's date and specific phrases that I choose. Instead of always manually copying and pasting these phrases or using concatenate as a worksheet formula so I can update the Notes in the Master, I just want a macro to run. I have a cell selected in the phrases sheet and a selected in the Master sheet and want to add the text from Phrases into the Notes cell in Master but results are not correct. Nrange picks up the text in oPhrases that's in the same ROW as the active cell in Master instead of the row with the selection box around it. Bascially, just concatenating except the row in both sheets will be variable. Thanks in advance for your help.

VBA Code:
Dim as Worksheet
Set ms = Worksheets("Master")
Dim ph as worksheet
Set ph = Worksheets("oPhrases")

Dim Mrange as Range
Dim Nrange as Range

Set Nrange = ph.Range("c" & selection.row)  ' supposed to be the cell with the right phrase for this file. 
Set Mrange = ms.Range("L" & selection.row) ' the current file in the Master I am working on

Mrange = NRange & " " & Mrange
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
'Selection' exists only on the active sheet. Assuming you have selected the appropriate cell on each of the sheets Master and oPhrase, try this:
VBA Code:
Sub concat()
Dim phCell As Range, msCell As Range, ph As Worksheet, ms As Worksheet, Mrange As Range, Nrange As Range
Set ms = Worksheets("Master")
Set ph = Worksheets("oPhrases")
Application.ScreenUpdating = False
ms.Select: Set msCell = Selection
ph.Select: Set phCell = Selection
Set Nrange = phCell
Set Mrange = msCell
Mrange = Nrange & " " & Mrange
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
'Selection' exists only on the active sheet. Assuming you have selected the appropriate cell on each of the sheets Master and oPhrase, try this:
VBA Code:
Sub concat()
Dim phCell As Range, msCell As Range, ph As Worksheet, ms As Worksheet, Mrange As Range, Nrange As Range
Set ms = Worksheets("Master")
Set ph = Worksheets("oPhrases")
Application.ScreenUpdating = False
ms.Select: Set msCell = Selection
ph.Select: Set phCell = Selection
Set Nrange = phCell
Set Mrange = msCell
Mrange = Nrange & " " & Mrange
Application.ScreenUpdating = True
End Sub

'Selection' exists only on the active sheet. Assuming you have selected the appropriate cell on each of the sheets Master and oPhrase, try this:
VBA Code:
Sub concat()
Dim phCell As Range, msCell As Range, ph As Worksheet, ms As Worksheet, Mrange As Range, Nrange As Range
Set ms = Worksheets("Master")
Set ph = Worksheets("oPhrases")
Application.ScreenUpdating = False
ms.Select: Set msCell = Selection
ph.Select: Set phCell = Selection
Set Nrange = phCell
Set Mrange = msCell
Mrange = Nrange & " " & Mrange
Application.ScreenUpdating = True
End Sub
JoeMo, thanks so much. Beautiful.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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