using variable for worksheets names and ranges VBA

Question

Hi i'm tring to use functions as generaly formulated as i can. In this code i neet to copy some data from one sheet to an other and i get only debugs... please help:

Public Function fCopyVerfügbarkeitenData()

    Dim sourceRange As Range
    Dim targetRange As Range
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    
    Set sourceRange = Sheets("Verfügbarkeit_Daten").Range("A4")
    Set targetRange = Sheets("Verfügbarkeiten").Range("A2")
    Set sourceSheet = Sheets("Verfügbarkeit_Daten")
    Set targetSheet = Sheets("Verfügbarkeiten")
    

    
    sourceSheet.Visible = True
    sourceSheet.Activate
    Call Tabelle18.refreshVerfuegbarkeiten
    
    Call fCopyPasteValues(sourceSheet, targetSheet, sourceRange, targetRange)
    sourceSheet.Visible = False
                    
End Function

Sub fCopyPasteValues(sourceSheet As Worksheet, targetSheet As Worksheet, sourceRange As Range, targetRange As Range)
    
        sourceSheet.sourceRange.Select   <-------------- THIS is where i get the Error, saying method or dataobjet not found! refering to sourceRange 

        Range(Selection, Selection.End(xlDown).End(xlToRight)).Select
        Selection.Copy
        targetRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        targetRange.NumberFormat = "m/d/yyyy"
    
End Sub

i tried to copy some data from one sheet to an other using functions and variables so i don't have to write the same code over and over again

Answer 1

#2
OR, an even cleaner way to do everything your function does.
Also I changed it into a function and gave it a bit more stability:

Public Function fCopyVerfügbarkeitenData()

    Dim sourceRange As Range
    Dim targetRange As Range
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    
    Set sourceRange = Sheets("sheet1").Range("A4")
    Set targetRange = Sheets("sheet1").Range("A2")
    Set sourceSheet = Sheets("Verfügbarkeit_Daten")
    Set targetSheet = Sheets("Verfügbarkeiten")
    
    
    sourceSheet.Visible = True
    sourceSheet.Activate
    Call Tabelle18.refreshVerfuegbarkeiten
    
    fCopyPasteValues sourceRange, targetRange
    sourceSheet.Visible = False
                    
End Function

Function fCopyPasteValues(sourceRange As Range, targetRange As Range)
        
        ' Width and Height
        Dim W As Long
        Dim H As Long
        
        ' Find Outer Bounds
        H = sourceRange.End(xlDown).Row - sourceRange.Row + 1
        W = sourceRange.End(xlToRight).Column - sourceRange.Column + 1
        
        ' Error handing in case region is only 1 cell wide or high
        If H > 100000 Then H = 1
        If W > 100000 Then W = 1
        
        ' Copy and reformat Action
        targetRange.Resize(H, W).Value = sourceRange.Resize(H, W).Value
        targetRange.NumberFormat = "m/d/yyyy"
        
End Function