rngd
12-10-2015, 11:53 AM
Hi everyone,
Using some code I found online and changing it up a little I was almost able to make my code do what I needed. My problem come in the line:
Name "Z:\Site_MEA\MEA ECC Exclusions\MEA_ECC_EX "&wo&".doc" As "Z:\Site_MEA\MEA ECC Exclusions\Approved MEA_ECC EX\MEA_ECC_EX "&wo&".doc"
I added this line to move a file based on a WorkOrder number (wo) (taken from a cell in the first column) to another location. My code will works perfectly if I manually change out the "&wo&" for a real number like 1234 but if I use the code how it is now, it doesn't work. Note: the value wo will keep changing based on what row it is at. The big problem that I have is the other macro will stop working properly when I run this code, it will not show any errors but it does not populate the sheet from word docs like it is suppose to.
Below you can see my code for this macro. Am I maybe missing a line that I should put in or is there another easy way to move a file.
Any help would be great.
David
Sub Approve_Selected_ECC_Exclusion()
Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
Dim wo As String
Application.ScreenUpdating = False
lastrow = Worksheets("Open ECC_EX").UsedRange.Rows.Count
lastrow2 = Worksheets("Approved ECC_EX").UsedRange.Rows.Count
If lastrow2 = 1 Then lastrow2 = 0
For r = lastrow To 2 Step -1
If Range("I" & r).Value = "approved" Then
wo = Range("A" & r).Text
Name "Z:\Site_MEA\MEA ECC Exclusions\MEA_ECC_EX "&wo&".doc" As "Z:\Site_MEA\MEA ECC Exclusions\Approved MEA_ECC EX\MEA_ECC_EX "&wo&".doc"
Rows(r).Cut Destination:=Worksheets("Approved ECC_EX").Range("A" & lastrow2 + 1)
lastrow2 = lastrow2 + 1
Worksheets("Open ECC_EX").Rows(r).Delete
Else:
End If
Next r
Application.ScreenUpdating = True
End Sub
Using some code I found online and changing it up a little I was almost able to make my code do what I needed. My problem come in the line:
Name "Z:\Site_MEA\MEA ECC Exclusions\MEA_ECC_EX "&wo&".doc" As "Z:\Site_MEA\MEA ECC Exclusions\Approved MEA_ECC EX\MEA_ECC_EX "&wo&".doc"
I added this line to move a file based on a WorkOrder number (wo) (taken from a cell in the first column) to another location. My code will works perfectly if I manually change out the "&wo&" for a real number like 1234 but if I use the code how it is now, it doesn't work. Note: the value wo will keep changing based on what row it is at. The big problem that I have is the other macro will stop working properly when I run this code, it will not show any errors but it does not populate the sheet from word docs like it is suppose to.
Below you can see my code for this macro. Am I maybe missing a line that I should put in or is there another easy way to move a file.
Any help would be great.
David
Sub Approve_Selected_ECC_Exclusion()
Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
Dim wo As String
Application.ScreenUpdating = False
lastrow = Worksheets("Open ECC_EX").UsedRange.Rows.Count
lastrow2 = Worksheets("Approved ECC_EX").UsedRange.Rows.Count
If lastrow2 = 1 Then lastrow2 = 0
For r = lastrow To 2 Step -1
If Range("I" & r).Value = "approved" Then
wo = Range("A" & r).Text
Name "Z:\Site_MEA\MEA ECC Exclusions\MEA_ECC_EX "&wo&".doc" As "Z:\Site_MEA\MEA ECC Exclusions\Approved MEA_ECC EX\MEA_ECC_EX "&wo&".doc"
Rows(r).Cut Destination:=Worksheets("Approved ECC_EX").Range("A" & lastrow2 + 1)
lastrow2 = lastrow2 + 1
Worksheets("Open ECC_EX").Rows(r).Delete
Else:
End If
Next r
Application.ScreenUpdating = True
End Sub