If someone would help me out I would appreciate it. In the code below, I am copying information from a master sheet to various sub-sheets, but I cant figure out how, based on the way I have written my code, to copy and paste special values.
I commented out the line that I need pasted special values.
Thanks for any help.
Code
Dim intData As Integer
Set wsData = Worksheets("Data")
LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add
wsData.Range("E3:E" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("E3"), Unique:=True
LastRowCrit = wsCrit.Range("E" & Rows.Count).End(xlUp).Row
For I = 4 To LastRowCrit
Set wsNew = Worksheets.Add
wsNew.Name = wsCrit.Range("E" & I).Value
wsNew.Range("a3").Value = "Estimator"
wsNew.Range("a5").Value = "IP Number"
wsNew.Range("b5").Value = "Project Name"
wsNew.Range("c5").Value = "Due Date"
wsNew.Range("d5").Value = "Days Remaining"
Next I
For Each wsWS In Worksheets
For intData = 4 To LastRow
Set rngIP = wsWS.Range("A65536").End(xlUp).Offset(1, 0)
Set rngPN = wsWS.Range("b65536").End(xlUp).Offset(1, 0)
Set rngDD = wsWS.Range("c65536").End(xlUp).Offset(1, 0)
Set rngDR = wsWS.Range("d65536").End(xlUp).Offset(1, 0)
If wsWS.Name = wsData.Range("e" & intData).Value And wsWS.Name <> "Data" _
And wsData.Range("i" & intData).Value <> "COMPLETE" Then
wsData.Range("a" & intData).Copy rngIP
wsData.Range("b" & intData).Copy rngPN
wsData.Range("h" & intData).Copy rngDD
'wsData.Range("i" & intData).Copy rngDR 'the value this returns is based on a formula, I would like to only return the value.
wsWS.Range("b3").Value = wsWS.Name
End If
Next intData
Next wsWS
Application.DisplayAlerts = False
wsCrit.Delete
Application.DisplayAlerts = True
End Sub
Display More