Switch to diff workbook with file name LIKE based on value in a ce

M

Maverick

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.
 
J

Jim Cone

Re: "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")
 
M

Maverick

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.
 
J

Jim Cone

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.
 
M

Maverick

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 said:
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.
 
J

Jim Cone

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?
 
M

Maverick

I suppose I should have tested it before asking the question. LOL

That does the trick. Thanks a lot.

It's not the rainy season I miss... it's everything else. Besides, I live in
Pennsylvania now. Today is a really nice day, but over the last month our
average daily temp was 56 degrees. You don't know how good you have it until
it's gone.
--

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 said:
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?
 
J

Jim Cone

You are welcome.
(there is no place like home)
Jim Cone



"Maverick"
wrote in message
I suppose I should have tested it before asking the question. LOL
That does the trick. Thanks a lot.

It's not the rainy season I miss... it's everything else. Besides, I live in
Pennsylvania now. Today is a really nice day, but over the last month our
average daily temp was 56 degrees. You don't know how good you have it until
it's gone.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top