remove columns with specific data in multiple sheets

**tanakwagu** · 06-22-2010, 01:19 AM

hi,
i'm a newbies with excel. hope that u all can give me an idea on how to do these things on multiple sheets at once using macro. For your information, i have 100+ sheets in a single workbook to work in a short time. What i need to do is;

i/ removing all column containing "29122" (formated as text) in multiple sheet..

ii/ adding new column before the column containing "Peruntukan (ET) 2010" in multiple sheet.

help needed... tq in advance...

as for now, i`m using these code but need to rerun on each sheet;

Sub DeleteColumn()
'
' DeleteColumn Macro
'
' Keyboard Shortcut: Ctrl+d
'
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "Peruntukan (ET) 2010"
    Range("B4").Select
    Selection.Columns.AutoFit
End Sub

**pike** · 06-22-2010, 04:57 AM

Hi tanakwagu
Welcome to the forum.
Maybe something like...

option explicit
Sub DeleteColumn()
dim ws as worksheets
 for each ws In worksheets
    with sheets(ws.Name)
    Columns("J:J").Delete Shift:=xlToLeft
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   with Range("B4")
               .FormulaR1C1 = "Peruntukan (ET) 2010"
    .Columns.AutoFit
end with
end with
next
End Sub

**tanakwagu** · 06-22-2010, 05:49 AM

Thanks for the reply Mr.Pike..

i've test the code but got error returned. here attached workbook sample.
what i'm going to do is;

remove entire column labeled as 26799, 27401, 27402, 27403, 27499 and 29122 (all marked red on SET1 sheet as example)

add new column after "Objek (CE)" column and labeled as "Peruntukan (ET) 2010"

and everything is done at once to all sheet. I can edit this manually but since it got 100++ sheet, it'll be great if i can apply it to all sheet at once. The "final" sheet is what it's suppose to be.

**pike** · 06-22-2010, 05:58 AM

hi Mr tanakwagu try..

Option Explicit
Sub DeleteColumn()
Dim ws As Worksheet 'change form worksheets
 For Each ws In Worksheets 
   With Sheets(ws.Name)
   Columns("J:J").Delete Shift:=xlToLeft
   Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  With Range("B4")
              .FormulaR1C1 = "Peruntukan (ET) 2010"
   .Columns.AutoFit
End With
End With
Next
End Sub

**pike** · 06-22-2010, 06:06 AM

ops,, ive tested this one

Option Explicit
Sub DeleteColumn()
Dim ws As Worksheet
 For Each ws In Worksheets
   Debug.Print ws.Name
   With Sheets(ws.Name)
   .Columns("J:J").Delete Shift:=xlToLeft
   .Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   .Range("B4").FormulaR1C1 = "Peruntukan (ET) 2010"
   .Range("B4").Columns.AutoFit
End With
Next
End Sub

**tanakwagu** · 06-22-2010, 08:37 PM

Bro Pike,

i've tested it on my workbook and it's running fine. Big Thanks for the solution.

What if the column that i want to remove was placed on different cell and column at each sheet and the only key or reference is the column label marked red (refer SET1 on attachment). Should i start in a new thread for this or continue here since this scenario were related to the first one.

**MSALIM** · 07-24-2010, 06:57 AM

this programe i want to run on multiple sheet more then 15 sheet so how i apply and which sheet or workbook i will apply???plz guide me and help me because i am new learner

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!
'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex

'Leave On Error ON for Row offset errors

If iColor < 0 Then
iColor = 36
Else
iColor = iColor + 1
End If

Cells.FormatConditions.Delete

With Range(Target.Address, Target.Address)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub

**pike** · 09-29-2010, 07:29 AM

MSALIM
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.