Excel VBA, getting range from an inactive sheet

by

Your issue is that the because the Cell references inside the Range ‘s are unqualified, they refer to a default sheet, which may not be the sheet you intend.
For standard modules, the ThisWorkbook module, custom classes and user form modules, the defeault is the ActiveSheet. For Worksheet code behind modules, it’s that worksheet.

For modules other than worksheet code behind modules, your code is actually saying

Sheets("Temp").Range(ActiveSheet.Cells(1), ActiveSheet.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial

For worksheet code behind modules, your code is actually saying

Sheets("Temp").Range(Me.Cells(1), Me.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial

In either case, the solution is the same: fully qualify the range references with the required workbook:

Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = ActiveWorkbook.Sheets("Temp")
Set sh2 = ActiveWorkbook.Sheets("Overview")

With sh1
    .Range(.Cells(1,1), .Cells(1,1).End(xlDown)).Copy
End With
sh2.Range("C40").PasteSpecial

Note: When using .End(xlDown) there is a danger that this will result in a range extending further than you expect. It’s better to use .End(xlUp) if your sheet layout allows. If not, check the referenced cell and the cell below for Empty first.

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. What is the default scope of worksheets and cells and range?
  4. Is there any event that fires when keys are pressed when editing a cell?
  5. Does the VBA “And” operator evaluate the second argument when the first is false?
  6. Split cell values into multiple rows and keep other data
  7. Save each sheet in a workbook to separate CSV files
  8. Is VBA an OOP language, and does it support polymorphism?
  9. Export each sheet to a separate csv file
  10. What are .NumberFormat Options In Excel VBA?
  11. Detect if range is empty
  12. Checking if a worksheet-based checkbox is checked
  13. Assign keyboard shortcut to run procedure
  14. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  15. Uncheck all checkboxes across entire workbook via CommandButton
  16. return csv file as recordset
  17. VBA workbooks.Close without being prompted to if the user wants to save?
  18. Extract Data from Word Document to an Excel SpreadSheet
  19. VBA equivalent to Excel’s mod function
  20. Copy a selected range to another worksheet
  21. Excel macro – paste only non empty cells from one sheet to another
  22. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  23. Pointers needed for speeding up nested loop macro in VBA
  24. ‘1004’: “The sort reference is not valid.”
  25. Select entire column in table using Excel VBA
  26. Create hyperlink to another sheet
  27. Reducing WithEvent declarations and subs with VBA and ActiveX
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment