Macro Recording Issue - Help Appreciated

[juuribe]

Hi,

I have been trying to record a macro to do the following:

1. Create a new column (always before the last column of the table)
2. Drag the formula from the previous column to the new one.

Example:

Columns: A, B, C, D, E

Need to create a new column always before the last column (E) so now we have E as F and a new blank column in E: A, B, C, D, E(blank), F (with E's data).

Then drag the formulas from column D to the new E (blank).

The problem that I am having is that the Recorded Macro is always creating the new column from the column E. So the first time it works correctly, getting A, B, C, D, E, F. Then, what I get is A, B, C, D, G (new), E, F... then, A, B, C, D, G, H (new), E, F.

Would really appreciate it if anyone could give me some guidance on how to get this working.

Many thanks!

 

[mole999]

Its basically doing what you told it to, not intelligent enough to know ytou want to do anything different, macros are about reducing repetive tasks. Post your code here in tags, it maybe there is a way to fix it, describe also what you want to happen

 

[lancer102rus]

The common code is like this:

Sub doThis()
    Dim lRow&, lCol&
        
    With ActiveSheet
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Columns(lCol).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1, lCol - 1).Resize(lRow, 1).AutoFill Destination:=.Cells(1, lCol - 1).Resize(lRow, 2), Type:=xlFillDefault
    End With
End Sub

In this example we have no table headings and data starts from the first row. Count of rows in table we calculate by "A" column.