Results 1 to 4 of 4

Help: FillDown doesn't work properly when only one line of data

Threaded View

  1. #1
    Registered User
    Join Date
    07-28-2011
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2007
    Posts
    7

    Help: FillDown doesn't work properly when only one line of data

    I'm having problems with the code I've been running in my macro. The problem areas run from " 'Insert Org ID" to " 'Moving columns" (I tried highlighting it red but it didn't work). Essentially, the code pulls data from three text files, adds columns, copies all the data from the last two text files to the first text file, adds and moves columns there, and then moves columns A:H of the final set to the template.

    The problem is that when the text files have only one line of data, the commands to copy and FillDown to the End of the Selection end up copying down the ENTIRE worksheet, and then when it goes to copy and paste, it brings up an error saying "Copy area is larger than paste area", and stops the macro.

    I have tried multiple times to fix this, but nothing I have done works. I really need this to work whether there is one line of data or 10,000 lines of data, or whatever ends up being the case. Ideas?

    Here's my code:
    Sub DP_Report()
    '
    ' DP_Report Macro
    ' Convert DP text files to Excel, formats and copies to template.
    '
    ' Keyboard Shortcut: Ctrl+Shift+D
    '
    Dim LastRow As Long
    Dim Day As String
     
    Day = InputBox("Date of File (mmddyy):")
    LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
    ' Opening Text Files
    Workbooks.OpenText Filename:= _
    "\\Drive\Folder\dp" & Day & "_113.txt" _
    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
    Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
    , 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
    Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
    TrailingMinusNumbers:=True
    Workbooks.OpenText Filename:= _
    "\\Drive\Folder\dp" & Day & "_2856.txt" _
    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
    Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
    , 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
    Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
    TrailingMinusNumbers:=True
    Workbooks.OpenText Filename:= _
    "\\Drive\Folder\dp" & Day & "_5298.txt" _
    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
    Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
    , 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
    Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2)), _
    TrailingMinusNumbers:=True
    ' Insert Org ID
    Windows("dp" & Day & "_113.txt").Activate
    Range("A1").EntireColumn.Insert
    Range("A1").FormulaR1C1 = "1000113"
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(0, -1).FillDown
    Windows("dp" & Day & "_2856.txt").Activate
    Range("A1").EntireColumn.Insert
    Range("A1").FormulaR1C1 = "1002856"
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(0, -1).FillDown
    Windows("dp" & Day & "_5298.txt").Activate
    Range("A1").EntireColumn.Insert
    Range("A1").FormulaR1C1 = "1005298"
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(0, -1).FillDown
    ' Copy to 113 sheet
    Windows("dp" & Day & "_2856.txt").Activate
    ActiveSheet.UsedRange.Copy
    Windows("dp" & Day & "_113.txt").Activate
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    Windows("dp" & Day & "_5298.txt").Activate
    ActiveSheet.UsedRange.Copy
    Windows("dp" & Day & "_113.txt").Activate
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    ' Closing Extra Windows
    Windows("dp" & Day & "_2856.txt").Activate
    ActiveWindow.Close
    Windows("dp" & Day & "_5298.txt").Activate
    ActiveWindow.Close
    ' Insert Report Type
    Windows("dp" & Day & "_113.txt").Activate
    Range("A1").EntireColumn.Insert
    Range("A1").FormulaR1C1 = "DP"
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(0, -1).FillDown
    ' Moving Columns
    Range("A1").EntireColumn.Insert
    Range("H1").EntireColumn.Copy
    Range("A1").EntireColumn.PasteSpecial
    Range("C1").EntireColumn.Insert
    Range("E1").EntireColumn.Copy
            Range("C1").EntireColumn.PasteSpecial
    ' Move to Template Sheet
        ActiveSheet.UsedRange.Copy
        Windows("Report_Comparison_Template_1.3.xlsm").Activate
        ActiveSheet.Paste
    ' Close 113 Window
        Windows("dp" & Day & "_113.txt").Activate
        ActiveWindow.Close
    Last edited by Figgylynn1023; 08-01-2011 at 04:54 PM. Reason: color did not show up

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