Thread: Adding action to current macro

houseguy007,

Edit your post and add the code tags (use the full editor, highlight the code and click on the #)

I did not see any periods in the workbook you had posted in this thread.

Adding teylyn's solution from here, change the Selection to specify the range.

Code:

Sub DataManip()
Dim bRow As Long, strWeek As String, i As Integer
strWeek = Application.InputBox("Week please")
For i = 1 To Sheets.Count
If Sheets(i).Name = "3- Table Chat Volume By Operat" Or Sheets(i).Name = "13- Table Chat Operator Perfor" Then
With Sheets(i)
.Rows("1:7").EntireRow.Delete
bRow = Sheets(i).Cells(1, 1).End(xlDown).Row
.Rows(bRow + 1 & ":" & Rows.Count).EntireRow.Delete
.Columns(2).Insert
.Range("B1") = "Week"
.Range("b2:b" & bRow) = strWeek
End With
End If

      Rows(1).Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next
End Sub

Sorry about that. I didn't realize the periods were a problem until later.

I tried to use the Macro but it didn't delete the periods. Would you like me to include a version of the workbook I posted earlier with periods in it?

Yes - please

Attached is the workbook with periods included in the header row similar to how they appear in the actual document. Thanks again for your help.

If you are referring to the periods in the Metric. # cells the macro as posted will remove them. To make it work with the book you posted, I needed to adjust the macro to delete 6 (instead of 7) rows and to look for the Sheet3 and Sheet13, but beyond that, it will work as posted

So it worked when you tried it?

I still can't get it to work for some reason. Are there any formatting issues that might cause the macro not to recognize a period?

Ahh crum! - I did it again (or didn't do it).

I forgot to make specify the sheet that the Find Replace is to be used on. I had Sheet3 as the active sheet, so when the macro ran, it removed the period from that sheet (twice).

Try this one. (change the sheet names to match your set up and the rows to be deleted (you had asked for 1 through 6, but the macro you posted earlier is showing 1 through 7.)

Code:

Sub DataManip()
Dim bRow As Long, strWeek As String, i As Integer
strWeek = Application.InputBox("Week please")
For i = 1 To Sheets.Count
If Sheets(i).Name = "Sheet3" Or Sheets(i).Name = "Sheet13" Then
With Sheets(i)
.Rows("1:6").EntireRow.Delete
bRow = Sheets(i).Cells(1, 1).End(xlDown).Row
.Rows(bRow + 1 & ":" & Rows.Count).EntireRow.Delete
.Columns(2).Insert
.Range("B1") = "Week"
.Range("b2:b" & bRow) = strWeek
End With
End If

      Sheets(i).Rows(1).Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next
End Sub

YES! It worked! Thanks!