Pfthb

What I am trying to do is to take values from specific cells and make them a filldown column. I have multiple worksheets with different values in them.

This code is working as expected with one worksheet :

Sub Formatting_one()

 Range("A12").Value = Range("M6").Value
 Range("A12:A" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

End Sub

Then, I started to try the same thing but with looping through worksheets. That's the point I am stuck with. Here is my code for this :

Sub Formatting_many()

 Dim ws As Worksheet

 For Each ws In ActiveWorkbook.Worksheets
 ws.[A1].Resize(, 6).EntireColumn.Insert

 ws.Range("A12").Value = ws.Range("M6").Value
 ws.Range("A12:A" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

 ws.Range("B12").Value = ws.Range("M7").Value
 ws.Range("B12:B" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

 ws.Range("C12").Value = ws.Range("M8").Value
 ws.Range("C12:C" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

 ws.Range("D12").Value = ws.Range("I5").Value
 ws.Range("D12:D" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

 ws.Range("E12").Value = ws.Range("I4").Value
 ws.Range("E12:E" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

 ws.Range("F12").Value = ws.Range("I6").Value
 ws.Range("F12:F" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

 ws.Range("G12").Value = ws.Range("I7").Value
 ws.Range("G12:G" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

 Next ws

End Sub

I did a step by step, and the result is that the cell is copied in the first cell of the filldown range cells but then it's deleted. Can somebody help ?

The issue …

… is that you look for the last used row in column G

Cells(Rows.Count, 7).End(xlUp).Row

But since you added 6 columns with ws.[A1].Resize(, 6).EntireColumn.Insert column G is now empty, so the last used row is 1

and you actually run

ws.Range("A12:A1").FillDown

which takes the empty cell from A1 and fills it down until A12 (so your inserted value in A12 gets removed).

Solution

After inserting your original column G moved to

ws.Cells(ws.Rows.Count, 7 + 6).End(xlUp).Row