CT Witter
MrExcel MVP
- Joined
- Jul 7, 2002
- Messages
- 1,212
Below is my code. I get an error when the code tries to do it's second loop and open the second book. (marked here by ********ERROR********************************) The error I receive is Run Time error '9', Subscript out of range.
Thanks!!
CODE:
Sub Autpen()
Dim var1
Dim counter, Int2 As Integer
Dim var2, var3 As String
Int2 = 1
'Prepare Excel for this procedure:
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMaximized
'Dialogue box to ask user if they are ready to run today's Consignment Report:
Range("A1").Select
Msg = "Click YES to Format and Update with New Data," & vbCrLf & "NO to Cancel and view old Data!"
Style = 4 + 32
Title = "Welcome to the ''Consignment Report'' !!"
Response = MsgBox(Msg, Style, Title)
If Response = 6 Then
ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Selection.ClearContents
Range("A1").Select
'IMPORT NEW DATA FROM Consignment FILE
'Prepare Excel for the import:
Application.EnableEvents = False
Application.DisplayAlerts = False
counter = InputBox("Please Enter Number of Consignment Sheets!", "Number of Sheets?!")
Do Until Int2 > counter
'FILE IMPORT From the Capac.xls
'First, see if you have the file open; if not then open it:
var4 = "Consignment" & Int2
On Error GoTo b:
********ERROR********************************
Windows(var4).Activate
********ERROR********************************
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:Consignment"
Workbooks.Open FileName:="C:Consignment" & var4
'This will open the file
c:
'Now when we get to c: the file is open either way so we activate it:
Windows(var4).Activate
'Then select the Capacity Report worksheet...
Sheets(var4).Select
'then prepare the sheet:
ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
'...then select the entire sheet...
Columns("D:D").Select
Selection.Find(What:="2PT1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select
'...and then copy the entire sheet's range onto the clipboard.
Selection.Copy
'Next, re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate
'Then select the destination worksheet...
Sheets("Sheet1").Select
'...then select the entire sheet's range to copy...
'Cells.Select
'...and PSV from Capac.xls's Capacity Report sheet
'into CapacityReport.xls's CapacityReport sheet
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
'Close Capac file because we don't need it anymore.
Windows(var4).Activate
ActiveWindow.Close
'Now the CapacityReport file has today's data.
'Re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate
Call Processor
Int2 = Int2 + 1
var4 = " "
Loop
'Now save
ActiveWorkbook.Save
'Inform the user that the macro is complete.
MsgBox prompt:="Report is complete.", _
Title:="Capacity Report Finished.", _
Buttons:=64
'Now, if the user way back when clicked No to the original question
'about if they want to run the Report macro, here's
'what happens to them:
Else
MsgBox prompt:=("Click ''OK'' to enter the file." & vbCrLf & "(Note: This is the data from the last time your ran this report." & vbCrLf & "To update, Re-run and select YES!)"), _
Title:="You Have Selected No!!", _
Buttons:=vbExclamation
End If
Range("A1").Activate
ActiveCell.End(xlToLeft).Select
ActiveWindow.ScrollColumn = 1
'Reset Excel back to the way it was before it was open:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Sub ActivateNextBlankDown()
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Sub Processor()
'Return to Beginning and Process Phase1
Range("A1").Select
Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
Columns("F:H").Select
Selection.Delete Shift:=xlToLeft
Range("C1").Select
var2 = ActiveCell.Value
Range("E1").Select
var3 = ActiveCell.Value
Columns("A:A").Select
Selection.Find(What:="2*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select
Selection.Delete Shift:=xlToTop
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.Value = var2
Range("B1").Select
ActiveCell.Value = var3
Range("A1").Activate
Call ActivateNextBlankDown
ActiveCell.Offset(2, 0).Select
End Sub
Thanks!!
CODE:
Sub Autpen()
Dim var1
Dim counter, Int2 As Integer
Dim var2, var3 As String
Int2 = 1
'Prepare Excel for this procedure:
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMaximized
'Dialogue box to ask user if they are ready to run today's Consignment Report:
Range("A1").Select
Msg = "Click YES to Format and Update with New Data," & vbCrLf & "NO to Cancel and view old Data!"
Style = 4 + 32
Title = "Welcome to the ''Consignment Report'' !!"
Response = MsgBox(Msg, Style, Title)
If Response = 6 Then
ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Selection.ClearContents
Range("A1").Select
'IMPORT NEW DATA FROM Consignment FILE
'Prepare Excel for the import:
Application.EnableEvents = False
Application.DisplayAlerts = False
counter = InputBox("Please Enter Number of Consignment Sheets!", "Number of Sheets?!")
Do Until Int2 > counter
'FILE IMPORT From the Capac.xls
'First, see if you have the file open; if not then open it:
var4 = "Consignment" & Int2
On Error GoTo b:
********ERROR********************************
Windows(var4).Activate
********ERROR********************************
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:Consignment"
Workbooks.Open FileName:="C:Consignment" & var4
'This will open the file
c:
'Now when we get to c: the file is open either way so we activate it:
Windows(var4).Activate
'Then select the Capacity Report worksheet...
Sheets(var4).Select
'then prepare the sheet:
ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
'...then select the entire sheet...
Columns("D:D").Select
Selection.Find(What:="2PT1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select
'...and then copy the entire sheet's range onto the clipboard.
Selection.Copy
'Next, re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate
'Then select the destination worksheet...
Sheets("Sheet1").Select
'...then select the entire sheet's range to copy...
'Cells.Select
'...and PSV from Capac.xls's Capacity Report sheet
'into CapacityReport.xls's CapacityReport sheet
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
'Close Capac file because we don't need it anymore.
Windows(var4).Activate
ActiveWindow.Close
'Now the CapacityReport file has today's data.
'Re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate
Call Processor
Int2 = Int2 + 1
var4 = " "
Loop
'Now save
ActiveWorkbook.Save
'Inform the user that the macro is complete.
MsgBox prompt:="Report is complete.", _
Title:="Capacity Report Finished.", _
Buttons:=64
'Now, if the user way back when clicked No to the original question
'about if they want to run the Report macro, here's
'what happens to them:
Else
MsgBox prompt:=("Click ''OK'' to enter the file." & vbCrLf & "(Note: This is the data from the last time your ran this report." & vbCrLf & "To update, Re-run and select YES!)"), _
Title:="You Have Selected No!!", _
Buttons:=vbExclamation
End If
Range("A1").Activate
ActiveCell.End(xlToLeft).Select
ActiveWindow.ScrollColumn = 1
'Reset Excel back to the way it was before it was open:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Sub ActivateNextBlankDown()
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Sub Processor()
'Return to Beginning and Process Phase1
Range("A1").Select
Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
Columns("F:H").Select
Selection.Delete Shift:=xlToLeft
Range("C1").Select
var2 = ActiveCell.Value
Range("E1").Select
var3 = ActiveCell.Value
Columns("A:A").Select
Selection.Find(What:="2*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select
Selection.Delete Shift:=xlToTop
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.Value = var2
Range("B1").Select
ActiveCell.Value = var3
Range("A1").Activate
Call ActivateNextBlankDown
ActiveCell.Offset(2, 0).Select
End Sub