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

[CsJHUN]

Hi guys, i have this (snipet):

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

 

[Fluff]

Try

Set reqdb = Workbooks.Open(dblink, ReadOnly:=True, Password:="asdqwe")
For i = 1 To reqdb.Sheets.Count
    reqdb.Sheets(i).Activate

 

[mikerickson]

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

But, if you qualify the sheet that you are activating, that should work.

For i = 1 To reqdb.Sheets.Count
    reqdb.Sheets(i).Activate
...

 

[CsJHUN]

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?

 

[Fluff]

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

reqdb.Sheets(i).Activate

activates the sheet in the reqdb workbook, so that is the name you should be getting

 

[CsJHUN]

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

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

reqdb.Sheets(i).Activate

then dim the Activesheet.Name to "asn" is working (that was before the addition of "reqdb.")

 

[Fluff]

Are you saying it's now working, or is there still a problem?

 

[CsJHUN]

works with "reqdb." added,

Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback