macro to tidy all sheets by seletcing a1

[tonywatsonhelp]

Hi Everyone,

I have a number of macros that copy paste change etc and the problem im getting is it leaves the page looking messy for example it might copy and paste data so the copy field is sel;ected
so when you then go to this sheet it looks untidy.

I've tried a few ideas but it has become aparent that what i need is a macro to do the tidy.

Its realy quite simple if it was in english it would say this,

for every sheet in this document, if its visible then range A1.select

can someone help me with the code please?

Thanks
Tony

 

[njimack]

You don't need to select cells to perform actions on them, so the activecell will remain unchanged. Example:

Range(A1").Copy Range ("B1")

 

[tonywatsonhelp]

Thanks njimack,

maybe you could give me a more exact example by showing me how I can do it with these two examples?

Sub Filter_Rawdata_?NDate()
Application.ScreenUpdating = False
Dat1 = Sheets("Control").Range("B2")
Lrow1 = Sheets("RawData2").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("RawData2").Range("A1", "K" & Lrow1).AutoFilter Field:=7, Criteria1:="1"
Sheets("RawData2").Range("A1", "K" & Lrow1).AutoFilter Field:=10, Criteria1:=">=" & Dat1
Sheets("RawData2").Range("A2", "K" & Lrow1).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sorted Data?").Range("A2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("RawData").Range("A1", "H1").AutoFilter
Sheets("RawData2").Range("A1", "K1").AutoFilter
End Sub

Sub SortedData_AApart1()
Application.ScreenUpdating = False
Lrow5 = Sheets("Sorted Data?").Cells(Rows.Count, "A").End(xlUp).Row
If Lrow5 < 2 Then
Lrow5 = 2
End If
Sheets("Sorted Data?").Range("A2", "E" & Lrow5).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sorted Data?").Range("AA2").PasteSpecial xlPasteValues
Lrow6 = Sheets("Sorted Data?").Cells(Rows.Count, "AA").End(xlUp).Row
If Lrow6 < 2 Then
Lrow6 = 2
End If
Sheets("Sorted Data?").Range("$AA$1:$AE$" & Lrow6).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[njimack]

I can't see anything in your code that selects cells, so it shouldn't be behaving as you've described.

To force cell A1 to be selected in each sheet, try this:

Sub Select_A1()
Dim ws As Worksheet
Dim ThisSheet As Worksheet
Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet
For Each ws In ThisWorkbook.Worksheets
    With ws
        If .Cells(1, 1).EntireRow.Hidden = False And .Cells(1, 1).EntireColumn.Hidden = False Then
            .Select
            .Cells(1, 1).Select
        End If
    End With
Next ws
ThisSheet.Select
Application.ScreenUpdating = True
End Sub

 

[mumps]

Maybe:

Sub SelectA1()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Visible <> xlHidden Then
            ws.Activate
            ws.Range("A1").Select
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Thanks Mjimack and mops,
both ideas work great and are exactly what I needed.

Mjimack, appreciate your help in look at my code I often wonder why this happens but this will solve it as its only cosmetic

Thanks

Tony