Results 1 to 9 of 9

Thread: macro change back to "thisworkbook" from "active workbook"

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Location
    Hungary, Budapest
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default macro change back to "thisworkbook" from "active workbook"

    Hi guys, i have this (snipet):
    Code:
    Private Sub readreqdb()
    Sheets("allrequest").Activate
    ActiveSheet.Unprotect Password:="asdqwe"
    dblink = Sheets("settings").Range("AG11").Value
    Workbooks.Open dblink, ReadOnly:=True, Password:="asdqwe"
    Set reqdb = ActiveWorkbook
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        asn = ActiveSheet.Name
        lraas = Range("A" & Rows.Count).End(xlUp).Row
    ....
    this should open a write protected shared workbook on lan (the dblink value in a cell) loop thru every sheet and copy the data on that sheet to macroworkbook (thisworkbook) "Allrequest" sheet, and... (the rest is not important i guess).
    The problem is:
    in the for loop the "Sheet(i).activate" select the sheet in thisworkbook and not the database.
    Ideas?

    Cheers
    John
    Answer:= 42, but please be aware of the possible different local settings(in control panel).
    Make a backup of your original data before testing any VBA, it can't be undone.
    I'm still learning with you, don't be hard on me Playing on Excel'10 and Win7x64.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    Try
    Code:
    Set reqdb = Workbooks.Open(dblink, ReadOnly:=True, Password:="asdqwe")
    For i = 1 To reqdb.Sheets.Count
        reqdb.Sheets(i).Activate
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,581
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    You don't need to select worksheets to copy their contents.

    But, if you qualify the sheet that you are activating, that should work.
    Code:
    For i = 1 To reqdb.Sheets.Count
        reqdb.Sheets(i).Activate
    ...

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Location
    Hungary, Budapest
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    Thx Fluff & Mike, i thought about that, and it's working ofcourse.
    later there is a "asn = activesheet.name" which gives the "thisworkbook" sheet(i).name (I changed to reqdb.sheets(i).name, but still...)
    I think this could casue problem later. Do you have any idea which should cause this?
    Last edited by CsJHUN; Sep 9th, 2019 at 09:48 AM.
    Answer:= 42, but please be aware of the possible different local settings(in control panel).
    Make a backup of your original data before testing any VBA, it can't be undone.
    I'm still learning with you, don't be hard on me Playing on Excel'10 and Win7x64.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    Are you sure it's giving the sheet name from ThisWorkbook, do you have sheets in both workbooks that have the same name?
    this line
    Code:
    reqdb.Sheets(i).Activate
    activates the sheet in the reqdb workbook, so that is the name you should be getting
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Location
    Hungary, Budapest
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    Quote Originally Posted by Fluff View Post
    Are you sure it's giving the sheet name from ThisWorkbook, do you have sheets in both workbooks that have the same name?
    this line
    Code:
    reqdb.Sheets(i).Activate
    activates the sheet in the reqdb workbook, so that is the name you should be getting
    Yep, i'm sure, because thisworkbook.sheet(1).name = "OwnRequest" and the reqdb.sheet(1).name = "Munka1" and the macro result for sheet(1).name is "ownreaquest" without the reqdb.
    (the "Munka1" is the Sheet1 in Hungarian, so its the default, its contain all previous request, which must be read into "allrequest" sheet.
    And yes again, activating with
    Code:
    reqdb.Sheets(i).Activate
    then dim the Activesheet.Name to "asn" is working (that was before the addition of "reqdb.")
    Answer:= 42, but please be aware of the possible different local settings(in control panel).
    Make a backup of your original data before testing any VBA, it can't be undone.
    I'm still learning with you, don't be hard on me Playing on Excel'10 and Win7x64.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    Are you saying it's now working, or is there still a problem?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Jan 2015
    Location
    Hungary, Budapest
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    works with "reqdb." added,

    Thanks again.
    Answer:= 42, but please be aware of the possible different local settings(in control panel).
    Make a backup of your original data before testing any VBA, it can't be undone.
    I'm still learning with you, don't be hard on me Playing on Excel'10 and Win7x64.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: macro change back to "thisworkbook" from "active workbook"

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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