Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Macro Issue


(!)

paul_carron's Avatar
paul_carron paul_carron is offline
Member with 191 posts.
THREAD STARTER
 
Join Date: Oct 2006
Experience: Advanced
08-Aug-2012, 09:51 AM #1
Excel Macro Issue
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
Attached Thumbnails
Excel Macro Issue-error1.jpg   Excel Macro Issue-error2.jpg   Excel Macro Issue-code.jpg  
GeorgeJ's Avatar
GeorgeJ GeorgeJ is offline
Member with 144 posts.
 
Join Date: Jan 2012
Location: Scotland
Experience: Intermediate
08-Aug-2012, 10:34 AM #2
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's Avatar
paul_carron paul_carron is offline
Member with 191 posts.
THREAD STARTER
 
Join Date: Oct 2006
Experience: Advanced
08-Aug-2012, 10:54 AM #3
Sorry, heres the whole script:

Quote:
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's Avatar
GeorgeJ GeorgeJ is offline
Member with 144 posts.
 
Join Date: Jan 2012
Location: Scotland
Experience: Intermediate
08-Aug-2012, 11:22 AM #4
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's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
09-Aug-2012, 12:49 AM #5
Hi there,

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

Code:
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑