Excel VBA - Use cell ref listed in a cell within VBA Copy-Paste subroutine

Question

Im somewhat of a novice at Excel VBA. Im trying to create a sub routine that copies the contents from a cell Sheet1 A2 and pastes to cell Sheet2 A2, the latter being specified in cell Sheet 1 B1. Simple expample.

Sheet1 A2 = 100
Sheet1 B1 = Sheet2 A2

I need help with the code that looks at the cell Sheet1 B1 for the location "Sheet2 A2" to paste the value 100 into?

My curent code is:

Sub CopyRows()
    Sheets("Sheet1").Select
    ' Find the last row of data
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' Loop through each row
    For x = 1 To FinalRow
        ' Decide if to copy based on column D
        ThisValue = Cells(x, 4).Value
        If ThisValue = "A" Then
            'Cells(x, 1).Resize(1, 33).Copy
            Cells(x, 1).Resize(1, 1).Copy
            Sheets("SheetA").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        ElseIf ThisValue = "B" Then
            Cells(x, 1).Resize(1, 1).Copy
            Sheets("SheetB").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        End If
    Next x
End Sub

K.Dᴀᴠɪs K.Dᴀᴠɪs · Accepted Answer · 2018-12-02T09:05:35

I am not entirely sure how exactly the code you provided is related to your actual question, but in regards to what you're asking:

"...copies the contents from a cell Sheet1 A2 and pastes to cell Sheet2 A2, the latter being specified in cell Sheet 1 B1". Where B2's value is formatted as Sheet2 A2.

You can split the value in this cell, which would give you an array of two strings: Sheet2 and A2 after the split. You can use these values to set your range object.

Once you set the range object, simply pass the value from the hard-coded range Worksheets("Sheet1").Range("A2") to the dynamic range provided in B2.

Dim wsSource As Worksheet, rawRangeArr() As String, rng As Range
Set wsSource = ThisWorkbook.Worksheets("Sheet1")
rawRangeArr = Split(wsSource.Range("B1").Value)
Set rng = ThisWorkbook.Worksheets(rawRangeArr(0)).Range(rawRangeArr(1))

rng.Value = wsSource.Range("A2").Value

Excel VBA - Use cell ref listed in a cell within VBA Copy-Paste subroutine

2 Answers