Manipulate Specific Sheet Data With Macro - Not Activesheet Ask

Hi All,

I have posted this same question here:

Link: https://stackoverflow.com/questions/49551881/manipulate-specific-sheet-data-with-macro-not-activesheet

I have 10 sheets in a workbook - These sheets were imported from individual workbooks - These workbooks were extracts from different monitoring tools

I need to apply a filter across all 10 worksheets, however, not all the sheets are in the same format/structure.

With 6 of the worksheets, the column headers are the same and in the same order.

The remaining 4 sheets have different headers. For example: The filter needs to look for a header name Status - This works for the 6 sheets that have the same structure, however, the other 4 sheets have the following:

wsheet1:

User Status instead of Status - I need to change the header to Status

wsheet2:

Current_Status instead of Status - I need to change the header to Status

Below is sample code that is supposed to manipulate the specified sheet in in order to have it "look" the same as the others, however, I am having some really annoying issues where the code isn't applied to the sheet specified and is instead applied to the "Activesheet" when the macro is executed.

Here is the code I have:

Sub arrangeSheets()

    Dim lastCol As Long, idCount As Long, nameCount As Long, headerRow As Long

    Dim worksh As Integer, WS_Count As Integer, i As Integer, count As Integer

    Dim rng As Range, cel As Range, rngData As Range

    Dim worksheetexists As Boolean

            worksh = Application.Sheets.count
            worksheetexists = False

            headerRow = 1       'row number with headers
            lastCol = Cells(headerRow, Columns.count).End(xlToLeft).Column 'last column in header row
            idCount = 1
            nameCount = 1


            ' Set WS_Count equal to the number of worksheets in the active
            ' workbook.
            WS_Count = ActiveWorkbook.Worksheets.count

            'If Application.Match finds no match it will throw an error so we need to skip them
            On Error Resume Next

            For x = 1 To worksh

                If Worksheets(x).Name = "wsheet1" Then
                    worksheetexists = True

                    Set rng = Sheets("wsheet1").Range(Cells(headerRow, 1), Cells(headerRow, lastCol)) 'header range

                    With Worksheets("wsheet1").Name

                        Rows(2).Delete
                        Rows(1).Delete
                        count = Application.Match("*USER STATUS*", Worksheets("wsheet1").Range("A1:AZ1"), 0)

                        If Not IsError(count) Then
                            For Each cel In rng                     'loop through each cell in header
                                If cel = "*USER STATUS*" Then       'check if header is "Unit ID"

                                    cel = "STATUS" & idCount        'rename "Unit ID" using idCount
                                    idCount = idCount + 1           'increment idCount

                                End If
                            Next cel
                        End If

                    End With

            Exit For

                End If

            Next x
            End Sub

Open in new window

Eitel DagninIT Security AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Sub arrangeSheets()

    Dim lastCol As Long, idCount As Long, nameCount As Long, headerRow As Long

    Dim worksh As Integer, WS_Count As Integer, i As Integer, count As Integer

    Dim rng As Range, cel As Range, rngData As Range

    Dim worksheetexists As Boolean

        worksh = Application.Sheets.count
        worksheetexists = False

        headerRow = 1       'row number with headers
        lastCol = Cells(headerRow, Columns.count).End(xlToLeft).Column 'last column in header row
        idCount = 1
        nameCount = 1


        ' Set WS_Count equal to the number of worksheets in the active
        ' workbook.
        WS_Count = ActiveWorkbook.Worksheets.count

        'If Application.Match finds no match it will throw an error so we need to skip them
        On Error Resume Next

        For x = 1 To worksh
            Set sh = Sheets(x)
            sh.Activate
            With sh
                Set rng = sh.Range(Cells(headerRow, 1), Cells(headerRow, lastCol)) 'header range

                .Rows(2).Delete
                .Rows(1).Delete
                count = Application.Match("*USER STATUS*", .Range("A1:AZ1"), 0)

                If Not IsError(count) Then
                    For Each cel In rng                     'loop through each cell in header
                        If cel = "*USER STATUS*" Or cel = "Current_Status" Then        'check if header is "Unit ID"
                            cel = "STATUS" & idCount        'rename "Unit ID" using idCount
                            idCount = idCount + 1           'increment idCount
                        End If
                    Next cel
                End If

            End With

        Next x
End Sub

Open in new window

Regards
0
Fabrice LambertFabrice LambertCommented:
Columns.count
Cells(headerRow, Columns.count)
ActiveWorkbook
Sheets("wsheet1")
Writing such instructions is asking for troubles, as they implicitly refer to ActiveWorkbook or ActiveSheet.
So, start by using explicit objects.
0
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Rgonzo1971,

Apologies for taking so long to reply. I tried your code and unfortunately it didn't do anything..

However, I have managed to get the code I required and it is working great. Thank you for your time and reply! :)

Link: https://stackoverflow.com/questions/49551881/manipulate-specific-sheet-data-with-macro-not-activesheet/49672827#49672827

Sub ManipulateSheets()

    Dim worksh As Integer

    Dim worksheetexists As Boolean

    worksh = Application.Sheets.count
    worksheetexists = False

    'If Application.Match finds no match it will throw an error so we need to skip them
    On Error Resume Next

    Worksheets("wSheet1").Activate

    With Worksheets("wSheet1")

        .Rows(2).Delete
        .Rows(1).Delete
    End With

    Worksheets("wSheet2").Activate

    With Worksheets("wSheet2")

        .Rows(2).Delete

    End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fabrice LambertFabrice LambertCommented:
Well, this code have serious issues:

- It assumes that the workbook it is applyed on is the active workbook. Can you garantee it will always work ? No mater user interactions ?

Application.Sheets.count is implicitly Application.ActiveWorkbook.Sheets.count
Worksheets("wSheet1").Activate is implicitly ActiveWorkbook.Worksheets("wSheet1").Activate
What if the active workbook isn't the expected one ?
0
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Fabrice,

Thank you for your reply. Could you perhaps provide me with a suitable fix for that please?
0
Fabrice LambertFabrice LambertCommented:
Set a reference on the workbook the function is supposed to work with.

Most of the time, it is the workbook where the code is written.
Within Excel, VBA provide a global variable: ThisWorkbook.

In practice a safe code can looks like:
Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.worksheet
Set ws = wb.Worksheets(1)

Dim rng As Excel.Range
Set rng = ws.Range("A1")

rng.value = "myValue"

Open in new window

Or a shorter version using the With statement:
With ThisWorkbook.Worksheets(1)
    Dim rng As Excel.Range
    Set rng = .Range("A1")
    rng.Value= "myValue"
End With

Open in new window

0
Eitel DagninIT Security AdministratorAuthor Commented:
Activating the specific sheet to be used.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.