AutoFill to right to variable destination

Erwin65456

New Member
Joined
Dec 19, 2020
Messages
8
Office Version
  1. 365
Platform
  1. 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
 

Attachments

  • Screenshot (19).png
    Screenshot (19).png
    31.4 KB · Views: 6

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Erwin65456

New Member
Joined
Dec 19, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Apologies, the box in Column L is from Sheet1 not Sheet2
 

Erwin65456

New Member
Joined
Dec 19, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
apologies, I'm new here.

here is the code in the correct format

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


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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Try
VBA Code:
    Selection.AutoFill Destination:=Range("A1").Resize(, 7), Type:=xlFillDefault
 

Erwin65456

New Member
Joined
Dec 19, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try
VBA Code:
    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.

VBA 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

New Member
Joined
Dec 19, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
VBA 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
 
Solution

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Glad you sorted out
Be happy
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,487
Members
416,031
Latest member
Omega67

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
Top