+ Reply to Thread
Results 1 to 5 of 5

Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defined

  1. #1
    Registered User
    Join Date
    03-16-2022
    Location
    india
    MS-Off Ver
    2021
    Posts
    2

    Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defined

    Hi am new to VBA, Could you please help on the below code.



    I have 2 workbooks and i want to copy the columns from one to another with the matching header.



    It shows an error in the following line as (Runtime Error - 1004 --> Application-defined or object Defined error)



    tWB.sheets("Main").Range(Cells(TLRow, TCol)) = aWB.sheets(1).Range(Cells(2, SCol), Cells(SLRow, SCol))





    Sub Pull ()



    Dim FileName() as Variant, nw as integer, i as integer

    Dim tWB as Workbook, aWB as Workbook

    Dim hcell as Range, Header as Range

    Dim SCol as Integer, TCol as Integer, SLRow as Integer, TLRow as Integer, SIndex as Integer,

    Dim TIndex as Integer



    Set tWB = ThisWorkbook

    FileName = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls; *.xlsm),*.xls;*.xlsm", MultiSelect:=True)

    nw = UBound(FileName)



    Set Header = tWB.sheets("List").Range("A1:A" & tWB.Sheets("List").Range("A" & Rows.Count).End(xlUp).Row)



    For i = 1 to nw

    Workbooks.Open FileName(i)

    Set aWB = ActiveWorkbook

    For each hcell in Header

    SCol = Application.Match(hcell.value, aWB.Sheets(1).Rows(1),0)

    TCol = Application.Match(hcell.Offset(0,1).value, tWB.Sheets("Main").Rows(1),0)

    SLRow = aWB.Sheets(1).Cells(aWB.Sheets(1).Rows.Count, "A").End(xlUP).Row
    TLRow = tWB.Sheets("Main").Cells(tWB.Sheets(1).Rows.Count, TCol).End(xlUP).Row + 1
    SIndex = Split(aWB.sheets(1).cells(1,SCol).Address, "$")(1)

    TIndex = Split(tWB.sheets("Main").cells(1,TCol).Address, "$")(1)


    tWB.sheets("Main").Range(Cells(TLRow, TCol)) = aWB.sheets(1).Range(Cells(2, SCol), Cells(SLRow, SCol))

    Next hcell

    Next i

    End Sub



    Another Method:

    Also i have tried to convert the column index number to Letter but am not sure what the error is since the code shows no error but the output is not coming



    tWB.Sheets("Main").Range(TIndex & TLRow) = aWB.sheets(1).Range((SIndex & 2), Range(SIndex & SLRow))

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defi

    try

    Please Login or Register  to view this content.
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defi

    Maybe:
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    03-16-2022
    Location
    india
    MS-Off Ver
    2021
    Posts
    2

    Re: Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defi

    It Worked.. Thank you so much

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defi

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-20-2021, 07:45 AM
  2. Runtime error 1004 application-defined object-defined
    By stevoDE in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2017, 09:58 AM
  3. Replies: 5
    Last Post: 08-19-2015, 07:10 PM
  4. [SOLVED] Error 1004: Application-defined or object-defined error on Range(Cells(x,y)) syntax
    By winch in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-04-2013, 11:02 AM
  5. Replies: 0
    Last Post: 05-14-2012, 11:59 PM
  6. RE: Runtime error 1004- application defined or object defined erro
    By Novice in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2006, 05:40 PM
  7. [SOLVED] RE: Runtime error 1004- application defined or object defined erro
    By Jim Thomlinson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-06-2006, 05:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1