Removing leading zeros and using text to columns in VBA

[jazminmorales]

I'm trying to remove all leading zeros from the entire column A (which depending on the day will have a different number of rows) and I also need to use text to columns for column E. I need both of these to be in the same macro.

 

[Joe4]

Welcome to the Board!

Just turn on the Macro Recorder, and record yourself doing these steps manually, and you should have most of the code you need.
The number of rows shouldn't matter, as you will be doing Text to Columns on the entire column.

 

[jazminmorales]

After I use the macro recorder to set the two columns to text and add a pivot table refresh, it gives me run-time error '1004'. :

Run-time error '1004':
We can't make this change for the selected cells because it will affect a PivotTable. Use the field list to change the report. If you are trying to insert or delete cells, move the PivotTable and try again.

 

[Joe4]

Are you getting this error while you are recording your Macro?
Or after you have recorded it, and trying to run it?

 

[jazminmorales]

After I use the macro recorder to set the two columns to text and add a pivot table refresh, it gives me run-time error '1004'. :

Run-time error '1004':
We can't make this change for the selected cells because it will affect a PivotTable. Use the field list to change the report. If you are trying to insert or delete cells, move the PivotTable and try again.

The error is after I try to run it.
This is the code:

Sub Practice_1()
'
' Practice_1 Macro
'

'
Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("NS Level").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("CO Level").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

I've also tried this code but it prompts you to manually select the cells and I want it to just run for both column A and E.
Sub RemoveLeadingZero()
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select one Range that you want to remove leading zero:", "RemoveLeadingZero", WorkRng.Address, Type:=8)
WorkRng.NumberFormat = "General"
WorkRng.Value = WorkRng.Value
End Sub

 

[Joe4]

Are you running on a "fresh" sheet of data, or after it has already been run once?
Because if the data is already set up with a Pivot Table before you try running the code, I could see how that could be a problem.