Using String Variables inside a Range selection for Column and Row values

Question

I need to copy a range selection from one sheet to another.

The range is dynamic based on .Find results. I am able to pass all the required data into variables, but getting those string variables (both Column and Row values) into the Range to copy gives me

"Run Time Error 1004: PasteSpecial method of Range class failed ..."

PasteSpecial Error Debug

My full code:

Private Sub AutoFill_Week_One()

Dim Well_1 As Range
Dim GasComp As Range

Dim gRow As Integer
Dim lRow As Integer

Dim GsRow As String
Dim LsRow As String

Dim GsDate As String
Dim LsDate As String

Dim GsRngS As String
Dim GsRngE As String
Dim LsRngS As String
Dim LsRngE As String

Dim GsComp As String
Dim LsComp As String

With Sheets("CSV Import")

    Set Well_1 = Range("O1:O200").Find("102040307310W600")
    Well_1.Activate
    
    gRow = ActiveCell.Row
    
    GsRow = "A" & CStr(gRow)
        
    If Range(GsRow).value = "G" And Well_1.value = "102040307310W600" Then

        GsDate = "E" & CStr(gRow)

        MsgBox GsDate

        Range(GsDate).Copy

        Sheets("Week One").Range("F31").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

        GsRngS = "T" & CStr(gRow)
        GsRngE = "AG" & CStr(gRow)

        GsComp = GsRngS & ":" & GsRngE

        MsgBox GsComp

        Set GasComp = .Range(GsComp)

        GasComp.Copy

        Sheets("Week One").Range("F33:F46").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    End If

    Set Well_1 = Range("O1:O200").FindNext(Well_1)
    Well_1.Activate
    
    lRow = ActiveCell.Row
    
    LsRow = "A" & CStr(lRow)

    If Range(LsRow).value = "L" And Well_1.value = "102040307310W600" Then
        MsgBox "Liquid"
    End If

End With

End Sub

I tried arrays, .Offset and every other thing I could think of.

Hey BigBen that transfer is working! But I can't get the syntax to transfer them to a vertical range (F33:F46) they are only transferring horizontally!

With ThisWorkbook.Sheets("CSV Import").Range(GsComp)
    Sheets("Week One").Cells(33, 6).End(xlUp).Cells(46, 6).Resize(.Rows.Count, .Columns.Count) = .value
End With

Try changing `Sheets("Week One").Range("F33:F46")` to `Sheets("Week One").Range("F33")` — BigBen, Jun 26 '20 at 19:38
You should qualify which workbook before the `Sheets` too... presumably `ThisWorkbook`. — BigBen, Jun 26 '20 at 19:40
Wow, thanks so much for these quick responses! I did both of those things and still get the error... It looks like my copying syntax is working but it will not paste to the next sheet... — EP Studio, Jun 26 '20 at 19:45
Do you have any merged cells in the sheet you're trying to paste on? — BigBen, Jun 26 '20 at 19:47
Not in those destination cells F33:F46 but the F column does have some merged cells — EP Studio, Jun 26 '20 at 19:50
You might try using value transfer instead of the clipboard, as demonstrated [here](https://stackoverflow.com/questions/51528000/vba-paste-as-values-how-to). — BigBen, Jun 26 '20 at 19:56
@BigBen Have a look to my edited post the value transfer is working but I can't get the right syntax to "paste" vertically!\ — EP Studio, Jun 26 '20 at 21:46

score 0 · Accepted Answer · answered Jun 26 '20 at 22:26

But I can't get the syntax to transfer them to a vertical range (F33:F46) they are only transferring horizontally!

Use Application.Transpose here:

With ThisWorkbook.Sheets("CSV Import").Range(GsComp)
    Sheets("Week One").Range("F33").Resize(.Columns.Count).Value = Application.Transpose(.Value)
End With

Using String Variables inside a Range selection for Column and Row values

1 Answers1