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