Macro to Cut and paste Values
 

I have a workbook with many lookups to external workbooks via function.
INDIERCT.EXT This works fine.....but is very slow; as it cycles every
associated file everytime I open my workbook (there could be 1000's).

In Column P (titled) Complete? A "Y" or "N" indicates status of any given
ROW of data. If the status is "Y" then I can eliminate the Formulas and
Hopefully significantly increase my speed of the workbook.

So what I am looking for is a Macro that will Scan the workseet and
whereever it finds a "Y" in Column P...it will COPY and PASTE SPECIAL VALUES
(for that row, columns A-U, I would like to keep whatever formulas that are
after Column U active).

My worksheet name is "ACTIVE ECO's" if that helps.

Macro to Cut and paste Values
 

Try this small macro:

Sub noformulas()
Dim i As Long, n As Long
n = Cells(Rows.Count, "P").End(xlUp).Row
For i = 1 To n
If Cells(i, "P").Value = "Y" Then
Set r = Range("A" & i & ":U" & i)
r.Copy
r.PasteSpecial Paste:=xlPasteValues
End If
Next
End Sub

--
Gary''s Student - gsnu201003


"tr" wrote:

I have a workbook with many lookups to external workbooks via function.
INDIERCT.EXT This works fine.....but is very slow; as it cycles every
associated file everytime I open my workbook (there could be 1000's).

In Column P (titled) Complete? A "Y" or "N" indicates status of any given
ROW of data. If the status is "Y" then I can eliminate the Formulas and
Hopefully significantly increase my speed of the workbook.

So what I am looking for is a Macro that will Scan the workseet and
whereever it finds a "Y" in Column P...it will COPY and PASTE SPECIAL VALUES
(for that row, columns A-U, I would like to keep whatever formulas that are
after Column U active).

My worksheet name is "ACTIVE ECO's" if that helps.

Macro to Cut and paste Values
 

Thanks worked Great

"Gary''s Student" wrote:

Try this small macro:

Sub noformulas()
Dim i As Long, n As Long
n = Cells(Rows.Count, "P").End(xlUp).Row
For i = 1 To n
If Cells(i, "P").Value = "Y" Then
Set r = Range("A" & i & ":U" & i)
r.Copy
r.PasteSpecial Paste:=xlPasteValues
End If
Next
End Sub

--
Gary''s Student - gsnu201003


"tr" wrote:

I have a workbook with many lookups to external workbooks via function.
INDIERCT.EXT This works fine.....but is very slow; as it cycles every
associated file everytime I open my workbook (there could be 1000's).

In Column P (titled) Complete? A "Y" or "N" indicates status of any given
ROW of data. If the status is "Y" then I can eliminate the Formulas and
Hopefully significantly increase my speed of the workbook.

So what I am looking for is a Macro that will Scan the workseet and
whereever it finds a "Y" in Column P...it will COPY and PASTE SPECIAL VALUES
(for that row, columns A-U, I would like to keep whatever formulas that are
after Column U active).

My worksheet name is "ACTIVE ECO's" if that helps.