Posts by markc

    Hi all

    Still having problems trying to activate a worksheet
    Most likely it is the active worksheet, I just need to make sure it is at

    Bugs out here:
    In the GetData procedure
    Error msg : Subscript out of range


    I also tried
    Worksheet("Sheet1").Activate and

    The actual name of the sheet will always be "strip_pdf_text" w/o quotes
    as defined by a .vbs process to convert from .pdf to .txt and then to .tsv.

    The file contains only 1 sheet

    Here is the entire code:


    Re: Data in Pivot Table

    Thanks Norie / Maqbool -

    Now I get it.
    I was trying to drag each field to the outer
    Would not let me do that
    Hence the confusion

    Thanks again

    Re: Data in Pivot Table

    Norie -
    Still Can't get it

    I start with the data field
    I drag shopper payment to the data field
    Count of Shopper payment field appears at A3
    The word "Total" is at B4
    And the count of payments down colB

    When I drag other data fields they also continue
    down colB

    Can I move these other data fields over to Col C and D?
    Or am I stuck with them in ColB?

    I updated my example


    Re: Subscript out of range error

    Thanks Will -

    Here is everything I did / tried

    OpenSelectedFile Procedure :

    Sub OpenSelectedFile()
    Dim myFile As Variant
    myFile = Application.GetOpenFilename("E*xcel Files (*.xl*), *.xl*", _
        , "Pick one:")
    If myFile <> False Then Workbooks.Open (myFile)
    End Sub

    Then changed GetData Procedure
    This error'd with Subscript out of range

    Then tried adding the .xls extension
    Same error message


    Re: Subscript out of range error

    Hi Dennis
    That didn't work either.
    The name of the sheet is not sheet1
    Is that the problem?
    I do not know the name of the sheet
    The user may change it


    Re: Subscript out of range error

    Hi Will

    I used the code to open a workbook with a single sheet named

    Here is the procedure for opening the file

    Sub OpenSelectedFile()
    Dim LeFile As Variant
    LeFile = Application.GetOpenFilename("E*xcel Files (*.xl*), *.xl*", _
        , "Pick one:")
    If LeFile <> False Then Workbooks.Open (LeFile)
    End Sub

    Hi all

    My procedure allows user to browse for file for copy/paste
    So I don't know the name of the file the user is choosing

    I'm trying to use this code to handle whatever the user

    lngRows = ActiveWorkbook.Worksheets("Sheet1").Range("A65536").End(xlUp).Row

    But The code is erroring out at this point
    Run time error 9
    Subscript out of range

    Seems like I am using an invalid method of ActiveWorkbook.
    Is there a better way to handle this?

    Full code:

    Re: Wildcard search / replace

    Thanks Dennis -

    Code is hanging here
    Error is RunTime error 62
    Input past end of file

    stContents = Input(LOF(fNum), fNum)

    Currently last line used is 1640, but that will expand/contract weekly

    Thanks much

    Re: Paste special method failed...

    Hi Norie -

    Here is the corrected code :

        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    Still receive the "Paste special..." error

    To run
    Alt + F8 to activate macro menu
    Click macro

    The controlling macro actually calls several subprocedures
    of which the above is a part.


    OpenSelectedFile procedure is in a separate module.


    Re: Paste special method failed...

    Timbo -

    Tried your code
    Came back Subscript out of range
    Highlighted here


    I tried commenting the line out
    Came back with paste special method failed

    Also, most of what I've read on the forum suggests
    not using Select

    Indeed I ran into some problems with slow code execution
    due to select command.

    See my previous post Re: slow code execution.

    Hi all -

    I keep receiving
    "Paste Special method of range class failed"

    I've tried re-writing the code several different ways,
    none of which works.

    Can you give me a pointer at what I'm doing wrong?


    Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10").PasteSpecial _
         Paste:=xlValues, Operation:=xlNone, _
         SkipBlanks:=False, Transpose:=False

    Hi all -

    I am receiving message
    "There is a large amount of data..."

    When I try to copy data from source
    Close Source
    Paste to destination

    I read up on Google and thought my code should fix the problem
    But I still receive same message and code error's out