Prev: Subtract colums in pivot table
Next: Addition function of multiple cells
From: tr on 25 May 2010 13:57
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.
From: Gary''s Student on 25 May 2010 15:31
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.
From: tr on 25 May 2010 16:58
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.
 | 
Pages: 1
Prev: Subtract colums in pivot table
Next: Addition function of multiple cells