Reformatting data on MANY sheets

[knpaddac]

My Excel document has approximately 1100 worksheets.
Each has a varying number (some have 2 or 3, some have 110) of rows of data.
Each row looks something like this (I have used commas to express columns) from column A to D: 1931 , 04-15 , Name , SC
I would like to to have all of these rows include the sheet name in column A and split the hyphenated numbers into two columns B and C so that the final result would end up something like this from A to E: sheet1 , 4 , 15 , Name , SC
Another caveat is that the many of the cells containing the hyphenated number have spaces before the numbers.
Can somebody help with a macro that will make changes for the varying number of lines in each of the sheets?

 

[Roderick_E]

Did you want to retain the existing data in column A? (ie 1931)?

 

[knpaddac]

Dang! I forgot to include that. Yes, I do want to retain column A. The result should look like this with info in columns A to F: sheet1, 1931 , 4 , 15 , Name , SC
Also, the final result should not have any spaces before or after the data, I just put that in to visually show the separation of columns.

Did you want to retain the existing data in column A? (ie 1931)?

 

[Roderick_E]

Ok test this on a COPY first... Do you understand how to add modules/macros?

Sub WorksheetLoop()


         ' Declare Current as a worksheet object variable.
         Dim Current As Worksheet


         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets
'unfilter sheet if filtered
            If Current.AutoFilterMode Then
    Current.Cells.AutoFilter
    End If
'find lastrow
lastrow = 0
If IsError(Current.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row) = False Then
lastrow = Current.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
End If


'do only if sheet has data
If lastrow > 0 Then
For x = 1 To lastrow
'working backwards
Current.Cells(x, "f") = Current.Cells(x, "d")
Current.Cells(x, "e") = "Name"
Current.Cells(x, "d") = Trim(Mid(Current.Cells(x, "b"), InStr(Current.Cells(x, "b"), "-") + 1, 99))
Current.Cells(x, "c") = Trim(Left(Current.Cells(x, "b"), InStr(Current.Cells(x, "b"), "-") - 1))
Current.Cells(x, "b") = Current.Cells(x, "a")
Current.Cells(x, "a") = Current.Name
Next x
End If
           
         Next
MsgBox "Complete"
      End Sub

 

[knpaddac]

I am relatively familiar (right click on a sheet tab, click 'view code', paste the code above, and choose 'run'.

When I do this I am getting 'Run-time error '5' Invalid procedure call or argument'

 

[Roderick_E]

this isn't a worksheet macro, please press ALT+F11, Insert, Module and paste in the code to the white large box

 

[knpaddac]

thanks. very grateful for the help.

 

[Roderick_E]

thanks. very grateful for the help.

You're welcome. Hopefully it accomplished what you wanted.