DevanG
06-13-2011, 12:09 PM
I know i shouldn't use name as my active workbook name, but besides that... it seems like it makes it to my copy and paste parts of the loops.. but nothing is getting pasted, and its running all the way to the closing of the workbooks. Where is the hole?
Private Sub cmdRunLog_Click()
Dim tpabook As Workbook
Dim bundledbook As Workbook
Dim dbbook As Workbook
Dim nqbook As Workbook
Dim scbook As Workbook
Dim Name As String
' cmdRunPM.Enabled = True
' cmdRolloverAndTransferred.Enabled = True
Name = ActiveWorkbook.Name
' set workbooks -- change wbook to real log when installing process
Set bundledbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copybundledlog.xls")
Set tpabook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copytpalog.xls")
Set dbbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copydblog.xls")
Set nqbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copynqlog.xls")
Set scbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copysmallcaselog.xls")
Dim i As Integer
Dim j As Integer
i = 1
j = 1
' start with checking bundled log, then move to the tpa, db, nq and small case, in that order to minimize time.
P2: Do While bundledbook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = bundledbook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value Then
bundledbook.Worksheets("Numeric Data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
bundledbook.Worksheets("Numeric Data").Range("D" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
bundledbook.Worksheets("additional data").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
Else
i = i + 1
End If
Loop
i = 1
Do While tpabook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = tpabook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value Then
tpabook.Worksheets("Numeric Data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
tpabook.Worksheets("Numeric Data").Range("D" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
tpabook.Worksheets("additional data").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
i = 1
Do While dbbook.Worksheets("DB Data").Range("AH" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = dbbook.Worksheets("DB Data").Range("AH" & (i + 1)).Value Then
dbbook.Worksheets("DB Data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
Workbooks(Name).Sheets(1).Range("M" & (i + 4)).Value = dbbook.Worksheets("DB Data").Range("Q" & (i + 1)).Value + dbbook.Worksheets("DB Data").Range("R" & (i + 1)).Value + dbbook.Worksheets("DB Data").Range("S" & (i + 1)).Value
dbbook.Worksheets("additional data").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
i = 1
Do While nqbook.Worksheets("input data").Range("B" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = nqbook.Worksheets("input data").Range("B" & (i + 1)).Value Then
nqbook.Worksheets("input data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
nqbook.Worksheets("input data").Range("E" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
nqbook.Worksheets("input data").Range("AD" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
i = 1
Do While scbook.Worksheets("log").Range("A" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = scbook.Worksheets("log").Range("A" & (i + 1)).Value Then
scbook.Worksheets("log").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
scbook.Worksheets("log").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
scbook.Worksheets("log").Range("BN" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
P1: bundledbook.Close False
tpabook.Close False
dbbook.Close False
nqbook.Close False
scbook.Close False
MsgBox "If any of the priced required revenues are 0 or "", run the pricing models. If not, you can skip to the Rollover and Transferred Assets."
End Sub
Private Sub cmdRunLog_Click()
Dim tpabook As Workbook
Dim bundledbook As Workbook
Dim dbbook As Workbook
Dim nqbook As Workbook
Dim scbook As Workbook
Dim Name As String
' cmdRunPM.Enabled = True
' cmdRolloverAndTransferred.Enabled = True
Name = ActiveWorkbook.Name
' set workbooks -- change wbook to real log when installing process
Set bundledbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copybundledlog.xls")
Set tpabook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copytpalog.xls")
Set dbbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copydblog.xls")
Set nqbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copynqlog.xls")
Set scbook = Application.Workbooks.Open("C:\Documents and Settings\mm27236\Desktop\T folder\logs\copysmallcaselog.xls")
Dim i As Integer
Dim j As Integer
i = 1
j = 1
' start with checking bundled log, then move to the tpa, db, nq and small case, in that order to minimize time.
P2: Do While bundledbook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = bundledbook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value Then
bundledbook.Worksheets("Numeric Data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
bundledbook.Worksheets("Numeric Data").Range("D" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
bundledbook.Worksheets("additional data").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
Else
i = i + 1
End If
Loop
i = 1
Do While tpabook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = tpabook.Worksheets("Numeric Data").Range("AC" & (i + 1)).Value Then
tpabook.Worksheets("Numeric Data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
tpabook.Worksheets("Numeric Data").Range("D" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
tpabook.Worksheets("additional data").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
i = 1
Do While dbbook.Worksheets("DB Data").Range("AH" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = dbbook.Worksheets("DB Data").Range("AH" & (i + 1)).Value Then
dbbook.Worksheets("DB Data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
Workbooks(Name).Sheets(1).Range("M" & (i + 4)).Value = dbbook.Worksheets("DB Data").Range("Q" & (i + 1)).Value + dbbook.Worksheets("DB Data").Range("R" & (i + 1)).Value + dbbook.Worksheets("DB Data").Range("S" & (i + 1)).Value
dbbook.Worksheets("additional data").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
i = 1
Do While nqbook.Worksheets("input data").Range("B" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = nqbook.Worksheets("input data").Range("B" & (i + 1)).Value Then
nqbook.Worksheets("input data").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
nqbook.Worksheets("input data").Range("E" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
nqbook.Worksheets("input data").Range("AD" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
i = 1
Do While scbook.Worksheets("log").Range("A" & (i + 1)).Value <> 0
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)).Value = scbook.Worksheets("log").Range("A" & (i + 1)).Value Then
scbook.Worksheets("log").Range("G" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("I" & (i + 4))
scbook.Worksheets("log").Range("F" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("M" & (i + 4))
scbook.Worksheets("log").Range("BN" & (i + 1)).Copy Workbooks(Name).Sheets(1).Range("S" & (i + 4))
j = j + 1 'move to next audit plan
If Workbooks(Name).Sheets(1).Range("A" & (j + 4)) = "" Then
GoTo P1
End If
i = 1 'set back to beginning of log
GoTo P2
Else
i = i + 1
End If
Loop
P1: bundledbook.Close False
tpabook.Close False
dbbook.Close False
nqbook.Close False
scbook.Close False
MsgBox "If any of the priced required revenues are 0 or "", run the pricing models. If not, you can skip to the Rollover and Transferred Assets."
End Sub