Use VBA to copy and paste to variable worksheet and eange

userkiGl1 0
Viewed 40

I am new to VBA and hoping to reduce manual work. I want to be able to copy and paste a range from a fixed worksheet ("c4:c178") into a variable worksheet. I have two dropdowns, one that has a list of all the worksheet names and the other that has the column number. My hope is the user could select the worksheet name and column reference in the drop-down and then click the macro button that will copy and paste the range to that reference. Is this possible?

Sub CopyPaste()
Dim Sheetname As String
Sheetname = ActiveSheet.Range("i3").Value
Dim Col As Long
Col = ActiveSheet.Range("i4").Value
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Tracking Only")
Dim rng As Range
Set rng = ActiveSheet.Range("c4:C178")
With rng
ws.Cells(4, Col).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

End Sub

I receive "Run-time error '1004': Application-define or object-defined error" and when I click debug, it highlights the last row, with the ws.cells code

copy-paste vba excel
1 Answers

Break your process into steps, store the dropdown values using variables, and assign the .Value of the source range to the target range.

With ThisWorkbook.Worksheets("Tracking Only")
    Dim sheetName As String
    sheetName = .Range("I3").Value 

    Dim col As Long
    col = .Range("I4").Value
    
    Dim rng As Range
    Set rng = .Range("C4:C178")
End With

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(sheetName)

With rng
    ws.Cells(4, col).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
BigBen0
Related