[paul_carron]

Hi,

When I try to run a macro in Excel I get error1.jpg. If I click OK to come out of it error2.jpg appears.

When I select Debug I get code.jpg. I think the issue is that there is no data to paste into the GME worksheet. Is this possibly the cause of my error? If not are there any possible suggestions as to what the cause might be?

Cheers
Paul

[GeorgeJ]

What is the selection? Is this done manually or defined elsewhere?
If the selection is on the last row of the usedrange, would this not copy everything from the selection to the bottom of the sheet? Also, is there something to make sure that the selections row is not less than 4?

[paul_carron]

Sorry, heres the whole script:

Sub Update()
'
' Update Macro
' Macro recorded 01/06/2009 by JohnstonM
'

'
Sheets("No links").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Sheets("BEL").Select
Cells.Select
Selection.ClearContents
Sheets("BGY").Select
Cells.Select
Selection.ClearContents
Sheets("CDF").Select
Cells.Select
Selection.ClearContents
Sheets("DGN").Select
Cells.Select
Selection.ClearContents
Sheets("FMT").Select
Cells.Select
Selection.ClearContents
Sheets("GBY").Select
Cells.Select
Selection.ClearContents
Sheets("GME").Select
Cells.Select
Selection.ClearContents
Sheets("OMA").Select
Cells.Select
Selection.ClearContents
Sheets("PIT").Select
Cells.Select
Selection.ClearContents
Sheets("TMB").Select
Cells.Select
Selection.ClearContents
Sheets("Working").Select
Rows("6:400").Select
Selection.Copy
Sheets("No links").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="BEL"
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BEL").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="BGY"
ActiveWindow.SmallScroll Down:=-3
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BGY").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="CDF"
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CDF").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="DGN"
ActiveWindow.SmallScroll Down:=-9
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DGN").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="FMT"
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FMT").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="GBY"
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("GBY").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="GME"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("GME").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="OMA"
Range("A1").Select
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("OMA").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="PIT"
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PIT").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="TMB"
Range("A1:CR1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TMB").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
Sheets("No links").Select
Range("A1").Select
Selection.AutoFilter Field:=3
Sheets("Working").Select
Range("C7").Select
Application.CutCopyMode = False
End Sub

[GeorgeJ]

It might be because you are selecting A1:CR1 and everything underneath
If there is nothing in A2 then i think it is trying to copy the whole page and paste it into page GME but starting at A4 so there is not enough space to paste everything.

If this is not the error, it may be that the amount of data you are trying to copy is more than your system can cope with. If that is the case, you will need to find another way of coding this.

[Zack Barresse]

Hi there,

While I have no way of testing this, your code can be boiled down quite a bit...

Sub Update()
'
' Update Macro
' Macro recorded 01/06/2009 by JohnstonM
'

'
    Dim WB As Workbook
    Dim wsNL As Worksheet
    Dim wsWorking As Worksheet
    Dim WS As Worksheet
    Dim rFilter As Range
    Dim rCopy As Range
    Dim aWS(1 To 10) As Variant
    Dim iStep As Long
    Dim iLastRow As Long

    Set WB = ThisWorkbook
    Set wsNL = WB.Worksheets("No Links")
    Set wsWorking = WB.Worksheets("Working")

    aWS(1) = "BEL"
    aWS(2) = "BGY"
    aWS(3) = "CDF"
    aWS(4) = "DGN"
    aWS(5) = "FMT"
    aWS(6) = "GBY"
    aWS(7) = "GME"
    aWS(8) = "OMA"
    aWS(9) = "PIT"
    aWS(10) = "TMB"

    Application.ScreenUpdating = False
    
    wsNL.Range(wsNL.Range("2:2"), wsNL.Range("2:2").End(xlDown)).EntireRow.Delete
    wsWorking.Range("6:400").Copy
    wsNL.Range("A1").PasteSpecial xlPasteValues
    wsNL.Range("K:K").Delete

    For iStep = LBound(aWS) To UBound(aWS)
        WB.Worksheets(aWS(iStep)).Cells.ClearContents
        Set WS = WB.Worksheets(aWS(iStep))
        iLastRow = wsNL.Range("A:CR").Find(What:="*", After:=wsNL.Range("A1"), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set rFilter = wsNL.Range("A1", wsNL.Cells(wsNL.Rows.Count, "A").End(xlUp))
        rFilter.AutoFilter 3, aWS(iStep)
        Set rCopy = wsNL.Range("A1:CR" & iLastRow).SpecialCells(xlCellTypeVisible)
        WS.Range("A4").PasteSpecial xlPasteValues
    Next iStep

    wsNL.AutoFilterMode = False
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

We could probably tidy it up a bit if you explained your data structure a little more in detail.

HTH