1

I have a working VBA script for the worksheet Function Test Procedure(FTP) that will take the values stored in 32 individually named ranges and paste them to a separate worksheet, Results. When I attempted to use that same script for Acceptance Test Procedure(ATP), it repeats the results from FTP and copies them below the original FTP Results. I am still new to VBA and do not understand much of it, but I do understand other programming languages. I cannot understand why this will not work for ATP like it did for FTP.

Function Test Procedure Script

Sub Copy_Filtered_Sections()

    Dim Section As Long, NextRow As Long

    For Section = 1 To 32

        NextRow = Sheets("Results").Range("A" & Rows.Count).End(xlUp).Row + 1 'Next empty row

        Sheets("Function Test Procedure").Select

        Range("FTPSec" & Section).Columns("A:H").SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("Results").Range("A" & NextRow)

'        Range("FTPSec" & Section).Columns("G:H").SpecialCells(xlCellTypeVisible).Copy _
'            Destination:=Sheets("Results").Range("N" & NextRow)

    Next Section

End Sub

Acceptance Test Procedure Script

Sub Copy_ATP_Tables()

    Dim SectionATP As Long, NextRow As Long

    For SectionATP = 1 To 32

        NextRow = Sheets("Results").Range("A" & Rows.Count).End(xlUp).Row + 1 'Next empty row

        Sheets("Acceptance Test Procedure").Select

        Range("ATPSec" & SectionATP).Columns("A:H").SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("Results").Range("A" & NextRow)

'           Range("FTPSec" & Section).Columns("G:H").SpecialCells(xlCellTypeVisible).Copy _
'                Destination:=Sheets("Results").Range("N" & NextRow)

    Next SectionATP


End Sub

The second function is returning the following error message:

"Run-time error '1004': Method 'Range' of object '_Global' failed" on the line "Range("ATPSec" & SectionATP).Columns("A:H").SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("Results").Range("A" & NextRow)

  • I'm not a VBA expert but one obvious difference is the 1st function uses Sheets("Function Test Procedure").Select and the 2nd Sheets("Acceptance Test Procedure").Activate. What happens if you change Activate to Select in the 2nd function? – DavidPostill Sep 30 '15 at 16:40
  • Still gives me the same error "Run-time error '1004': Method 'Range' of object '_Global' failed" on the line "Range("ATPSec" & SectionATP).Columns("A:H").SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("Results").Range("A" & NextRow). It is the same error as before I posted the question. – Gerasimos.Zap Sep 30 '15 at 17:06
  • Ah. You should have included the error message in your question. I've done it for you. Next time please include any error messages. – DavidPostill Sep 30 '15 at 17:10
  • My mistake, though I thought I included the error message. I am trying to figure out why it is failing. It was working just fine before I copied the macro to run on another worksheet. – Gerasimos.Zap Sep 30 '15 at 17:19
1

I believe the issue is that the workbook has named ranges for FTP but not for ATP. In the Function Test Procedure code, on the first iteration the analogous line

Range("FTPSec" & Section).Columns("A:H").SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheets("Results").Range("A" & NextRow)

calls for the visible cells in columns A through H of named range FTPSec1. Presumably there are named ranges FTPSec1, FTPSec2, ..., FTPSec32 in your workbook.

In the Acceptance Test Procedure code, by mimicking the syntax here, you've assumed that named ranges ATPSec1, ATPSec2, ..., ATPSec32 are defined in the workbook. If they are not, your code will throw an error because Excel does not know what Range("ATPSec1") is referring to.

To check that this is in fact the problem, open the Name Manager in Excel by pressing Ctrl + F3. Here you will see all the named ranges in the workbook. I suspect you will see the FTP ranges but not the ATP ranges.

To solve this problem, you must do one of the following:

  • Define ranges ATPSec1, ATPSec2, ..., ATPSec32 in your workbook. Do this by creating new named ranges via the Name Manager; or

  • Alter your code for ATP so that the VBA identifies the relevant ranges, presumably using the same criteria you would use to define the ranges in the first place.

  • No...I remembered to set new named ranges for ATP. One of the first things I did was to look over my named ranges and see if anything was amiss and it wasn't. – Gerasimos.Zap Sep 30 '15 at 17:26
  • You were correct. Instead of naming them ATPSec1...ATPSec 31, I named them APTSec1...APTSec31. I adjusted my macro to use APT instead of ATP and it worked. – Gerasimos.Zap Sep 30 '15 at 18:18
  • @G.Zap glad to hear it! If this solution worked for you, you can indicate it by clicking the check mark below the down arrow beside this answer. See this help page for more information about accepting answers. – Excellll Sep 30 '15 at 18:38
0

To remove ambiguity, you should refer to the sheet when using a range. Combine two lines of code

    Sheets("Acceptance Test Procedure").Select
    Range("ATPSec" & SectionATP).Columns("A:H").SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheets("Results").Range("A" & NextRow)

to this:
Sheets("Acceptance Test Procedure").Range("ATPSec" & SectionATP).Columns("A:H").SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("Results").Range("A" & NextRow)

Thus you qualify the source in the same way you qualify the destination.
If that doesn't remove the error you have to debug this manually. In the VBA editor, step into the sub (F8) to initialize the variables. Then click into the direct access window and type

SetctionATP = 1
? Sheets("Acceptance Test Procedure").Range("ATPSec" & SectionATP).address

This should return a valid range. Next

? Sheets("Acceptance Test Procedure").Range("ATPSec" & SectionATP).Columns("A:H").address

You can test other named ranges by varying SectionATP.

  • 1
    I stepped through it and it got caught up at the line i just added based on your recommendation. It gives me an error stating "Application-defined or object-defined error." And the Immediate command requires an end of statement or I get the same error as above. – Gerasimos.Zap Sep 30 '15 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.