Recorded macro doesn't work on other sheets

Question

I'm working on a recorded macro that will execute the same action to a lot of similar sheets. If works neatly for the sheet i recorded it on, however, when i try it on other workbooks it doesn't work. Here's a piece of the code, basically it copies the first column, pastes on a new sheet, removes duplicates and gets the first 6 numbers:

Sub Macro2()

    ActiveSheet.Select
    Columns("A:A").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count).Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.UnMerge
    ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],6)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B14")
    Range("B2:B14").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Selection.Insert Shift:=xlToRight
    Range("D8").Select
End Sub

I know it doesn't look good, I'm still learning. I don't get it why it doesn't work on other sheets if it looks so simple.

What do you mean by "it doesn't work"? I see nothing that is sheet specific. However you should reduce/remove the selections. [here](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/9292/avoid-using-select-or-activate) are some tips. — arcadeprecinct, Sep 14 '16 at 14:39

BruceWayne · Accepted Answer · 2016-09-14T15:02:15.760

0

You're getting inconsistent results likely due to the use of .Select, which it's best practice to avoid.

I've created two variables to store your worksheets, and do what you have above, but without .Select. The only thing is, what's the last column .Insert Shift:=xlToRight supposed to do?

Sub macro2a()
Dim mainWS As Worksheet, newWS As Worksheet

Set mainWS = Worksheets("Sheet1")    ' I assume this is the sheet name of your main worksheet.
Set newWS = Sheets.Add(after:=Sheets(Sheets.Count))

newWS.Columns("A:A").Value = mainWS.Columns("A:A").Value    ' instead of copy/paste, you can set ranges equal if you just need values.
With newWS
    .Columns("A:A").UnMerge
    .Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo

    .Range("B2").FormulaR1C1 = "=Left(RC[-1],6)"
    .Range("B2").AutoFill Destination:=.Range("B2:B14")
    .Range("B2:B14").Value = .Range("B2:B14").Value ' instead of copy/paste special, just set values equal to remove formulas

    With .Columns("A:A")
        .ClearContents
      '   .Insert shift:=xlToRight  ' What's this line to do?
    End With '.Columns("A:A")

End With 'newWS
newWS.range("D8").Select ' This is okay since it's the end, and we're just using .Select to get to the cell.
End Sub

edited Sep 14 '16 at 15:02

answered Sep 14 '16 at 14:55

BruceWayne

22,923
15
65
110

Thanks, I'll give it a try. The last commands delete the first column (with the data i pasted from the other sheet) and include a blank one (odd, but necessary). The data i want remains in the column B as i used the formula LEFT to extract the value i need. – Serveira Sep 14 '16 at 15:01
@Serveira - Okay, I just edited that last line to just clear contents of column A. *Please* note I wasn't sure what sheet should have been the "pasting TO" sheet, vs. the data source, so you may have to tweak the sheets in the code. – BruceWayne Sep 14 '16 at 15:02
That works perfectly, thanks. I'll try and adapt this to the rest of my code. – Serveira Sep 14 '16 at 15:43
@Serveira - Happy to help! I ***highly*** suggest you review my code and compare to yours and learn how to avoid `.Select`. I didn't really "do" a whole lot to get it working, all I did was avoid using `.Select`, and used two worksheet variables. Essentially, to remove `.Select`, just "snug" it back up to the line ending in `.Select`...ie: `Cells(1,1).Select // Selection.Copy` can just be `Cells(1,1).Copy` (just remove `.Select`, `.Selection`). – BruceWayne Sep 14 '16 at 15:53
Will do! I'm really trying to learn how to code and you guys are helping a lot. – Serveira Sep 14 '16 at 20:19

Recorded macro doesn't work on other sheets

1 Answers1