Erwin65456
New Member
 Joined
 Dec 19, 2020
 Messages
 8
 Office Version

 365
 Platform

 Windows
Any Assistance is greatly appreciated.
I have 2 sheets. sheet 1 has a variable list of numbers in column A. Sheet 2 has a variable list of names in Column A. the formula moves the names to the right based on the number in each corresponding cell in sheet 1. once this is done, the formula then inserts a header row in sheet 2. it then places the first 3 headers in cells A1:C1 "Level 1" "Level2" "Level3" this all works.
what does not work is: it needs to autofill to the right the ascending 'Level' headers to variable last column that contains data.
I have managed to get this right with a normal 'fill' but cannot get this right with 'autofill' after hours of bashing my head against my keyboard. it needs to auto fill as the ascending pattern in the headers must continue.
The code:
Application.ScreenUpdating = False
Dim i As Long
Dim X As Long
Set Order = Worksheets("sheet1")
Set Lvl = Worksheets("sheet2")
'Set H = Order.Application.WorksheetFunction.Max(Range("A:A"))  instead of finding the last column in sheet 2 with data, I tried to find the maximum value in sheet 1 which the names in sheet 2 would move by, as it was more efficient, I could not get this right either, but even if I managed to get this right, I would run into the same problem below in any case.
For i = Order.Range("A1") To Order.Cells(Rows.Count, "A").End(xlUp).Row
Lvl.Cells(i, 1).Resize(, Order.Cells(i, 1).Value).Insert Shift:=xlToRight
Next
Lvl.Range("A1").EntireRow.Insert
Lvl.Range("A:A").Delete
Lvl.Range("A1").FormulaR1C1 = "Level 1"
Lvl.Range("B1").FormulaR1C1 = "Level 2"
Lvl.Range("C1").FormulaR1C1 = "Level 3"
X = Lvl.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Lvl.Range("A1:C1").Select
'Set Y = Lvl.Range("A1" & Cells(Range("A1").Row, X)) I tried this, did not work
'Set Z = Lvl.Range("A1:C1")
Range("A1:C1").Select
'Lvl.Range(Selection, Cells(ActiveCell.Row, X)).FillRight  this worked, but all headers are "Level 1" the pattern does not ascend.
Selection.AutoFill Destination:=Range("A1" & Cells(Range("A1").Row, X)), Type:=xlFillDefault  Runtime error '1004': Autofill method of range class failed
Application.ScreenUpdating = True
I have 2 sheets. sheet 1 has a variable list of numbers in column A. Sheet 2 has a variable list of names in Column A. the formula moves the names to the right based on the number in each corresponding cell in sheet 1. once this is done, the formula then inserts a header row in sheet 2. it then places the first 3 headers in cells A1:C1 "Level 1" "Level2" "Level3" this all works.
what does not work is: it needs to autofill to the right the ascending 'Level' headers to variable last column that contains data.
I have managed to get this right with a normal 'fill' but cannot get this right with 'autofill' after hours of bashing my head against my keyboard. it needs to auto fill as the ascending pattern in the headers must continue.
The code:
Application.ScreenUpdating = False
Dim i As Long
Dim X As Long
Set Order = Worksheets("sheet1")
Set Lvl = Worksheets("sheet2")
'Set H = Order.Application.WorksheetFunction.Max(Range("A:A"))  instead of finding the last column in sheet 2 with data, I tried to find the maximum value in sheet 1 which the names in sheet 2 would move by, as it was more efficient, I could not get this right either, but even if I managed to get this right, I would run into the same problem below in any case.
For i = Order.Range("A1") To Order.Cells(Rows.Count, "A").End(xlUp).Row
Lvl.Cells(i, 1).Resize(, Order.Cells(i, 1).Value).Insert Shift:=xlToRight
Next
Lvl.Range("A1").EntireRow.Insert
Lvl.Range("A:A").Delete
Lvl.Range("A1").FormulaR1C1 = "Level 1"
Lvl.Range("B1").FormulaR1C1 = "Level 2"
Lvl.Range("C1").FormulaR1C1 = "Level 3"
X = Lvl.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Lvl.Range("A1:C1").Select
'Set Y = Lvl.Range("A1" & Cells(Range("A1").Row, X)) I tried this, did not work
'Set Z = Lvl.Range("A1:C1")
Range("A1:C1").Select
'Lvl.Range(Selection, Cells(ActiveCell.Row, X)).FillRight  this worked, but all headers are "Level 1" the pattern does not ascend.
Selection.AutoFill Destination:=Range("A1" & Cells(Range("A1").Row, X)), Type:=xlFillDefault  Runtime error '1004': Autofill method of range class failed
Application.ScreenUpdating = True