If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|