FillDown Code when only one line of Data

F

figgylynn1023

New Member
Joined
Jul 21, 2011
Messages
24
I have three data sets that get put together with a macro. Before they are copied onto the same sheet the macro codes them by their set in column A. However, when I have only one line of data from one of the sets, it copies the set ID down the entire sheet, and then an error comes up in the copy and paste section of the code. Is there a way to stop this from happening?

Here's my code so far:
Rich (BB 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

I really want it to copy the Org ID down the inserted column A for each line, but when it's only one line, I don't want it to copy down the whole sheet, I just want it on the one line. Ideas?
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
I'm hoping a little clarification might help get some responses, as this issue does create problems when running my reports and I haven't found a solution yet.

Sometimes the text files I am loading do have only one line of data, but the way my code is set up to "FillDown" the fields I input after opening those text files create issues when only one line exists.

Instead of stopping at line one in those cases, it continues to paste/FillDown the ENTIRE worksheet, causing problems when it tries to copy/paste that data into the cumulative worksheet.

Is there a way to adjust this code to FillDown when there is more than one line of data, but to stop at only one line when there IS only one line?

Rich (BB code): 
' 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


I would really truly be grateful for some advice on this. Thanks.
 
Upvote 0
Ended up getting an answer to this from another source and some testing on my own of a few things.

Here's what ended up working:
Code: 
Windows("bf" & Day & "_5298.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).Value="5298"
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
357
willow1985
willow1985
G
  • Question Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
404
GK039
G
D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
153
druck21
D
J
  • Question Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
423
shknbk2
S
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
449
CapRavOr
C

Forum statistics

Threads
1,224,519
Messages
6,179,263
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top