Excel VBA Using String Variables inside a Range selection for Colum AND Row values

Question

this has been killing me for the last week so I'm breaking down and asking for help! I have searched this exhaustively and haven't been able to crack it... Hoping someone can save me!

I need to copy a range selection from one sheet to another which should be an easy task, however the range is dynamic based on .Find results... I am able to pass all the required data into variables no problem but getting those string variables (both Column AND Row values) into the Range to copy keeps giving me the "Run Time Error 1004: PasteSpecial method of Range class failed ..."

PasteSpecial Error Debug

Here is my full code currently:

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

Sorry, I know this code is kinda hacked together but I've tried arrays, .offset and every other thing I could think of with no luck...

Any thoughts?

Thanks so much in advance!

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! What am I missing?

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

Excel VBA Using String Variables inside a Range selection for Colum AND Row values

1 Answers1