Excel VBA : Copy 2 ranges by use of Application.Caller

[Hidden Dan]

My friends, as a novice on VBA I'm struggling with a script. Basically it should select 2 defined ranges and copy those to the clipboard. That's all.

But I have 18 different value ranges and 2 header ranges. Script should combine 1 header with 1 single range. So I need 18 individual scripts, however I learned that just 1 should do the tric. I currently use an Application.Caller script to hide/show different rows. My idea is to use this script as a backbone for combining ranges. Nonetheless I can't figure it out.

This script is in use for hide/show rows and runs from a button.

Sub ShowHideRows()

    Dim arr


    'split the calling button name into an array
    '  (array will be zero-based)
    arr = Split(Application.Caller, "_")


    '**EDIT** check array is expected size...
    If UBound(arr) <> 3 Then Exit Sub


    If IsNumeric(arr(1)) And IsNumeric(arr(2)) Then
        With ActiveSheet  ' "Me" if the code is in the sheet module, else "ActiveSheet"
            .Unprotect Password:=""


            'arr(1) determines start row
            'arr(2) determines # of rows
            'arr(3) determines if rows are hidden or not
            .Cells(arr(1), 1).Resize(arr(2), 1).EntireRow.Hidden = (arr(3) = "H")
            .Protect Password:=""
        End With
    End If
End Sub

My button may be called like: btn_16_5_H and it will hide row 16-20 (-> btn_16_5_S will show row 16-20)


For copying ranges to the clipboard I want to combine 1 header + 1 range, triggered by a button. So

Header : RHEAD1 / RHEAD2
Range 1 : RIVA01
Range 2 : RIVA02
.......................
Range 18: RMVA12

Button 1 : btn_RHEAD1_RIVA01
Button 2 : btn_RHEAD1_RIVA02
..........................................
Button 18: btn_RHEAD2_RMVA12

And so on.


Is anyone around that can help me to complete this script ?

Grateful, Dan

 

[Hidden Dan]

In addition, I do know how to deal with the copy process. I use

    Application.Goto Reference:="RHEAD1"
    Range("B11:Z12,B1071:Z1099").Select
    Range("B1071").ActivateSelection.Copy
    Range("B1110").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1110").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Cut

This works fine to me.