Multi entries in sheet using copy and paste with quick form

PhilExcel

New Member
Joined
Dec 10, 2016
Messages
19
I've posted a message before with this spreadsheet I use at work with Excel 2013. The page is laid out where multi items need to be pasted in. The cells to paste in are merged and it doesn't allow the data to be directly entered into the cells, I need to click on the cells then paste in the formula bar. I've unmerged the cells and tried 'Center across selection' as horizontal alignment. But this causes all kinds of issues with justification.

I wanted to show an example but I don't see an option to attach it. There are at least 23 items that have to be dropped in. Consider I'm using this all day and working multiple claims at a time while on the phone, I'm going mad with this arcadic process!

Doing this should be a simple, clean and fast. Working many claims I'm finding myself making mistakes, like dropping in the wrong cell or the text from the source program didn't copy.

So since the format has to be like this. Is there a way to create a separate area on the sheet or on another sheet where I can add the data in a form, just like with Access. This way it would be a simple interface, limiting errors. Then of course the data would move into the formatted sheet.

Any suggestions are appreciated!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Most people on this Forum discourage using merged cells. It may make things look nice on your screen but this can cause you all sort of problems when you want to work with this data as you are having now.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
When you want to add more data to the merged cells, do you always want to append to the end of the data.
 

PhilExcel

New Member
Joined
Dec 10, 2016
Messages
19

ADVERTISEMENT

Yes, typically it's all new.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
When referring to a range of merged cells we always have to refer to the upper left cell in the range.

So this script would take the data from Range("B2") and enter that data into Range("H3")
H3 being the upper left cell in the Merged Range.


Code:
Sub Add_To_Merged_Cell()
Range("H3").Value = Range("B2").Value
End Sub

Now you said:

There are at least 23 items that have to be dropped in.

If that is the case we would need a script something like this:

Code:
Sub Add_To_Merged_Cell_With_New_Line()
Range("H3").Value = Range("H3").Value & vbNewLine & Range("B2").Value
End Sub

This Vba script always adds the value in Range("B2") to the merged cells and adds a newline to the data
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Now this script could be modified to work with more then one cell of data.

You could enter all 23 items into 23 different cells then when your ready run the script and all the values would be added to the merged cells in the order you want with a new line after each entry.
If you want this to happen you would need to tell me where these separate ranges are and where the top left corner of the merged cells are.
And doing this would require Vba
You could put the script in a button and when you click the button the script would run.
If your interested in this approach let me know.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Assuming again the top left cell in your merged range is Range("H3")

Try this:
Put all the values you want entered into the merged cells into Range("A1") and below.

So if you enter "David" in Range("A1") and "Mary" into range ("A2")

Your merged cells would now have:

David
Mary

You can put as many values into Column "A" as you like.

When the script is run the values in column "A" are cleared and ready for next time.

Code:
Sub Add_To_Merged_Cell_With_New_Line()
'All values Column "A"
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        Cells(3, "H").Value = Cells(3, "H").Value & Cells(i, 1).Value & vbNewLine
    Next
Range("A1:A" & Lastrow).ClearContents
Application.ScreenUpdating = True
End Sub
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
There may be but people like me do not like opening files due to security concerns.
Your question seemed easy to understand. You never answered back as if my suggestion in post # 8 would work for you.


Is there a way in this forum to send a file?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

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
Top