Excel VBA Cell Range Copy sub rountine

Question

Hello fellow programmers, I am having an issue with VBA coding in Excel. My issue is copying data from ranges of cells in VBA to other ranges of cells in VBA. Here is example of the sub routine I have...

 Public Sub CopyRange(ByVal pv_ws_source_worksheet As Worksheet, _
              ByVal pv_ws_destination_worksheet As Worksheet, _
              ByVal pv_rg_source_range As Range, _
              ByVal pv_rg_destination_range As Range)

    Dim Cell_Range As Range
    Dim CommaSplit() As String
    Dim ColonSplit() As String
    Dim i As Integer
    Dim j As Integer

    CommaSplit() = Split(pv_rg_destination_range.Address, ",")

    For Each Cell_Range In pv_ws_source_worksheet.Range(pv_rg_source_range.Address)
        pv_ws_destination_worksheet.Range(CommaSplit(i)).Value = Cell_Range.Value
        i = i + 1
    Next

End Sub

This sub currently can copy ranges of cells like B17:B24 to indiviual cells like B25,B18,B22,B21,B11,A12,A2,C2. I need this sub modified so that it can copy ranges of cells to other ranges of cells. An example of that idea is B24:B30 to C12:C17 and they can be on different sheets. Please help I would greatly appericate it :)

ApplePie ApplePie · Accepted Answer · 2012-11-16T20:32:57

You're overthinking this. You can do a simple:

Worksheet1.Range("something").Copy
Worksheet2.Range("something").PasteSpecial xlPasteValues
'If needed add this for formatting
'Worksheet2.Range("something").PasteSpecial xlPasteFormat

Excel VBA Cell Range Copy sub rountine

1 Answers