Copy & Paste Values: Not working as I think it should (and another thing)

[hip2b2]

Below is the code I am using (I know, not very sophisticate, but the best I know how to do).

Two Problems
1- I would like to copy the values (not the formulas) AND formatting from sheet "Prod Runs" to "Sheet 2". As it stands I get the values but lose the formatting.

2- "Sheet 2", "Row L" has several rows with the word "Closed" (these are random, e.g., they could be anywhere in the "Column L". I would like to know how to delete the rows where "Closed" is found in "Column L"


Any and all suggestions will be gratefully received.

Sub ACCT_MGR()

    Sheets("Sheet2").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Sheets("Prod Runs").Select
    Columns("A:S").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Columns("A:T").EntireColumn.AutoFit
    Columns("I:O").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll ToRight:=-1
    Columns("B:B").ColumnWidth = 25
    Columns("D:D").ColumnWidth = 1.5
    Columns("I:I").ColumnWidth = 2.5
    Range("A1").Select
    Sheets("Prod Runs").Select
    Range("A8").Select
End Sub

 

[fhqwgads]

for your first question maybe try separate pasting values and number formats by replacing

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

with

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

 

[hip2b2]

for your first question maybe try separate pasting values and number formats by replacing

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

with

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

That worked perfectly except (I'm sure you hate the "excepts") that 2 rows have odd row heights (larger than required). I see nothing in those rows in the "copy from" sheet that would cause that.

Any fix you can suggest? Even if not thanks for your excellent fix.

hip

 

[fhqwgads]

not sure what could be causing it. maybe some text is getting wrapped or something
are your row heights all the same? you could maybe just add some row height code in after the paste special bit

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.RowHeight = 15

 

[hip2b2]

not sure what could be causing it. maybe some text is getting wrapped or something
are your row heights all the same? you could maybe just add some row height code in after the paste special bit

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.RowHeight = 15

Thanks, that fixed it; though I still have no idea of what was the cause of the formatting "quirks" (not that it matters at this point).

hip