+ Reply to Thread
Results 1 to 19 of 19

`range` in another sheet

  1. #1
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    `range` in another sheet

    Hey again,

    I have a problem with applying the following code (thanks Hahobe) to other sheets; the issue is that the cell I need to refer to in other sheets is in sheet1 (cell A27), but I need it here (still in sheet1) when I apply the following code into other sheets(2,3,4,5,6).
    Please Login or Register  to view this content.
    If you don`t know what I exactly mean you can take a look at my previous thread when there is the whole story:
    http://www.excelforum.com/excel-prog...ific-cell.html

    many thanks in advance for your help!

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: `range` in another sheet

    if you want use another Sheet you have to change
    With Sheets("Sheet1")
    If solved remember to mark Thread as solved

  3. #3
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: `range` in another sheet

    try this one. This will affect all sheets in one macro.

    Please Login or Register  to view this content.
    Last edited by thisisgerald; 10-10-2012 at 06:48 AM. Reason: edited for vogelman requirements
    Don't forget to mark your thread as [SOLVED].

  4. #4
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: `range` in another sheet

    I know patel, but this is more complex

    for exqmple, for sheet2 I need to delete rows from 3 to 40 if the values in column B does not match to the one in cell A27 in sheet1. For sheet3 the range will also be different.

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: `range` in another sheet

    I reckon you mean
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  6. #6
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: `range` in another sheet

    JosephP in your code there is no indication that in sheet2 we check values from column B instead of A, so I think it won`t work..

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: `range` in another sheet

    so change Cells(r, 1) to Cells(r, 2)

  8. #8
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: `range` in another sheet

    looks like it would work
    can A27 (reference cell) be in another excel file? let`s call it ´Venray´
    so how should this line look like:
    Please Login or Register  to view this content.
    ?
    Maybe like this:
    Please Login or Register  to view this content.
    ?

  9. #9
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: `range` in another sheet

    @Vogelman

    It is possible to assign variable from other workbook


    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: `range` in another sheet

    gerald the workbook already exist and is opened before and while the macro is running, so there is no need to create new (it only makes me confused)
    any idea?

  11. #11
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: `range` in another sheet

    I'm also working with it..
    Last edited by thisisgerald; 10-10-2012 at 08:01 AM.

  12. #12
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: `range` in another sheet

    thank you!

    another, quick question before I mark this topic as solved:

    how to rename the new workbook? lets suppose that I have a macro that creates and then activates the created workbook; I want to rename it and use the new name while activating.

  13. #13
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: `range` in another sheet

    you have to save the workbook to name it

  14. #14
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: `range` in another sheet

    Here it is.

    Please Login or Register  to view this content.
    how to rename the new workbook? lets suppose that I have a macro that creates and then activates the created workbook; I want to rename it and use the new name while activating.
    when you use:
    Please Login or Register  to view this content.
    this would open the file but with different file name.
    example, filename is Excel.xls once added, it would be Excel1.xls
    then you can use
    Please Login or Register  to view this content.
    to save new file name

  15. #15
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: `range` in another sheet

    is it possible to save it by implementing the right code?
    to open a new workbook I use
    Workbooks.Add

    what`s the code for saving it under specific name (e.g. `Venray`)?

  16. #16
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: `range` in another sheet

    Please Login or Register  to view this content.
    for instance

  17. #17
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: `range` in another sheet

    Yes it is possible.

    Change "Your Path" on the path you want to save.

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: `range` in another sheet

    thank you guys!
    problem solved

  19. #19
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: `range` in another sheet

    you're welcome :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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