Paddy1979
Well-known Member
- Joined
- Sep 23, 2005
- Messages
- 608
Hello all, please can advise me on this problem.
My spreadsheet is designed to gather batches of information onto a summary sheet.
On my spreadsheet I have a userform with 2 options that allows the user to either
A. refresh productivity weekly figures
&
B. refresh period end figures
If A (productivity) is run before B (period end) , B will not work.
However if i close the spreadsheet and reopen and retry option B it then runs perfectly , so its seems A is impacting on B somehow.
A and B are both written modules shown below, can you see why B bugs out on the below code line only if A is run first?
Many thanks
Paddy
A.Productivity code
B. Period Reports, (not all code , just to debug point)
My spreadsheet is designed to gather batches of information onto a summary sheet.
On my spreadsheet I have a userform with 2 options that allows the user to either
A. refresh productivity weekly figures
&
B. refresh period end figures
If A (productivity) is run before B (period end) , B will not work.
However if i close the spreadsheet and reopen and retry option B it then runs perfectly , so its seems A is impacting on B somehow.
A and B are both written modules shown below, can you see why B bugs out on the below code line only if A is run first?
Code:
S1 = .Find(What:=PR, After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Column - 1
Many thanks
Paddy
A.Productivity code
Code:
Sub Productivity()
Dim PROD As Worksheet
Dim rfile As Variant
Dim dnumber As Variant
Dim X As Long
Set PROD = Sheet4
Sheet4.Unprotect Password:="Pass"
X = Sheet4.Range("C65334").End(xlUp).Row + 1
MsgBox "Please open the weekly Productivity INNS035 report"
rfile = Application.GetOpenFilename("Microsoft Excel Workbooks,*.xls")
If rfile = False Then Exit Sub
Workbooks.Open Filename:=rfile
Sheets("Technician Detail").Select
Set Data1 = ActiveWorkbook
here1:
dnumber = Application.InputBox("Please input the Week number the INNS035 data reflects", "Week Number", "")
If dnumber = False Then Exit Sub
If Left(dnumber, 1) <> "W" Then
MsgBox " With Prefix 'W'"
GoTo here1
End If
Set c = PROD.Columns(3).Find(dnumber, LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
MsgBox "Aborted ! Data is already present for this Week", vbCritical
Exit Sub
Else
End If
With Columns("B")
R = .Find(What:="Steven Lee", After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Row
End With
Range("B" & R & ":AS" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
With PROD
.Activate
.Range("D" & X).PasteSpecial xlPasteAll
End With
With PROD.Range("A" & X & ":AS" & Cells(Rows.Count, "D").End(xlUp).Row)
.Columns(3).Value = dnumber
.Columns(1).FormulaR1C1 = "=VLOOKUP(RC[2],wk_p,3,0)"
.Columns(2).FormulaR1C1 = "=VLOOKUP(RC[1],wk_p,2,0)"
.Columns(45).FormulaR1C1 = "=SUM(RC[-11]:RC[-9],RC[-7]:RC[-5])+SUM(RC[-8],RC[-3],RC[-2])/2"
.Copy
.PasteSpecial xlPasteValues
End With
Data1.Activate
ActiveWorkbook.Close SaveChanges:=False
Sheet4.Protect Password:="Pass"
Sheet3.Select
End Sub
B. Period Reports, (not all code , just to debug point)
Code:
Sub periodend()
Dim PR As Variant
Dim lr As Long, i As Long
Dim S1 As Integer, p1 As Integer, c1 As Integer, n1 As Integer
Dim cell As Range
Dim home As Workbook
Set home = ActiveWorkbook
Application.Calculation = xlCalculationManual
here:
PR = Application.InputBox("Please input the Period number the data reflects (with Capital P)", "Period Number", "")
If PR = False Then Exit Sub
If Left(PR, 1) <> "P" Then
MsgBox " With Prefix 'P'"
GoTo here
End If
'set start points for data entry ''
With Sheet5.Rows("1:1")
S1 = .Find(What:=PR, After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Column - 1
End With