Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Copying to cell A1 - Invalid Compiler Error

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Calgary
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a peice of code that allows me to copy a range of A29:T53 from an excel file to a cell A1 of a new excel sheet. It continues to give me an Invalid Qualifier Error at the "copy cell values" action.

    Here is the code and the error: The invalid qualifier is in red

    Option Explicit

    Sub Folder_Workbooks()

    Dim wkbCopy As Excel.Workbook
    Dim Path$, Workbook$, RangeCopy$
    Dim Sheet%

    Application.DisplayAlerts = False
    Application.EnableEvents = False

    'set range address to copy from/to
    RangeCopy$ = "K29:T53"
    Path$ = "W:ComptrolCorp_RepMONTHEND2002Monthly StewardshipOPEX"
    Workbook$ = Dir(Path$ & "*01*.xls")

    'loop all workbooks in folder
    Do While Not Workbook$ = ""

    'assign sheet index to copy data to
    Sheet% = Sheet% + 1

    'open workbook to copy from
    Set wkbCopy = GetObject(Path$ & Workbook$)

    'copy cell values
    ThisWorkbook.Sheets(Sheet%).Range(Cells(1), _
    Cells(RangeCopy$.Rows.Count, RangeCopy$.Columns.Count)) _
    = wkbCopy.Sheets(1).Range(RangeCopy$).Value

    wkbCopy.Close
    Set wkbCopy = Nothing

    'try to find next workbook in folder
    Workbook$ = Dir
    Loop

    Application.EnableEvents = True
    Application.DisplayAlerts = True


    End Sub

    Any help would be appreciated. Thanks

    _________________
    Help Me Obi Wan Kenobi, You're My Only Hope.

    [ This Message was edited by: izzyq on 2002-03-26 08:05 ]

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Sorry. I'm not Obi wan, but try this.
    Regards,

    Sub Folder_Workbooks() 'ReconstructionByColo
    Dim wkbCopy As Workbook, path$, Workbook$, RangeCopy$, Sheet%
    With Application
    .DisplayAlerts = False
    .EnableEvents = False
    RangeCopy$ = "K29:T53"
    'Ithink Need "/"
    path$ = "W:ComptrolCorp_RepMONTHEND2002Monthly StewardshipOPEX/"
    Workbook$ = Dir(path$ & "*01*.xls")
    Do While Not Workbook$ = ""
    Sheet% = Sheet% + 1
    Set wkbCopy = GetObject(path$ & Workbook$)
    'necessary to clarify which sheet is referred to
    With wkbCopy.Sheets(1).Range(RangeCopy$)
    ThisWorkbook.Sheets(Sheet%).Cells(1, 1) _
    .Resize(.Rows.Count, .Columns.Count).Value = .Resize.Value
    End With
    wkbCopy.Close
    Set wkbCopy = Nothing
    Workbook$ = Dir
    Loop
    .EnableEvents = True
    .DisplayAlerts = True
    End With
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •