Running Call Procedures & Macros on all Worsheets in Workbook axcluding a Control Sheet

Afternoon Folks,

Looking for a bit of help again, please? I have finally managed to import a number of CSV files into one spreadsheet using a macro... my intention now is to use a series of macros as Called procedures to perform the data clean up and calculation on the individual worksheets and return the results onto the control sheets or various output sheets etc as I run them.

The 1st stumbling block I have hit is running the text to columns macro on all sheets, throws up an error as the 1st sheet doesn have any data to parse.....its the control sheet! There must be something simple that will allow me to run all my macros against all sheets excluding the 1st one in the workbook? I cant find it...

The sheet names change daily, as do the number of them... the only constants here are the contents of the control sheet and the contents of the data being pulled through on the extracts...

I'm a total newbie to VB, any help is greatly apprecaited!

Cheers

Thanks, and now my novice skills really start to show...

so using the code above I thought that I would Call the various procedures that i want to run on each of the worksheets, excluding the Control sheet..

The 1st one being a fairly simple Text to Columns procedure to separate the CSV file into separate columns...

The code now looks like this

[Code]

Sub Perform_Data_Cleanup()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Control" Then

Call Text_to_Column_Comma 'run text to columns macro on all worksheets
Call DeleteColumnF 'run delete columnf macro on all worksheets
Call copy_subject_Column 'run copy subject column macro on all worksheets

End If

Next ws

End Sub

Sub Text_to_Column_Comma()
'
' Text_Columns1 Macro
' Macro to separate Text to Columns where the delimiter is Comma
'
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Range("A1").Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
:=",", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=3
Application.DisplayAlerts = False
Application.ScreenUpdating = True

End Sub
Sub DeleteColumnF()
'
' DeleteColumnF Macro

Worksheet.Columns("F:F").Delete Shift:=xlToLeft

End Sub
Sub copy_subject_Column()

Range("D:D").Select
Selection.Copy
Range("H:H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheet.Columns("D:D").Delete Shift:=xlToLeft
End Sub
Sub Text_to_Column_Asterisk()
'
' Text_Columns1 Macro on Asterisk
' Macro to separate Text to Columns where the delimiter is *
'
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Range("G1").Select
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
:="*", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=3
Application.DisplayAlerts = False
Application.ScreenUpdating = True

End Sub

[Code]

The only problem with this is that I get a runtime 1004 error on this at

[code]

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
:=",", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
[code]
on the 1st text tol columns, i'm assuming becuase its still trying to run the text to columns on the control sheet as thats where the debugger takes me to....

appreciate that the code is really inefficient and untidy just now, but all the elements work individually, and on individual worksheets, I can work on bringing it together bit by bit (may still be here next year!!!

Anyone got any ideas please?!

Cheers

Thanks Norie, would really appreciate this!

sure - sorry...

Please Login or Register  to view this content.

The only problem with this is that I get a runtime 1004 error on this at

Please Login or Register  to view this content.

on the 1st text tol columns, i'm assuming becuase its still trying to run the text to columns on the control sheet as thats where the debugger takes me to....

appreciate that the code is really inefficient and untidy just now, but all the elements work individually, and on individual worksheets, I can work on bringing it together bit by bit (may still be here next year!!!

Anyone got any ideas please?!

Anyone help me with this one... sorry to bump

Thanks very much for this - working like a dream. I now get what you mean. I have several other procedures that I will be calling in here so get the principles behind what it's doing after deeing it laid out!

I have a few other challenges...would be interested to see if you have any thoughts, will raise a separate post!