Paste value (Transpose) to specific Range in Cells via InputBox

Hello Seniors,

I have following code which is bit semi-auto

Please Login or Register  to view this content.

'

Since new data would keep coming, i want Input Box be used to allow User to select range (Say B7 here) where copied data could be pasted as values using Transpose. How to do it?

If, above is not doable, Is there a way whereby copied data values from Source worksheet could be pasted [transpose - from column to rows] in last used range of row in Column B of Targetworksheet .

Thanks

Originally Posted by maniacb

And for your option of entering a range

Please Login or Register  to view this content.

This does not allow to select range through Mouse, Have to enter cell reference manually inside Input Box , but worked.

Worked like a charm for solution suggested in #2 above.

Many Thanks

Paste value (Transpose) to specific Range in Cells via InputBox

Originally Posted by analystbank

This does not allow to select range through Mouse, Have to enter cell reference manually inside Input Box , ....

Hello is analystbank
Maybe we is not quite clear then what you want.

For your info only is:
maniacb, he is Input Box function ( https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function )he did it… any you to enter cell reference manually inside Input Box
( But also important to note he is with formats )

Jindon he is dided for coding only values , ( not format is copied ). Also he is dided Application InputBox method ( https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox ) - It is different one isn’t it – You can choose. Jindon dided chose Type:=8 last argument for then you select the range and it is put in automatically you don’t have to enter cell reference manually inside Input Box

Other info is from me:
_ Microsoft Application.InputBox method it does have some Bugs, so people often prefer not to use it
_ Transpose function ( jindon he did use it ) it does have some Bugs, so people often prefer not to use it

---

Just some ideas I do have:

---

_1 idea
Simple macro is like mixture of maniacb and jindon. Use inputBox function like maniacb and values only are transposed like jiundon but not quite the same is, because I don’t like to use Transpose – I can use Index instead I did ( https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html#post5410028 )

Please Login or Register  to view this content.

Code it is in Book1.xls – I did upload its attached Managed

---

_2 idea
This is maybe a bit complicated, but just idea is
Only a Pop up message box will tell you what to do. But you ignore it. Then select the cell in worksheet “Target”

This is macros you must have, and you run Sub Jewano2()
In Normal Code module

Please Login or Register  to view this content.

In ThisWorkbook code module

Please Login or Register  to view this content.

Coding they is in Book1.xls – I did upload its attached Managed also

---

_2 idea
This is maybe very simple for you
All is in workbook zeszyt1.xls
You only have to select cell in worksheet “Target”
In ThisWorkbook code module

Please Login or Register  to view this content.

Jewano

---

Ref
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function
https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox
https://excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function
https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html#post5410028