+ Reply to Thread
Results 1 to 2 of 2

run time error 1004

Hybrid View

  1. #1
    Registered User
    Join Date
    08-20-2015
    Location
    bulawayo,zimbabwe
    MS-Off Ver
    2013
    Posts
    3

    run time error 1004

    sorry i am not experienced in VBA. I have 2 workbooks (saleman .xls and DailyReport.xlsm). Saleman .xls is generated by my POS daily by over writing the existing data in the file. DailyReport.xlsm has functions to generate reports using data copied from saleman .xls. I am trying make a vba script that will copy data from saleman .xls ( excluding headings) and paste to the last row of the table in sheet 1 of DailyReport.xlsm. i tried the code below and received thiS error message "run time error 1004 application-defined or object-defined error".
    Sub CopyReps_btn()
    
    
    Dim WS, WS2 As Worksheet
    Dim LastRow As Long
    'Dim lastCell As Range
    Dim LastCellRowNumber As Long
    Dim sPath As String, sFile As String
    
    
    Dim wb As Workbook, wb2 As Workbook
    Dim vFile As Variant
    
    'Set source workbook
    Set wb = ThisWorkbook
    Set wb = ActiveWorkbook
    Set WS = ActiveSheet
    With WS
        Set lastCell = .Cells(.Rows.Count, "C").End(xlUp)
        LastCellRowNumber = lastCell.Row + 1
    End With
    
    
    'Set selectedworkbook
    sPath = "C:\Sales2016APD\DownloadAPD\"
        sFile = sPath & "salesman.xls"
    Workbooks.Open (sFile)
    Set wb2 = ActiveWorkbook
    Set WS2 = wb2.Sheets("salesman")
    
    'last row
      With WS2
            LastRow = .Cells(.Rows.Count, A).End(xlUp).Row
    'Select cells to copy
    
    wb2.Worksheets("Output").Range("A2", "N" & LastRow).Select
    Selection.Copy
    End With
    'Go back to original workbook you want to paste into
    wb.Activate
    
    'Paste starting at the last empty row
    wb.Worksheets("DailyReport.xlsm").Range("C" & LastCellRowNumber).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    'Close and save the workbook you copied from
    wb2.Close
    
    End Sub
    Last edited by misodd; 08-26-2015 at 07:15 AM.

  2. #2
    Registered User
    Join Date
    08-20-2015
    Location
    bulawayo,zimbabwe
    MS-Off Ver
    2013
    Posts
    3

    Re: run time error 1004

    sorry i am not experienced in VBA. I have 2 workbooks (saleman .xls and DailyReport.xlsm). Saleman .xls is generated by my POS daily by over writing the existing data in the file. DailyReport.xlsm has functions to generate reports using data copied from saleman .xls. I am trying make a vba script that will copy data from saleman .xls ( excluding headings) and paste to the last row of the table in sheet 1 of DailyReport.xlsm. i tried the code below and received thiS error message "run time error 1004 application-defined or object-defined error".
    Sub CopyReps_btn()
    
    Dim WS, WS2 As Worksheet
    Dim LastRow As Long
    'Dim lastCell As Range
    Dim LastCellRowNumber As Long
    Dim sPath As String, sFile As String
    
    
    Dim wb As Workbook, wb2 As Workbook
    Dim vFile As Variant
    
    'Set source workbook
    Set wb = ThisWorkbook
    Set wb = ActiveWorkbook
    Set WS = ActiveSheet
    With WS
        Set lastCell = .Cells(.Rows.Count, "C").End(xlUp)
        LastCellRowNumber = lastCell.Row + 1
    End With
    
    
    'Set selectedworkbook
    sPath = "C:\Sales2016APD\DownloadAPD\"
        sFile = sPath & "salesman.xls"
    Workbooks.Open (sFile)
    Set wb2 = ActiveWorkbook
    Set WS2 = wb2.Sheets("salesman")
    
    'last row
      With WS2
            LastRow = .Cells(.Rows.Count, A).End(xlUp).Row
    'Select cells to copy
    
    wb2.Worksheets("Output").Range("A2", "N" & LastRow).Select
    Selection.Copy
    End With
    'Go back to original workbook you want to paste into
    wb.Activate
    
    'Paste starting at the last empty row
    wb.Worksheets("DailyReport.xlsm").Range("C" & LastCellRowNumber).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    'Close and save the workbook you copied from
    wb2.Close
    
    End Sub

+ 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. [SOLVED] Sort Macro Run-time error '1004': Application/Object-defined error.
    By sam1212 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2014, 10:05 AM
  2. Addon error : Run-time error '1004': Method 'MacroOptions' of object '_Application' failed
    By jtcoleman in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 02-05-2014, 12:23 PM
  3. Replies: 4
    Last Post: 11-15-2013, 05:03 PM
  4. VBA Code...error = run time error 1004 autofilter method of range class failed
    By Dariusd7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2013, 04:49 PM
  5. Error "run-time Error '1004': General Odbc Error
    By D4WNO77 in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  6. Excel Macro Error - Run time error 1004 - Paste method of worksheet class failed
    By kvflynn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 10:51 AM
  7. [SOLVED] run-time error '1004': Application-defined or object-deifined error
    By rich5665@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2005, 05:05 PM

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