VBA help needed urgently

[asad]

Hi Guys,

I have recorded the following macro

Sub Macro1()    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C1").Select
    Range("$A$1:$C$500").AutoFilter Field:=3, Criteria1:="="
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("$A$1:$C$449").AutoFilter Field:=3
    Range("D2").Select
End Sub

Can I please get this to work on all the sheets in the workbook?

Thanks for your help
Asad

 

[asad]

Thanks for that Mark.

One more favour, if may please.
Can I say in code - all sheets except for Master and Info?

 

[MARK858]

Thanks for that Mark.

One more favour, if may please.
Can I say in code - all sheets except for Master and Info?

Sub Macro2()
    Dim ws As Worksheet
    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        [COLOR="#FF0000"]If ws.Name <> "Master" Or ws.Name <> "Info" Then[/COLOR]
            On Error Resume Next
            ws.Range("C3:C" & ws.Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12).EntireRow.Delete
            On Error GoTo 0
        [COLOR="#FF0000"]End If[/COLOR]
    Next

    Application.ScreenUpdating = True
End Sub

 

[asad]

Hi Michael and Mark,

I have been testing all the different codes given by both of you. I am not getting any of those to do exactly what is intended.

[COLOR=#333333]Sub Macro1()[/COLOR]Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
    With Cells
    .Value = .Value
    End With
    Range("C1").Select
    Range("$A$1:$C$500").AutoFilter Field:=3, Criteria1:="="
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("$A$1:$C$449").AutoFilter Field:=3
    Range("D2").Select
Next ws [COLOR=#333333]End Sub[/COLOR]

This one does not replace formulas with values, and .Value = .Value gets highlighted.

[COLOR=#574123]Sub Macro2()[/COLOR]    Dim ws As Worksheet
    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        [COLOR=#FF0000]If ws.Name <> "Master" Or ws.Name <> "Info" Then[/COLOR]
            On Error Resume Next
            ws.Range("C3:C" & ws.Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12).EntireRow.Delete
            On Error GoTo 0
        [COLOR=#FF0000]End If[/COLOR]
    Next

    Application.ScreenUpdating = True [COLOR=#574123]End Sub[/COLOR]

This one is deleting all the rows in range, even the ones that have data.

My spreadsheet is set up so that formula in column B is looking at column C, and if blank then returns the value from next row in column A.
Then there are formulas in columns D, E, F and G as well. All of them look at Column C and return the values accordingly. Now I want all of these formulas in columns B, D, E, F, and G to be changed to values so that I do not lose the results. And then, I want to delete the rows that have blank cells in column C.

Thanks for your help.
Asad

 

[Michael M]

Try

Sub MM1()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name <> "Master" Or ws.Name <> "Info" Then
        ws.Activate
            With ws.UsedRange
                .Value = .Value
            End With
            With Range("C1", Cells(Rows.Count, "C").End(xlUp))
                .Replace "", "#N/A", xlWhole, , False, , False, False
            Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
        End With
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[asad]

Try

Sub MM1()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name <> "Master" Or ws.Name <> "Info" Then
        ws.Activate
            With ws.UsedRange
                .Value = .Value
            End With
            With Range("C1", Cells(Rows.Count, "C").End(xlUp))
                .Replace "", "#N/A", xlWhole, , False, , False, False
[COLOR=#ff0000]            Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete[/COLOR]
        End With
    End If
Next ws
Application.ScreenUpdating = True
End Sub

Hi Michael,

This also resulted in an error. The line with the red fonts above gets highlighted and the formulas are still there.

Sorry for all this. But if you can get this to work, then that will be a big help.

Thanks
Asad

 

[asad]

Hi Michael,

I have changed your macro a little bit and now it looks like this

Sub Data()    Dim ws As Worksheet
    Application.ScreenUpdating = False


    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Or ws.Name <> "Info"  Then
        ws.Activate
            With ws
                ws.Range("A1:F500").Value = ws.Range("A1:F500").Value
                Range("C1").Select
                Range("$A$1:$C$500").AutoFilter Field:=3, Criteria1:="="
                Rows("3:3").Select
                Range(Selection, Selection.End(xlDown)).Select
                Application.CutCopyMode = False
                Selection.Delete Shift:=xlUp
                Range("$A$1:$C$449").AutoFilter Field:=3
                Range("D2").Select
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

And it worked.
But I am sure, you can make it better. I will wait for your solution before I do anything to all the files.

Thanks
Asad

 

[Michael M]

If you step through my code...the lines

With ws.UsedRange
 .Value = .Value
End With

will remove all formulas and replace them with the values....and it works fine for me, remembering that Master and Info sheets will be unaffected

When that is done are the cells to be removed in col "C" blank or Zeros

If you are still having issues please upload a copy of the worksheet in question to Dropbox or similar and I'll look into it further

 

[asad]

If you step through my code...the lines

With ws.UsedRange
 .Value = .Value
End With

will remove all formulas and replace them with the values....and it works fine for me, remembering that Master and Info sheets will be unaffected

When that is done are the cells to be removed in col "C" blank or Zeros

If you are still having issues please upload a copy of the worksheet in question to Dropbox or similar and I'll look into it further

The cells in column C are blanks. No zeros.

 

[Michael M]

In that cese the code in Post #14 should work fine...it does for me !!
Upload to dropbox if you have any further problems

 

[asad]

Thanks Michael

Got it to work now.

I had a corrupt tab in the file. I deleted that and it works fine now. Sorry about the unnecessary botheration.

Thanks for your help.

Regards
Asad