Using Cell Value in one sheet as destination for paste in another

Question

I am new to VBA and I am trying to use the content of cell A2 in book1, sheet2 as the destination sheet of the paste and the content of cell E2 in book1, sheet2 as the destination cell to paste into. The content of these cells can change. I am copy and pasteing the content of book1,sheet1,cell(n15). any help is greatly appreciated. When I press the enter buton on book1, sheet1 I get runtime error subscript out of range and when I go into debug it highlights the line :

Workbooks("Book2.xlsx").Worksheets("WeekT.Value").Range("NameCel.value").PasteSpecial Paste:=xlPasteValue

````

    Public Sub Book1()
     Dim WeekT As Range
     Dim NameCel As Range
     Set WeekT = Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("A2")
     Set NameCel = Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("E2")
    End Sub
Private Sub Workbook_open()
         Worksheets("Sheet2").Range("A2").Value = (Me.Week.Value + "T")
         Worksheets("Sheet2").Range("C2").Value = (Me.Collegue_Name.Value)
         Worksheets("Sheet1").Range("N15").Value = ("")
        End Sub

        Private Sub Week_change()
         Worksheets("Sheet2").Range("A2").Value = (Me.Week.Value + "T")
        End Sub

        Private Sub Enter_Click()
     Set WeekT = Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("A2")
     Set NameCel = Workbooks("Book1.xlsm").Worksheets("Sheet2").Range("E2")
          Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("N15").Copy
          Workbooks.Open "F:\\Folder1\Book2.xlsx"
        Workbooks("Book2.xlsx").Worksheets(WeekT).Range(NameCel).PasteSpecial Paste:=xlPasteValues
        End Sub


``````

You say you are trying to do something, but not exactly what is your problem. If your code is not working, please describe the issue as specifically as possible. — SJR
You don't want the quotes here "WeekT.Value", but also you need to declare the variables outside the Open event as otherwise other procedures will not be able to access them. See cpearson.com/excel/Scope.aspx — SJR
thanks, I have put the dims and sets into a public sub, removed the quotes, and removed the .value off the sets. but now it is bringing error object required? and highlighting the same line. when I highlight the weekT and Namecel on that line it says they are empty? but the cells that are refered to are full — marshall9901

Pspl Pspl · Accepted Answer · 2019-04-15T13:35:40

It seems to me you complicated too much your task. To copy a cell value to another just write:

Workbooks("destiny file").Sheets("sheet name").Cells(Row, Column).FormulaR1C1 = Workbooks("origin file").Sheets("sheet name").Cells(Row, Column).value

It "flows" better if you use variables for your workbooks and worksheets:

----------
Dim W1 as Workbook
Set W1 = Workbooks("origin workbook")
Dim S1 as Worksheet
Set S1 = W1.Sheets("sheet name")
'---------
Dim W2 as Workbook
Set W2 = Workbooks("destiny workbook")
Dim S2 as Worksheet
Set S2 = W2.Sheets("sheet name")
'--> Copy the value:
S2.Cells(Row, Column).FormulaR1C1 = S1.Cells(Row, Column).value

This way you don't have to use Copy/Paste commands.

Using Cell Value in one sheet as destination for paste in another

1 Answers