Pass a cell range as a variable and copy data using a function-CodePudding

Im trying to create a function in VBA that copies data from one sheet to another based on the input to the function. However, I'm having great difficulty in using the arguments to the function in the actual function itself.

Below is my code: as you can see, the range I want is hardcoded in for now, and this works! But I cannot get it to accept the range I pass as an argument to the function. What am I doing wrong?

data should be in place of range(F35:F65) and target should be in place of range(C6)

Function Copytranspose(data As Range, target As Range)

    
    
    Worksheets("Data").Activate
    ActiveSheet.Range("F35:F65").Copy
    
    Worksheets("Totalizers").Activate
    ActiveSheet.Range("C6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        
End Function

Sub tempo()
Call Copytranspose(Range("F35:F65"), Range("C6"))

End Sub

CodePudding user response：

Copy Values of a Range

Two HardCoded Worksheets (Range Address (String) Parameters)

Option Explicit

Sub CopyRange( _
        ByVal SourceRangeAddress As String, _
        ByVal DestinationFirstCellAddress As String, _
        Optional ByVal DoTranspose As Boolean = False)
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    Dim sws As Worksheet: Set sws = wb.Worksheets("Data")
    Dim srg As Range: Set srg = sws.Range(SourceRangeAddress)
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("Totalizers")
    Dim dfCell As Range: Set dfCell = dws.Range(DestinationFirstCellAddress)
    
    Dim drg As Range
    
    If DoTranspose Then
        Set drg = dfCell.Resize(srg.Columns.Count, srg.Rows.Count)
        drg.Value = Application.Transpose(srg.Value)
    Else
        Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
        drg.Value = srg.Value
    End If
        
End Sub

Sub tempo()
    
    ' Either transpose...
    CopyRange "F35:F65", "C6", True ' 'C6:AG6'
    ' ... or do not transpose:
    'CopyRange "F35:F65", "C6" ' 'C6:C36'

End Sub

Any Ranges (Range Parameters)

Option Explicit

Sub CopyRange( _
        ByVal srg As Range, _
        ByVal dfCell As Range, _
        Optional ByVal DoTranspose As Boolean = False)

    Dim drg As Range
    
    If DoTranspose Then
        Set drg = dfCell.Resize(srg.Columns.Count, srg.Rows.Count)
        drg.Value = Application.Transpose(srg.Value)
    Else
        Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
        drg.Value = srg.Value
    End If
    
End Sub

Sub tempo()
    
    ' Either transpose...
    CopyRange Range("F35:F65"), Range("C6"), True ' 'C6:AG6'
    ' ... or do not transpose:
    'CopyRange Range("F35:F65"), Range("C6") ' 'C6:C36'
    
End Sub

CodePudding user response：

Something like this?

Sub CopyData(sourceRange As Excel.Range, targetRange As Excel.Range)

targetRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value

End Sub

Sub TestCopyData()
    
CopyData Sheets("Sheet1").Range("a1:a3"), Sheets("sheet1").Range("q1")
    
End Sub