Macro to insert a column

fatman0013

New Member
Joined
Sep 20, 2010
Messages
13
Well i got this far.... but it looks like i have alot to learn.
i have 20 worksheets in a workbook and i ned to insert a new column to the right of column A, and then pars the data in column A using a fixed with.

here is what i have :

Sub insert 3()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
Columns("B:C").Select
Selection.insert Shift = lToRight
Next ws
End Sub
'all this is doing is adding about 50 columns to the active worksheet.

'Then to Pars the data in colum A i was tring this:

Sub pars()
' pars Macro
Dim ws As Worksheet
For Each ws In Worksheets
'make sure that cell B1 is not empty

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(12, 1)), TrailingMinusNumbers:=True

Next myWorksheet
End Sub

This one worked it parsed my data from column A with the fix-with; but again it only did it in the active worksheet and not for all of the worksheet in the workbook.

Any help would be great!!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You need to qualify the sheet like this

Code:
Sub insert 3()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
      ws.Columns("B:C").insert 
Next ws
End Sub
 

fatman0013

New Member
Joined
Sep 20, 2010
Messages
13
hey that worked great how about this one any clue why it wont go to the next Worksheet

Sub pars()
' pars Macro
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets

Columns("A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(12, 1))


Next ws

End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub pars()
' pars Macro
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

    ws.Columns("A").TextToColumns Destination:=ws.Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(12, 1))
    

Next ws

End Sub
 

Forum statistics

Threads
1,143,661
Messages
5,720,143
Members
422,267
Latest member
olund

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top