CODEWITHSUNDEEP
excelvba
marshall9901
marshall9901

Reputation: 1

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

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


``````

Upvotes: 0

Views: 333

Answers (1)

Pspl
Pspl

Reputation: 1474

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.

Upvotes: 0

Related Questions