Handling data/copying to another sheet

Question

I have the following lines of code in one module that operates on sheet 1. I want three of these values to transfer to the second sheet as they're calculated. This method does what I want it to but the activate commands switches sheets which I don't want. There's multiple solutions to this that I can think of. I could use the worksheets("") prefix to refer to cells in that sheet. Or I could run another module in that sheet picking up values from the first one, separately.

But I wanted to learn what the most common and efficient method is because this feels like something very basic and easy. Not just for copying cells but for any other manipulation. A way to operate on another sheet without using "activate" and switching sheets.

    Range("g2") = length
    Range("g3") = t1
    Range("g4") = t2
    Range("g5") = ti
    Range("g6") = freq
    
    Worksheets("Sheet2").Activate
    
    Range("b2") = t1
    Range("b3") = t2
    Range("b4") = ti

Is With Sheets ("") the best method to use here ?

Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba. It's not only about Select, but also about Activate (and how to avoid them) — FunThomas, Aug 20 '21 at 12:19
@FunThomas thanks for this thread.! I think I prefer using with worskheets("") to handle data on that page better than using the prefix. — Wren, Aug 20 '21 at 12:28
That's up to you. Just be aware that you should avoid to have something like `Range` in your code (that's called *"unqualified"*). Instead, always tell Excel which sheet you want to access so your code doesn't rely on the Active Sheet. — FunThomas, Aug 20 '21 at 12:33
@Wren the example he gives has code for that. Scroll down to the part about "Dim a Worksheet variable" — Jimmy Smith, Aug 20 '21 at 12:33
@FunThomas I don't think I fully understand you. Do you mean I should add the worksheet name as a prefix to range whenever I use it? Like ws.range("") ? — Wren, Aug 20 '21 at 13:01
Basically you should avoid writing `Range(...)` or `Cells(...)` in a regular module without prefixing/qualifying them with a worksheet object, so `ws.Cells(...)` for example, or `.Cells()` in a `With` block. `Range(...)` and `Cells(...)` in a *worksheet* code module default to that worksheet, so it's a little different there, but maybe good practice to use (eg) `Me.Range(...)` for the sake of being explicit. — Tim Williams, Aug 20 '21 at 15:54
@TimWilliams I just came online to say that I understand now why I need the sheet name as prefix. I had two files open and while running the vba editor I must've "activated" the other workbook and the module kept returning an error even though it was running fine just earlier. I understood eventually its because it couldn't activate the worksheet because it was a different file altogether . Thanks! — Wren, Aug 20 '21 at 16:24
@TimWilliams actually this error persists. If I have another excel file open and I have that opened and highlighted, the module for the first workbook throws an error at me every time. I thought using "set ws = worksheets("") " would solve this. Oh okay wait do i need to add "workbook("").worksheets("") " ? Edit : yes! — Wren, Aug 20 '21 at 16:31
Yes - if you use (eg) `ThisWorkbook.Worksheets("sheetNameHere")` you will avoid errors when working with multiple workbooks open. Also best to do something like (eg)`Dim wbSource As Workbook: Set wbSource = Workbooks("myData.xlsx")` and then use `wbSource` after that instead of having `Workbooks("myData.xlsx")` all over your code. — Tim Williams, Aug 20 '21 at 16:55
@TimWilliams thanks a lot brother! I came for a different query and learned a good habit to develop. This would've been a huge pitfall for me in the future for sure. — Wren, Aug 20 '21 at 17:38
You know what they say - "good habits come from experience - experience comes from bad habits" ;-) — Tim Williams, Aug 20 '21 at 17:56

score 1 · Accepted Answer · answered Aug 20 '21 at 18:26

Qualifying Objects

Range("A1") - an unqualified range
Worksheets("Sheet1").Range("A1") - (a range with) an unqualified worksheet
ThisWorkbook.Worksheets("Sheet1").Range("A1") - a fully qualified range

Sub Qualify()
    
    ' ...
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    'Set wb = ActiveWorkbook ' the workbook you're 'looking at'
    'Set wb = Workbooks("Test.xlsm") ' an open workbook
    'Set wb = Workbooks.Open("C:\Test\Test.xlsm") ' a to be opened workbook
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    
    sws.Range("G2").Value = Length
    sws.Range("G3").Value = t1
    sws.Range("G4").Value = t2
    sws.Range("G5").Value = ti
    sws.Range("G6").Value = freq
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet2")
    
    dws.Range("B2").Value = t1
    dws.Range("B3").Value = t2
    dws.Range("B4").Value = ti

End Sub

Sub QualifyWith()
    
    ' ...
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    With wb.Worksheets("Sheet1")
        .Range("G2").Value = Length
        .Range("G3").Value = t1
        .Range("G4").Value = t2
        .Range("G5").Value = ti
        .Range("G6").Value = freq
    End With
    
    With wb.Worksheets("Sheet2")
        .Range("B2").Value = t1
        .Range("B3").Value = t2
        .Range("B4").Value = ti
    End With

End Sub

Thanks sir, this was a helpful concept to learn early on in my VBA journey. — Wren, Aug 23 '21 at 03:39

Handling data/copying to another sheet

1 Answers1

Qualifying Objects