+ Reply to Thread
Results 1 to 3 of 3

Copy row contaning matching value to another workbook - Subscript out of range error

Hybrid View

VBA FTW Copy row contaning matching... 02-28-2013, 02:41 PM
arlu1201 Re: Copy row contaning... 03-01-2013, 02:03 PM
VBA FTW Re: Copy row contaning... 03-04-2013, 12:55 PM
  1. #1
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Copy row contaning matching value to another workbook - Subscript out of range error

    Hey again,
    I'm having a little trouble sending a row to another workbook, was wondering if someone could offer a little advice as to what I'm doing wrong? I have 3 workbooks, A,B, and C. Workbook A contains my code. The code needs to open workbook B and find a value in column BF. If that value is found, it is then to search a directory for a workbook with today's date...this is workbook C. If workbook C exists, I need to open it and copy the row that had the matching value from workbook B to the first blank row in workbook C.

    I think I have the searching/opening/matching part down. I'm running into a 'Subscript out of range' error when trying to set the values of the first blank row in workbook C equal to the values of the row from workbook B. Here is my code in full
    Option Explicit
    Sub MoveToPublish()
    Dim prepublishPath As String
    Dim publishPath As String
    Dim smartNum As String
    Dim line
    Dim apprvdAcctFile As String
    Dim acctApprovedFolder As String
    Dim unapprvedAcctFile As String
    Dim Rng
    
    If Sheet1.Range("AwardLetter").Value = "" Then
        MsgBox "Please select a file.", vbInformation, ""
        Exit Sub
    End If
    
    smartNum = ThisWorkbook.Sheets("Main").Range("J4").Value
    
    unapprvedAcctFile = "C:\Users\Eric\Desktop\Accounting Output\" & Sheet1.Range("L4").Value & ".xlsx"
    'open accounting file for selected school
    Workbooks.Open Filename:=unapprvedAcctFile
            
            'search for smart number in accounting file
            With Sheets("Sheet1").Range("BF:BF") 'searches all of column BF
            Set Rng = .Find(What:=smartNum, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
            
    line = Application.Match([smartNum], Sheets("Sheet1").[BF:BF], 0)
    acctApprovedFolder = "C:\Users\Eric\Desktop\Accounting Output\Approved For Import\"
    apprvdAcctFile = acctApprovedFolder & Format(Now, "mm-dd") & " " & ThisWorkbook.Sheets("Main").Range("AcctOutputFile").Value & ".xlsx"
    unapprvedAcctFile = "C:\Users\Eric\Desktop\Accounting Output\" & Sheet1.Range("L4").Value & ".xlsx"
      
      If Dir(apprvdAcctFile) <> "" Then
        Workbooks.Open Filename:=apprvdAcctFile
        ActiveWorkbook.Cells(Range("A65000").End(xlUp).Row + 1, 1).Resize(, 3) = Workbooks(unapprvedAcctFile).Sheets("Sheet1").[A1].Item(line, 1).Resize(, 3).Value
      Else
        Workbooks.Add
        ActiveWorkbook.SaveAs (apprvdAcctFile)
        ActiveWorkbook.Cells(Range("A65000").End(xlUp).Row + 1, 1).Resize(, 3) = Workbooks(unapprvedAcctFile).Sheets("Sheet1").[A1].Item(line, 1).Resize(, 3).Value
      End If
    
    prepublishPath = "C:\Users\Eric\Desktop\Award_Letters\Excel pre_publish\" & Sheet1.Range("AwardLetter").Value
    publishPath = "C:\Users\Eric\Desktop\Award_Letters\Publish\" & Sheet1.Range("AwardLetter").Value
    Name prepublishPath As publishPath
    
    'create log of uploaded files
    Sheet1.Range("H" & Rows.Count).End(xlUp).Offset(1) = Sheet1.Range("AwardLetter").Value
    
    Call fileSrch
    Else
    MsgBox "This record could not be found on the accounting file."
    End If
    End With
    End Sub
    I'm getting the error on this line
    ActiveWorkbook.Cells(Range("A65000").End(xlUp).Row + 1, 1).Resize(, 3) = Workbooks(unapprvedAcctFile).Sheets("Sheet1").[A1].Item(line, 1).Resize(, 3).Value
    Any thoughts on what I'm doing wrong? If you have a different method to suggest I'm all ears as well

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Copy row contaning matching value to another workbook - Subscript out of range error

    You have not specified the sheet name. You have said "Activeworkbook.Cells" but you are missing the sheet name there.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Re: Copy row contaning matching value to another workbook - Subscript out of range error

    Go it sorted, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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