1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro Issue

Discussion in 'Business Applications' started by paul_carron, Aug 8, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. paul_carron

    paul_carron Thread Starter

    Joined:
    Oct 15, 2006
    Messages:
    198
    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 Files:

  2. GeorgeJ

    GeorgeJ

    Joined:
    Jan 16, 2012
    Messages:
    144
    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?
     
  3. paul_carron

    paul_carron Thread Starter

    Joined:
    Oct 15, 2006
    Messages:
    198
    Sorry, heres the whole script:

     
  4. GeorgeJ

    GeorgeJ

    Joined:
    Jan 16, 2012
    Messages:
    144
    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.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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
     
  6. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064301