[SOLVED] Copy and paste text into different workbook using specified cell location

[notgoodatexcl]

I'm trying to make a macro that will copy text from one workbook and paste it into another. However, the cell that it needs to be pasted in will differ based on the user (i.e one user may want to paste the text in Cell A1, while a different user may want to paste the text in Cell E45).

I thought a user-friendly way of doing this would be to have a "Paste Cell Range" cell where the user could just type in which cell range they wanted their text to be pasted in, and the macro would use that cell value when determining what cell range to paste the text in.

Normally, I would paste something using:

Worksheets("Sheet1").Range("A1").PasteSpecial

I was wondering what I would write to dimension a variable as the cell the user input in the excel sheet. Let's say the user specified in Cell(4 , 2) that they wanted their text to be pasted into TestPasteBook.xlsx in A1. I tried:

Please Login or Register  to view this content.

The code runs when I manually replace "PasteCellRange" with "A1", but if I leave it I get a "Run-time error '1004': Application-defined or object-defined error".

So I'm assuming that I'm declaring the variable incorrectly, but I'm not sure the correct method.

Thanks!

[MrChipper]

In the last line of code before End Sub you don't need "" around PasteCellRange.

[kev_]

With the paste to cell reference typed into cell B4, try this:

Please Login or Register  to view this content.

and could be tidied up with

Please Login or Register  to view this content.

[notgoodatexcl]

That was the issue, the code works now. Thanks!