ExcelBanter - Code to look at all sheets not just specified sheet ?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Code to look at all sheets not just specified sheet ? (https://www.excelbanter.com/excel-programming/366703-code-look-all-sheets-not-just-specified-sheet.html)

Code to look at all sheets not just specified sheet ?

I have been assisted with this code by some very helpful people, but i have
narrowed it down to these lines below that i need to modify slightly.

How can i change the [Worksheets(4)] in (2)two instances below to refer to
ALL(could be anywhere from 2-49) sheets in the workbooks instead ?

If mybook.Worksheets(4).Range("B3").Value = input1 And
mybook.Worksheets(4).Range("D3").Value = input2 Then
mybook.Worksheets(4).Copy
After:=basebook.Sheets(basebook.Sheets.Count)

The 3rd instance of Worksheets(4)[Worksheets(4).Copy] needs to be the sheet
that contains the 2 range values (B3" & "D3"),
where the other 2 instances [worksheets(4)] need to refer to ALL sheets in
the workbook.

Regards

Corey....

Code to look at all sheets not just specified sheet ?

Corey,

Add the following (wrapping around your code with 3 changes) and it should
work:

Dim i as Integer

mybook.Activate
For i = 2 to Sheets.Count

' Put the code you have below here, replacing the "Worksheets(4)" with
"Sheets(i)" in all 3 instances

Next i

I don't know of a way to do all at once, only to step through each sheet one
at a time...

David

How can i change the [Worksheets(4)] in (2)two instances below to refer to
ALL(could be anywhere from 2-49) sheets in the workbooks instead ?

If mybook.Worksheets(4).Range("B3").Value = input1 And
mybook.Worksheets(4).Range("D3").Value = input2 Then
mybook.Worksheets(4).Copy
After:=basebook.Sheets(basebook.Sheets.Count)

The 3rd instance of Worksheets(4)[Worksheets(4).Copy] needs to be the
sheet that contains the 2 range values (B3" & "D3"),
where the other 2 instances [worksheets(4)] need to refer to ALL sheets in
the workbook.

Regards

Corey....

Code to look at all sheets not just specified sheet ?

Corey,

The only time that I mess with documents on a network folder is when I
already have the network share mapped to a drive letter through the OS. If
you map the drive through the OS, check the box to restore the connection
upon reboot it will always have the same drive letter. Then, you can use
the path of "X:\.....\my documents\folder name", eliminate the ChDrive line
and the ChDir will work fine. (Changing the X:\... to the drive letter and
any higher level folders before the "My Documents")

I haven't messed with the ChDrive action before, but it could also be
causing an error since you are giving it a path instead of a drive letter,
and if it isn't already mapped, it could be looking for the
username/password for the network share.

David

"Corey" wrote in message
...
Thanks David for the reply.

I can seem to get the code to work great if i set it to a local
folder,(Did this to test and setup)
But as i need it to look to a folder on a network as:
MyPath = "\\Office2\my documents\folder name"
ChDrive MyPath
ChDir MyPath

I get an error in the ChDrive My Path
and ChDir My Path

Any idea's whay that is ?
And how to rectify it.
Corey....

Regards

Corey McConnell

Code to look at all sheets not just specified sheet ?

David,
I had the same thoughts, and actually went through MY Computer rather than
My Network Places, using "Z:\Costing" instead of \\Office2\my
documents\Costing, But i get the same error for some reason.

In the set up of the code(from Ron De Bruin's site) it says it is suitable
for Network applications....

Regards

Corey....

"Dove" wrote in message
...
Corey,

The only time that I mess with documents on a network folder is when I
already have the network share mapped to a drive letter through the OS.
If you map the drive through the OS, check the box to restore the
connection upon reboot it will always have the same drive letter. Then,
you can use the path of "X:\.....\my documents\folder name", eliminate the
ChDrive line and the ChDir will work fine. (Changing the X:\... to the
drive letter and any higher level folders before the "My Documents")

I haven't messed with the ChDrive action before, but it could also be
causing an error since you are giving it a path instead of a drive letter,
and if it isn't already mapped, it could be looking for the
username/password for the network share.

David

"Corey" wrote in message
...
Thanks David for the reply.

I can seem to get the code to work great if i set it to a local
folder,(Did this to test and setup)
But as i need it to look to a folder on a network as:
MyPath = "\\Office2\my documents\folder name"
ChDrive MyPath
ChDir MyPath

I get an error in the ChDrive My Path
and ChDir My Path

Any idea's whay that is ?
And how to rectify it.
Corey....

Regards

Corey McConnell

Code to look at all sheets not just specified sheet ?

chdir doesn't work with the \\Servername format.

Previously posted by Rob Bovey:
' Placed at the top of a general module outside any procedures:
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

' sample usage

Sub FindFile()
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
fName = Application.GetOpenFileName
End Sub

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...

David,
I had the same thoughts, and actually went through MY Computer rather than
My Network Places, using "Z:\Costing" instead of \\Office2\my
documents\Costing, But i get the same error for some reason.

In the set up of the code(from Ron De Bruin's site) it says it is suitable
for Network applications....

Regards

Corey....

"Dove" wrote in message
...
Corey,

The only time that I mess with documents on a network folder is when I
already have the network share mapped to a drive letter through the OS.
If you map the drive through the OS, check the box to restore the
connection upon reboot it will always have the same drive letter. Then,
you can use the path of "X:\.....\my documents\folder name", eliminate
the ChDrive line and the ChDir will work fine. (Changing the X:\... to
the drive letter and any higher level folders before the "My Documents")

I haven't messed with the ChDrive action before, but it could also be
causing an error since you are giving it a path instead of a drive
letter, and if it isn't already mapped, it could be looking for the
username/password for the network share.

David

"Corey" wrote in message
...
Thanks David for the reply.

I can seem to get the code to work great if i set it to a local
folder,(Did this to test and setup)
But as i need it to look to a folder on a network as:
MyPath = "\\Office2\my documents\folder name"
ChDrive MyPath
ChDir MyPath

I get an error in the ChDrive My Path
and ChDir My Path

Any idea's whay that is ?
And how to rectify it.
Corey....

Regards

Corey McConnell

Code to look at all sheets not just specified sheet ?

Thanks Tom.
Got the whole project up and running now.

Finally....

Thanks and thanks to ALL who assisted me with my constant posts over the
past weeks.

Corey....
"Tom Ogilvy" wrote in message
...
chdir doesn't work with the \\Servername format.

Previously posted by Rob Bovey:
' Placed at the top of a general module outside any procedures:
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

' sample usage

Sub FindFile()
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
fName = Application.GetOpenFileName
End Sub

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...

David,
I had the same thoughts, and actually went through MY Computer rather
than My Network Places, using "Z:\Costing" instead of \\Office2\my
documents\Costing, But i get the same error for some reason.

In the set up of the code(from Ron De Bruin's site) it says it is
suitable for Network applications....

Regards

Corey....

"Dove" wrote in message
...
Corey,

The only time that I mess with documents on a network folder is when I
already have the network share mapped to a drive letter through the OS.
If you map the drive through the OS, check the box to restore the
connection upon reboot it will always have the same drive letter. Then,
you can use the path of "X:\.....\my documents\folder name", eliminate
the ChDrive line and the ChDir will work fine. (Changing the X:\... to
the drive letter and any higher level folders before the "My Documents")

I haven't messed with the ChDrive action before, but it could also be
causing an error since you are giving it a path instead of a drive
letter, and if it isn't already mapped, it could be looking for the
username/password for the network share.

David

"Corey" wrote in message
...
Thanks David for the reply.

I can seem to get the code to work great if i set it to a local
folder,(Did this to test and setup)
But as i need it to look to a folder on a network as:
MyPath = "\\Office2\my documents\folder name"
ChDrive MyPath
ChDir MyPath

I get an error in the ChDrive My Path
and ChDir My Path

Any idea's whay that is ?
And how to rectify it.
Corey....

Regards

Corey McConnell