+ Reply to Thread
Results 1 to 4 of 4

referring to cells in other sheets

Hybrid View

  1. #1
    Registered User
    Join Date
    08-01-2013
    Location
    Verona - Italy
    MS-Off Ver
    Excel 2007-2010-2013
    Posts
    33

    referring to cells in other sheets

    Hi,

    in a code I faced with a lot of copy paste operation between different sheets. I see that this kind of statement doesn't work when the referred sheet is not selected (j and k are integer variables and >0):

    Sheets(sh.Name).Range(Cells(j + 1, 1), Cells(j + k, 7)).Copy
    and also similar like

    Worksheets(sh.Name).Range(Cells(j + 1, 1), Cells(j + k, 7)).Copy
    sh.Range(Cells(j + 1, 1), Cells(j + k, 7)).Copy
    do not work.

    The only way I see i to not use the "cells(...)" function inside "range(...)", but I find it a very simple way to refer to cells with variables like j and k in the example.

    I wonder also if there's a way avoid this problem without the necessity to select the sheet.

    Thans a lot
    Regards
    Diego

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,056

    Re: referring to cells in other sheets

    You just need to specify the sheet for the Cells property too:

    sh.Range(sh.Cells(j + 1, 1), sh.Cells(j + k, 7)).Copy
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    08-01-2013
    Location
    Verona - Italy
    MS-Off Ver
    Excel 2007-2010-2013
    Posts
    33

    Re: referring to cells in other sheets

    Oh my godness, what a stupid mistake... that's right! Thanks

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,056

    Re: referring to cells in other sheets

    Glad to help. (it's a very common mistake)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Look up across multiple sheets without referring sheet names
    By Shareez Saleem in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-10-2021, 02:27 AM
  2. Named range referring to multiple sheets
    By mjj300 in forum Excel General
    Replies: 3
    Last Post: 07-05-2019, 03:15 PM
  3. Referring to sheets by position
    By elmasguapo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2015, 05:44 PM
  4. Replies: 3
    Last Post: 02-11-2012, 08:34 AM
  5. List the cells with data validation rules referring to other sheets/books.
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-07-2011, 11:22 AM
  6. Referring to data on other sheets
    By aquinlan in forum Excel General
    Replies: 8
    Last Post: 05-29-2010, 06:34 PM
  7. Pivot shart referring to multiple sheets
    By Kanga in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-19-2005, 07:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1