[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:

 Sub test()
 Dim PasteCellRange As String
 PasteCellRange = Cells(4, 2)

 Workbooks("TestCopyBook").Worksheets("Sheet1").Range("B6:B9").Copy

 Workbooks.Open "TestPasteBook.xlsx"
 Worksheets("Sheet1").Range("PasteCellRange").PasteSpecial

End Sub

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!