AutoFill to right to variable destination

[Erwin65456]

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

 

[Erwin65456]

Apologies, the box in Column L is from Sheet1 not Sheet2

 

[Erwin65456]

apologies, I'm new here.

here is the code in the correct format

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")) 


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 

Selection.AutoFill Destination:=Range("A1" & Cells(Range("A1").Row, X)), Type:=xlFillDefault

Application.ScreenUpdating = True

 

[mohadin]

Try

    Selection.AutoFill Destination:=Range("A1").Resize(, 7), Type:=xlFillDefault

 

[Erwin65456]

Try

    Selection.AutoFill Destination:=Range("A1").Resize(, 7), Type:=xlFillDefault

this did work, but it is a single use. the length of numbers and names is variable which is the problem.

you gave me an idea though. I swapped 7 with X, and it worked.

thank you very much Mohadin, I now have a fully working code.

Application.ScreenUpdating = False
Dim i As Long
Dim X As Long

Set Order = Worksheets("sheet1")
Set Lvl = Worksheets("sheet2")

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

Selection.AutoFill Destination:=Range("A1").Resize(, X), Type:=xlFillDefault

Application.ScreenUpdating = True

 

[Erwin65456]

Application.ScreenUpdating = False
Dim i As Long
Dim X As Long

Set Order = Worksheets("sheet1")
Set Lvl = Worksheets("sheet2")
    
    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

Selection.AutoFill Destination:=Range("A1").Resize(, X), Type:=xlFillDefault
    
Application.ScreenUpdating = True

 

[mohadin]

Glad you sorted out
Be happy