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

Untested but it would probably work as-is if above the line that broke you added `sourceSheet.Select` — Marcucciboy2, Dec 14 '22 at 06:28
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Dec 14 '22 at 07:44

Cameron Critchlow · Accepted Answer · 2022-12-14T01:00:14.067

#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

that is a beautiful and inspiring solution. It worked perfectly. Thank you for your help. — Lupus, Dec 14 '22 at 13:32

Cameron Critchlow · Answer 2 · 2022-12-14T00:45:21.233

0

#1
The Range object Already Includes the sheet as a parent.

Sub fCopyPasteValues(sourceSheet As Worksheet, targetSheet As Worksheet, sourceRange As Range, targetRange As Range)
    
        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

edited Dec 14 '22 at 00:45

answered Dec 14 '22 at 00:39

Cameron Critchlow

1,814
1
4
14

Or... technically I think this would work... but just dont: `sourceSheet.range(sourceRange.address).Select` – Cameron Critchlow Dec 14 '22 at 00:41
the problem here was, that i didn't activate the sheet on witch the range was. Your solution is indeed shorter and nicer than mine :) . Thank you for your help – Lupus Dec 14 '22 at 13:36
As long as you build good range variables, there is never any need to `.activate` or `.select` . In your case, the best option would be to just reference the range object: `Set sourceRange = workbooks("WorkbookName").Worksheets("SheetName").Range("YourRange")` . There will never be a need to reference a sheet or workbook in conjunction with that range object, nor will you need to use `.activate` or `.select` . – Cameron Critchlow Dec 14 '22 at 17:04

using variable for worksheets names and ranges VBA

2 Answers2