Macro only runs on sheet i recorded it on.

[BF15]

I have a macro that i recorded on the first tab of my spreadsheet. I need it to function on all the tabs, and it looks likeits hanging up because it specficially only works on the first tabe. Here is the code form the macro. Can you guys pleasehelp me figure out how to make it function on all the tabs of the workbook.

Thanks,
brian

Sub pastImacro()
'
' pastImacro Macro
'
' Keyboard Shortcut: Ctrl+i
'
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Table2").Select
    Range("F3").Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("01-Sep-2010").ListObjects("Table2").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("01-Sep-2010").ListObjects("Table2").Sort.SortFields. _
        Add Key:=Range("Table2[Status]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("01-Sep-2010").ListObjects("Table2").Sort.SortFields. _
        Add Key:=Range("Table2[Created On]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("01-Sep-2010").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F3").Select
    ActiveWindow.SmallScroll Down:=-27
    Range("A3:F3").Select
    Range("F3").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A4:F4").Select
    Range("F4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    Range("A3:F4").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A5:F101").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

[BF15]

just to add to this, all the tabs are excat copies of each other, other than having a different date. Im trying to use this macro to jsut simply be able to do a paste and it sort, and format the page.

[Andrew-R]

Hello and welcome to the forum - unfortunately before we go any further you need to read the forum rules and use code tags around any code that you post. If you edit your original post then I'm sure somebody will be happy to help you.

[BF15]

Hello and welcome to the forum - unfortunately before we go any further you need to read the forum rules and use code tags around any code that you post. If you edit your original post then I'm sure somebody will be happy to help you.

Ok, i think i wraped it correctly.

[Andrew-R]

Yup, looks like the job.

Macros written with the macro recorder are rather inefficient, because they have in a lot of selecting data which doesn't need to happen. It's late here in the UK, so rather than rewrite this properly I've just hacked it to work, but work it should...

Sub pastImacro()
'
' pastImacro Macro
'
' Keyboard Shortcut: Ctrl+i
'
Dim lSheetLoop As Long

For lSheetLoop = 1 To ActiveWorkbook.Sheets.Count

  ActiveWorkbook.Sheets(lSheetLoop).Activate

    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Table2").Select
    Range("F3").Activate
    Application.CutCopyMode = False
    ActiveSheet.ListObjects("Table2").Sort.SortFields. _
        Clear
    Activesheet.ListObjects("Table2").Sort.SortFields. _
        Add Key:=Range("Table2[Status]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    Activesheet.ListObjects("Table2").Sort.SortFields. _
        Add Key:=Range("Table2[Created On]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With Activesheet.ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F3").Select
    ActiveWindow.SmallScroll Down:=-27
    Range("A3:F3").Select
    Range("F3").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A4:F4").Select
    Range("F4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    Range("A3:F4").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A5:F101").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Next lSheetLoop

End Sub

[BF15]

Yup, looks like the job.

Macros written with the macro recorder are rather inefficient, because they have in a lot of selecting data which doesn't need to happen. It's late here in the UK, so rather than rewrite this properly I've just hacked it to work, but work it should...

Sub pastImacro()
'
' pastImacro Macro
'
' Keyboard Shortcut: Ctrl+i
'
Dim lSheetLoop As Long

For lSheetLoop = 1 To ActiveWorkbook.Sheets.Count

  ActiveWorkbook.Sheets(lSheetLoop).Activate

    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Table2").Select
    Range("F3").Activate
    Application.CutCopyMode = False
    ActiveSheet.ListObjects("Table2").Sort.SortFields. _
        Clear
    Activesheet.ListObjects("Table2").Sort.SortFields. _
        Add Key:=Range("Table2[Status]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    Activesheet.ListObjects("Table2").Sort.SortFields. _
        Add Key:=Range("Table2[Created On]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With Activesheet.ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F3").Select
    ActiveWindow.SmallScroll Down:=-27
    Range("A3:F3").Select
    Range("F3").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A4:F4").Select
    Range("F4").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    Range("A3:F4").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A5:F101").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Next lSheetLoop

End Sub

I replaced my existing code on in th emacro with this.

Now when i try to sue ctrl i to do the special paste it does not paste anything.

[Andrew-R]

Please don't quote entire posts when replying, it just clutters up the thread.

Is the Ctrl-i short-cut still assigned to the macro? That would be the first thing to check.

[BF15]

Please don't quote entire posts when replying, it just clutters up the thread.

Is the Ctrl-i short-cut still assigned to the macro? That would be the first thing to check.

Sorry about that.

Ok, that was the problem. Now im having an issue when i run the macro.

run-time error '1004':

PasteSpecial method of Range class failed.

Do you have any idea what could cause this?
Thanks for all your help.

[Andrew-R]

It looks like the issue is that you're trying to paste special without actually copying anything first - are you running this macro after selecting a range to copy?

[BF15]

It looks like the issue is that you're trying to paste special without actually copying anything first - are you running this macro after selecting a range to copy?

yes, im copying the datafrom a seperate spreadsheet. and trying to paste it into the one the macro is ran on.

[Andrew-R]

OK, that's what's causing the problem. On the first pass through the worksheet loop you're pasting the data then turning the cut and paste mode off, so subsequent passes through have nothing to paste.

Is it the range "Table2" that you're pasting?