-1

It is similar to these two question:

Enter a new line and copy formula from cells above

excel vba insert row with formatting

I have seen this question, but I don't think it solves my problem.

Aqqqq
  • 816
  • 2
  • 10
  • 27

3 Answers3

0

Well, at least you have done some research, thus ... I will show about half of the answer - the format.

Take a look at the following:

Option Explicit

Public Sub CopyFormatToTheLeft(lngColumnNumber As Long)

    If lngColumnNumber < 2 Then
        MsgBox "Nope!"
        Exit Sub
    End If

    Columns(lngColumnNumber).Copy
    Columns(lngColumnNumber - 1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Cells(1, 1).Select 'no selection - no fun

End Sub

Public Sub TestMe()

    CopyFormatToTheLeft 3

End Sub

You will transfer the format of column C to column B. To transfer the formulas, take a look af the different posibilities of xlPaste.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Does your answer insert a new column with a format of Column C or merely copy Column C to B? Because I don't want the content in Column B (in your example) to be replaced. – Aqqqq Aug 11 '17 at 07:21
-1

If you wanna insert this should do the trick, might not be the cleanest way ;) You can change the Offset as you want.

Sub ColumnOffsetCopy()

Application.ScreenUpdating = False
Application.CutCopyMode = False

With ActiveSheet
    ActiveCell.EntireColumn.Insert
    ActiveCell.EntireColumn.Formula = ActiveCell.Offset(0, 1).EntireColumn.Formula
    ActiveCell.Offset(0, 1).EntireColumn.Copy: ActiveCell.EntireColumn.PasteSpecial xlPasteFormats
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
krib
  • 569
  • 4
  • 14
  • Would your code work if I replace "ActiveCell" as a specific cell? (e.g. sheetname.Cells(0, 9)) – Aqqqq Aug 11 '17 at 07:42
  • @Aqqqq the fastest fix (imo) is just to add `.Range("I1").Select` after `With ActiveSheet` if its column 9 you want. if you want to change the column you get formats and formulas adjust `Offset`. – krib Aug 11 '17 at 09:20
  • What do you mean by " if its column 9 you want. if you want to change the column you get formats and formulas adjust Offset" I don't want to change the column from which I got formats and formulas from (if that is what you are talking about). – Aqqqq Aug 11 '17 at 12:03
  • @Aqqqq You posted `(e.g. sheetname.Cells(0, 9))` the `9` refers to col 9. Just use `.Range("I1").Select` after `With ActiveSheet`. just make sure to use the letter of the column in `Range("yourcolumn and row")` – krib Aug 11 '17 at 12:06
  • Would you mind if change your answer in such a way that it is applicable to a cell defined by its row and column number instead of simply "ActiveCell"? Same apply to the sheet (instead of Activesheet). I am not sure if I understand your answer correctly. – Aqqqq Aug 18 '17 at 08:33
  • I just edited your answer to my best understanding of your comment. This is the code I tried and it did not work. – Aqqqq Aug 18 '17 at 08:37
  • the point of the `ActiveCell`is that makes it more versatile. Meaning you can Select what ever cell in what ever column and you get you desired result. Why change it? If this helped you to get Your desired result either Accept it or just use it – krib Aug 18 '17 at 08:37
  • In my case it is a step of a automatation process. (so far I have never needed such versatalitity.) The user is not going to select a cell in the middle of that process. No it did not help me to get my desired result. As I said, it did not work. – Aqqqq Aug 18 '17 at 08:39
  • It does not work at all. The code will not run after ".Range("O1").EntireColumn.Select" is carried out. – Aqqqq Aug 18 '17 at 08:55
  • didnt read it through, but range wont in that matter. i'll see what i can do today – krib Aug 18 '17 at 08:56
  • What do you mean by "range wont in that matter"? – Aqqqq Aug 18 '17 at 08:57
  • Correction to my former statement: ".Range("O1").EntireColumn.Select" is not run either – Aqqqq Aug 18 '17 at 08:58
-1

I just figure that I could insert column analogous to inserting rows. Following is the code for inserting the 15th column of destSheet:

        Application.ScreenUpdating = False
        Application.CutCopyMode = False
        destSheet.Cells(1, 15).EntireColumn.Copy
        Range(destSheet.Cells(1, 15).Offset(1, 0), destSheet.Cells(1, 15).Offset(1, 0)).EntireColumn.Insert Shift:=xlToLeft
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
Aqqqq
  • 816
  • 2
  • 10
  • 27
  • More parts of this that looks to be more from what i posted few days back. – krib Aug 18 '17 at 09:51
  • I don't think so. Only the first and the last two lines just happen to be similar to your code, which might not be needed (I put it there just in case). I as a VBA beginner would never be able to go from your code to the third and the fourth line I posted. "More parts of this that looks to be more from what i posted few days back." just proves that you did not read the code carefully at all. Your code also does not work for me. – Aqqqq Aug 18 '17 at 13:56