Deleting Leading Characters Across Multiple Sheets

[slpswhite]

I have a need to delete leading characters (5) from the same column (D) across multiple worksheets. I can get it to work on a single spreadsheet using the following code:

Sub RemoveCORP()
Range("A1").Value.Activate
For Each Cell In Range("D2", Range("D1000").End(xlUp))
If Not IsEmpty(Cell) Then
Cell.Value = Right(Cell, Len(Cell) - 5)
End If
Next Cell
End Sub

Every time I add a workbook this doesn't want to run. In this case I need to remove my worksheet names are John, Ralph, Mary and Virginia.

I have set the sheets and tried to enter a sheet name before the line "For Each" to see if it would work.

Any ideas?

 

[Joe4]

Try this:

Sub RemoveCORP()

    Dim ws As Worksheet
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        ws.Activate
        For Each cell In Range("D2", Range("D1000").End(xlUp))
            If Not IsEmpty(cell) Then
                cell.Value = Right(cell, Len(cell) - 5)
            End If
        Next cell
    Next ws
    
    Application.ScreenUpdating = True
        
End Sub

 

[slpswhite]

Thanks, I will give it a try, Since I have multiple worksheets is there a way to start it on one and end after another?. In this case I have Raw Data which I don't want to change, but once I have copied the data over to the new named worksheets (The four names) I want to stop it there. I don't need it to run on worksheets after these four.

 

[Joe4]

How many sheets do you have in all?

There are a few ways of explicitly excluding certain sheets:
- by name (if there are not that many and are always named the same way)
- by location within the workbook (the first sheet has an index of 1, the second 2, etc)

If you can tell us how the workbook is typically laid out, and the pattern, we can amend the code for you.

 

[slpswhite]

So the worksheets when I am done will be as follows:

Raw Data SP (Sheet 1)
Vendor 1
Vendor 2
Vendor 3
Vendor 4
Vendor 5
Vendor 6

The number of vendors could grow to 15 (right now there are 6)

From these 6-15 worksheets I will be parsing data to other worksheets which come after these.

Does this help?

 

[Joe4]

So are you saying that you want it to run on every sheet except the first one?

Sub RemoveCORP()

    Dim ws As Worksheet
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        If ws.Index > 1 Then
            ws.Activate
            For Each cell In Range("D2", Range("D1000").End(xlUp))
                If Not IsEmpty(cell) Then
                    cell.Value = Right(cell, Len(cell) - 5)
                End If
            Next cell
        End If
    Next ws
    
    Application.ScreenUpdating = True
        
End Sub

 

[slpswhite]

I don't want it to run on the first correct, But I do want it to run on the next 6-15 (dependent on the number of vendors) but not on any sheets after the vendors. I can move the vendor sheets to the back end of the workbook if needed.

 

[Joe4]

You need to come up with some sort of rule that we can program against - or we need some way to differentiate the ones to run against versus the ones not to.
- Is there a defined number of sheets (that doesn't change) NOT to run against?
- Is there any sort of name pattern to either the ones to run against (or ones not to run against)?
- Or is there some way we can identify where to stop?

 

[slpswhite]

Maybe the best thing would be to move the sheets after the vendor to another workbook. I can certainly do this and pull what I need into it. This way I could let it run on everything after the first sheet. I can use the last code you sent. This might be the easiest thing to do. Thoughts?

 

[Joe4]

Whatever makes the most sense for you.

Note that programming it to only run against certain sheets is not difficult, provided you can define a clear rule for determining that those sheets are.
Think of it this way. If you were instructing someone to do this manually, how would you describe to them a method for determining which sheets they would need to do it to? (Maybe the title on column D is the giveaway?)
If you can define that using a rule that always works, we can probably incorporate that same logic into the code.