I have one workbook that compiles data from several other workbooks. Each of
those workbooks have different names (obviously). The names are based on
three factors: 1) a county code that identifies the organization submitting
the file; 2) the month; and 3) the year.

I need my code to switch to the workbook (which would be open) to copy
values to be pasted in the active workbook. What I want the code to do is
verify that the workbook that it is switching to has a valid (reasonable)
name in case more than these two workbooks are open. Thus, I want it to make
sure that the file name contains the month in it. The month is stored in the
cell to the left of the active cell (but it is numeric and the file name
contains the month in text). This is why I'm trying to use the offset
function.

I'm just not getting my code to correctly identify the file name and it
endlessly loops. This is most likely because I'm not getting stMonth to
populate with the correct value. If the cell to the left of the active cell
is 07, I should be getting stMonth to equal July. Then the code should
continue to switch active windows until the file name contains July and is an
Excel document.

I hope I described this well enough. This is my first time using VB for
Excel. I'm getting fairly good at using it in Access, but am very much a n00b
here.


Sub mcrDataCollection()

Dim stActiveCell As String
Dim stMonth As String

stActiveCell = ActiveCell.Address
stMonth = Format(Range(stActiveCell).Offset(0, -1).Value, "mmmm")

ActiveWindow.ActivateNext

Do Until UCase(ActiveWindow.Caption) Like UCase("*" & stMonth & "*" &
".xls") = True

If UCase(ActiveWindow.Caption) Like UCase("*" & stMonth & "*" & ".xls")
= False Then
ActiveWindow.ActivateNext
End If

Loop

Sheets("Sheet1").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("08-09 verif stats.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("08-09 stat form-MACRO Test Version.xls").Activate
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("08-09 verif stats.xls").Activate
Sheets("Sheet2").Select
Range(stActiveCell).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

--

HTH

Don''''t forget to rate the post if it was helpful!

Please reply to newsgroup only, so that others may benefit as well.

"This is most likely because I'm not getting stMonth to populate with the correct value."

Try...
stMonth = Format$(ActiveCell.Offset(0, -1).Value * 29, "mmmm")
--
Jim Cone
Portland, Oregon USA

Jim,

Thanks for the response. I'm not sure what * 29 is doing in the code. Could
you please explain its purpose?

BTW... I used to live in Portland and miss it very much. You lucky dog.
--

HTH

Don''''t forget to rate the post if it was helpful!

Please reply to newsgroup only, so that others may benefit as well.


"Jim Cone" wrote:


"This is most likely because I'm not getting stMonth to populate with the correct value."

Try...
stMonth = Format$(ActiveCell.Offset(0, -1).Value * 29, "mmmm")
--
Jim Cone
Portland, Oregon USA

The Format function, for dates/time, requires a complete date based
on the long date international setting of Windows...

7 returns 1/6/1900 (January)
Multiplying by 29 = 203 which returns 7/21/1900 (July)
--
Jim Cone
Portland, Oregon USA


"Maverick"
wrote in message
Jim,
Thanks for the response. I'm not sure what * 29 is doing in the code. Could
you please explain its purpose?
BTW... I used to live in Portland and miss it very much. You lucky dog.
--



"Jim Cone" wrote:
"This is most likely because I'm not getting stMonth to populate with the correct value."
Try...
stMonth = Format$(ActiveCell.Offset(0, -1).Value * 29, "mmmm")
--
Jim Cone
Portland, Oregon USA

Okay. I'm following you. Would this be viable for any month though? If the
month were 8, would multiplying by 29 get me August?
--

HTH

Don''''t forget to rate the post if it was helpful!

Please reply to newsgroup only, so that others may benefit as well.


"Jim Cone" wrote:

The Format function, for dates/time, requires a complete date based
on the long date international setting of Windows...

7 returns 1/6/1900 (January)
Multiplying by 29 = 203 which returns 7/21/1900 (July)
--
Jim Cone
Portland, Oregon USA


"Maverick"
wrote in message
Jim,
Thanks for the response. I'm not sure what * 29 is doing in the code. Could
you please explain its purpose?
BTW... I used to live in Portland and miss it very much. You lucky dog.
--



"Jim Cone" wrote:
"This is most likely because I'm not getting stMonth to populate with the correct value."
Try...
stMonth = Format$(ActiveCell.Offset(0, -1).Value * 29, "mmmm")
--
Jim Cone
Portland, Oregon USA

Try it... "Msgbox stMonth" will display the value of stMonth.

Cloudy and expected high of 60 in Portland today.
--
Jim Cone
Portland, Oregon USA


"Maverick"
wrote in message
Okay. I'm following you. Would this be viable for any month though? If the
month were 8, would multiplying by 29 get me August?
--


"Jim Cone" wrote:
The Format function, for dates/time, requires a complete date based
on the long date international setting of Windows...

7 returns 1/6/1900 (January)
Multiplying by 29 = 203 which returns 7/21/1900 (July)
--
Jim Cone
Portland, Oregon USA