Multi entries in sheet using copy and paste with quick form

[PhilExcel]

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!

 

[My Aswer Is This]

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.

 

[PhilExcel]

Right but is there anyway to use Excel in a matter like this?

 

[My Aswer Is This]

When you want to add more data to the merged cells, do you always want to append to the end of the data.

 

[PhilExcel]

Yes, typically it's all new.

 

[My Aswer Is This]

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.

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:

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]

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]

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.

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

 

[PhilExcel]

Is there a way in this forum to send a file?

 

[My Aswer Is This]

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?