Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Pasting to A1

  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 this code which copies the range K20:T50 from 10 XL files and pastes them to an new work book. Currently it will paste the copied data to K20:T50 in the new sheets and I need it start copying at A1.

    The code I have right keeps giving me an "invalid qualifier error" for the "RangeCopy$" variable. The pertinent code is under the "Copy Cell Values" tag.



    Sub Folder_WorkbooksCopy()
    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(RangeCopy$).Value = _
    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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try just changing this code:


    'set range address to copy from/to
    RangeCopy$ = "K29:T53"


    To


    'set range address to copy from/to
    RangeCopy$ = "A1"


    HTH

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

    Default

    Changing this code changes the information that is initially copied. Instead of copying the deired K29:T53, it will only copy cell A1. It will copy it to A1 but it is not the correct information.

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
  •