Use VBA to Autofill a Row until the end of the number of data in another row

Makwa2070 

Maybe this will be a simplified version of the below even though it doesn't solve the entire problem

How do i make this below  VBA code that sort two columns by "sales unit this year " to be able to run on any worksheet ,please note that i got this code from running macro now i want to modify it

code is failing to run on a new worksheet can this be because of the  name difference?

   With ActiveWorkbook.Worksheets("ME5").Sort

        .SetRange Range("A4:H661325")

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

Haytham Amairah hi I ran a macro to sort a file, the last step is to sort data by "sales unit this year "means data from Column  H must Goto Column  G. to do this  i used sort function to move all the data to column G now to move the remaining data from H to G I went to the last cell of G that has data , move one cell below it to the empty cell the use a formula to bring data from H(e.g lastE

mptycellOF G=H56)  and tried to autofill this to the equivalent  last H cell that has data

this is the code that i added on top of the quote that was recorded by VBA when i was recoding a macro

Range("A4").Select
Selection.End(xlToRight).Select
Range("G4").Select
Selection.End(xlDown).Select
Range("G1059044").Select 'go all the way down to cell 1059044
Selection.End(xlUp).Select 'then co the way up until you find data
lastCell = ActiveCell.Address(True, False) 'trying to give the lastcell the address of the active cell because last cell always changes according to the length of the data
ActiveCell.Offset(1, 0).Select 'from the active cell which is supposed to be the last cell go one cell down
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[1]"
Selection.AutoFill Destination:=Range("lastCell" & Range("G4" & Rows.Count).End(xlUp).Row) ' want it to select the dynamic cell and autofill the formula
Range(Selection, Selection.End(xlDown)).Select

NickNick33

Hi,

After I reproduced this issue, I noticed that the error also occurs even if you have two records in the imported file.

If you have one or two rows in the imported file, there is no need to use the AutoFill method in the macro because all rows in the table are already filled.

If you use it in this case, there will be an error!

The solution is to make the code smart somehow to see if the imported file has less than three rows and if so, skip the autofill process to prevent the error.

This is what I suggest to overcome this issue:

Sub PrepFile()
'
' PrepFile Macro
'

'
Sheets("Sheet1").Select
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=current;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [current]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "current"
.Refresh BackgroundQuery:=False
End With
Range("K10").Select
ActiveCell.FormulaR1C1 = "Shipment ID"
Range("L10").Select
ActiveCell.FormulaR1C1 = "Shipment Name"
Range("B2").Select
Selection.Copy
Range("K11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("L11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("10:10").Select
Application.CutCopyMode = False
Selection.Copy
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("2:10").Select
Range("A10").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Columns("K:L").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight

If Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 1 Then
Range("A1").Select
Exit Sub
ElseIf Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 2 Then
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Exit Sub
End If

Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
End Sub

Please replace your old macro with it.

Hope that helps

Haytham Amairah 

Here are the macro steps:

• Imports the source file I provided,
• adds 2 columns (Shipmentid and Name) in addition to the columns in the source file.
• It takes what is in B1 in the source as Shipmentid and copies down the new column A
• It takes what is in B2, copies down the new column B
• Deletes the top rows up to the headings so that the whole file becomes one uniform table. 

This works fine except when there is a single in the source. I have attached both the Excel file and the different files that are used as the source. 

The macro is created to import the file called current.tsv. We just swap the files we want to import by renaming them "current.tsv". So I provided one sith single line and another with multiple. The one with multiple works fine but single one returns error as the VBA seems to have a problem with it. 

Curious situation. I hope this helps. 

NickNick33

Hi,

This is what I got after opening your file:

Sorry, but the data isn't clear and I'm not sure what you're trying to do!

Based on your macro, you want to fill down Column A & B based on the number of rows in column E ??

Haytham Amairah 

Sure, it is attached. Th eoriginal file is a tsv file but the upload tool would not accept tsv so I had to change it to csv. You will need to change it to tsv for the Excel file to read it. 

Thank you 

NickNick33 

 Hi,

To figure out the problem, please provide a sample of the data you work on.

Regards

Haytham Amairah 

Hi Haytham,

I have used your code in my macro when it imports from a text file and reorganizes the records. As part of it, it is supposed to copy down rows of a table as below:

ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

This works nicely but if there is a single line of record in the file, it copies the row but then it adds a second row, copies down the first row in column A and B then returns a VB Error as shown in the attached screenshot.

Any ideas how to deal with it?

Thanks

Nick