Help - VBA code inserting columns and keeping formulas

[Prish]

Hi,

Please assist me. I have attached a sample file.
I am using the following code to insert a column and copy formulas.

Sub InsertColumn()

    ActiveCell.Offset(-1, 0).EntireColumn.Insert
    ActiveCell.EntireColumn.Copy ActiveCell.Offset(-1, 0).EntireColumn
    
End Sub

This inserts a column but does not include the formulas in the header row and total row. Also the total column does not include the new column in it's sum calculation. Please help me, I greatly appreciate it.

The code I have for inserting a row works fine however it copies the row above - I would like it to copy only the formulas.

 

[DocAElstein]

Re: VBA Inserting columns and keeping formulas in Table Ranges

...apologies for not picking that up. .....

I should have noticed it myself, with hindsight it is obvious , like most things. But it is all good llearning
I want to tidy up and do some explanations on the codes for later referrence, then i will drop them of.

Alan

 

[DocAElstein]

Re: VBA Inserting columns and keeping formulas in Table Ranges

Ok, here we go

This sort of thing is almost certainly done best with “VBA Tables” and Named Ranges etc.
The VBA Guide To ListObject Excel Tables
( “VBA ListObject Excel Tables” stuff )

But that needs a bit more Knowledge then I have and is best done on a file that I would then give, which we prefer to avoid – the link could not work later making the Thread not as useful to anyone else, and downloading these days is getting more and more dodgy.. so best to avoid.. etc.... etc..

_.............

( BTW. There are a lot of extra unnecessary lines and steps in all the codes i have done, and they are not as complicated as they look.
In this “opened up” form it is easy to understand and modify. So a good start point )

_.........................

Just for fun it might be worth me giving you first a couple of codes which were my start point - as you said you are familiar “record” which i take to mean using the macro recorder.. what i did to start with....
_...

So these two codes I got basically by running a macro recorder , then tweaked them a bit. (Note I am inserting whole columns and rows here, ( which i changed in my final code to just inserting specific Ranges) - . Depending on how you may be adding things to the sheet, one way or the other may be better.
_ - But concentrating on a specific Range would make it an interesting exercise to compare with a “VBA ListObject Excel Tables” ..... maybe a "“VBA ListObject Excel Tables” Expert" might catch this thread one day and do an nice alternative....

The main tweak I did might be worth noting. This was was to change the macro generate
VBA Worksheets. Paste Method
To
VBA Range. PasteSpecial Method.

Both those two use the Clipboard, but the latter allows you to pick out what version held there. That is important to you for your Formulas as well as Formatting ( colour etc. ), which I assume you want to preserve.

VBA Worksheets. Paste Method is a bit hit and miss with what gets pasted out. The quick method which by-passes the clipboard ( as in your first code) , is also a bit hit and miss. ( I prefer to write fully that in its full form to remind me what is going on.. so a quick mod to your initial code to show that..

    ActiveCell.EntireColumn.Copy Destination:=ActiveCell.Offset(-1, 0).EntireColumn

You see the syntax could ba a bit missleading.

.Copy on its own puts something in the clipboard
It is the
VBA .Copy Method
It is a single Comand

-..........

This is also a single command

Copy Desination:=
Is a different command which does something else…

-......................................

Just to summarise that, - we have 4 commands, all different

_ .Copy

_ Worksheets. Paste

_ Range. PasteSpecial

_ Copy Desination:=

http://www.mrexcel.com/forum/excel-...opy-only-values-not-formulas.html#post4043472
_......

Here then those code, note they are hard copied, so only applying to a specific range... (my final codes will work on whatever ranges you have, only requiring that they start at B4)

Option Explicit
Sub PrischInsertColumnAtEndMacroRecording()
    Columns("H:H").Select
    Selection.Insert
    Columns("F:G").Select
    Selection.Copy
    Columns("G:H").Select
    'ActiveSheet.Paste    ' main change to code...... from Worksheets. Paste Method .....
    Columns("G:H").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats          '..to ...... Range Paste Special Method to allow more selective use of the varius copies in the clipboad when pasting        ''    http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value.html#post4071766            http://www.excelforum.com/showthread.php?t=1110162&p=4230363&highlight=#post4230540                                                         ''    http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value.html#post4071766
    Columns("F:G").Select
    Selection.Copy
    Columns("G:H").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Columns("G:G").SpecialCells(xlCellTypeConstants).Clear                      ' https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.specialcells.aspx                    https://msdn.microsoft.com/de-de/library/office/ff196157.aspx
End Sub

Sub PrischInsertRowAtEndMacroRecording()
    Rows("9:9").Select
    Selection.Insert
    Rows("7:8").Select
    Selection.Copy
    Rows("8:9").Select
    Rows("8:9").PasteSpecial Paste:=xlFormulas
    Rows("7:8").Select
    Selection.Copy
    Rows("8:9").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Rows("8:8").SpecialCells(xlCellTypeConstants).Clear
End Sub

I will drop the final main codes in the next Thread to keep it less cluttered...

 

[DocAElstein]

Re: VBA Inserting columns and keeping formulas in Table Ranges

The main codes now, and some brief explanations

Rem 1)
Gets all the important info of where things are, including Top left of the two tables

Rem 2)
Determines where the row or column will be added.
( For the rows you are asked for if Salesperson is being considered. If you say no it defaults to Expenses )

Rem 3)
A table row or column ( range ) is added, ( actually an empty range “row” of the table “width” or empty range “column” of total “depth” for both Tables is added ) . This is added just after the appropriate “end”

Rem 4)
First various Copying and (xlSpecial)Pasting is done to give the Formulas and Formats copied.
The values not required are then removed by using the VBA special cells stuff.
This section could probably be don a bit more efficiently, as could much of the code to speed it up a bit, but as an initial go to be going on with it is OK,, i think...

Rem 5)
May not be needed. It chops out a range equal to the one inserted just after the final increased size Tables.

_..............

I have not checked it out extensively yet, but see how you get on ...



Codes:

[color=blue]Sub[/color] PrischInsertRowAtEnd() [color=darkgreen]' http://www.mrexcel.com/forum/excel-questions/936598-help-visual-basic-applications-code-inserting-columns-keeping-formulas.html#post4500417[/color]
Rem 1) Worksheets Info
[color=blue]Dim[/color] ws1 [color=blue]As[/color] Worksheet                                                                                     [color=darkgreen]' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object. This also us to get easilly at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )[/color]
[color=blue]Set[/color] ws1 = Worksheets("Budget")                                [color=darkgreen]' use Worksheets Object collection of Worksheets to referrence Worksheet  ' values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer"".[/color]
[color=darkgreen]'                                Set ws1 = Worksheets.Item(1) ' Item alternative counting from tabs from left[/color]
[color=darkgreen]' Table Salesperson[/color]
[color=blue]Dim[/color] TLTblS [color=blue]As[/color] Range [color=darkgreen]' Top left cell of Table Salesperson[/color]
[color=blue]Set[/color] TLTblS = ws1.Range("B4") [color=darkgreen]'        "......Top Left "Salesperson" Table always be at B4....."[/color]
[color=blue]Dim[/color] rngTblS [color=blue]As[/color] Range
[color=blue]Set[/color] rngTblS = TLTblS.CurrentRegion [color=darkgreen]'  "...(might add other groups later)...."  .. So restrict us to working on a range given by the Current region property, which when applied to a range object returns a new Range object of the "box" containg all connected cells to the original Range Object[/color]
[color=blue]Dim[/color] rwsInItS [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'                                       , clmsInItS As Long   ".....Column should be added to both sections simultaneously....."                                '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )     . [color=blue]Long[/color] is one of the few Varaible where th "Blue print" "Pointer" can be decalred at the outset faily fully, - the actual value may change but the "length" of, say, 10.12345 is the same as 00.12545[/color]
[color=blue]Let[/color] rwsInItS = rngTblS.Rows.Count [color=darkgreen]'                          : Let clmsInItS = rngTblS.Columns.Count[/color]

[color=darkgreen]' Table Expenses[/color]
[color=blue]Dim[/color] TLTblE [color=blue]As[/color] Range [color=darkgreen]' Top left cell of Table Expenses[/color]
[color=blue]Set[/color] TLTblE = ws1.Cells.Find(What:="Expenses", after:=TLTblS, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext, MatchCase:=True) [color=darkgreen]'   Range.Find method to get at Top left of expense table[/color]
[color=blue]Dim[/color] rngTblE [color=blue]As[/color] Range: [color=blue]Set[/color] rngTblE = TLTblE.CurrentRegion
[color=blue]Dim[/color] rwsInItE [color=blue]As[/color] Long [color=darkgreen]'                                       , clmsInItE As [color=blue]Long[/color][/color]
[color=blue]Let[/color] rwsInItE = rngTblE.Rows.Count [color=darkgreen]'                          : Let clmsInItE = rngTblE.Columns.Count[/color]

Rem 2) Ask Table for row to be added, determine where next row to be added
[color=blue]Dim[/color] rwNext [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Dim[/color] Response [color=blue]As[/color] Long [color=darkgreen]'In VBA Butons "yes is 6, 7 is "no"[/color]
Response = MsgBox(prompt:="Salesperson?", Buttons:=vbYesNo, Title:="Yes for Salesperson, No for Expenses")  [color=darkgreen]' Displays a message box with the yes and no options.[/color]
    [color=blue]If[/color] Response = vbYes [color=blue]Then[/color]
    [color=blue]Let[/color] rwNext = ((TLTblS.Row) + (rwsInItS - 1)) + 1 [color=darkgreen]' Start of table + (rows in it -1)  to give last row, then add 1 for next free row for extension[/color]
    [color=blue]Else[/color] [color=darkgreen]'Assume that for answer "No",  Expenses need new row[/color]
    [color=blue]Let[/color] rwNext = ((TLTblE.Row) + (rwsInItE - 1)) + 1
    [color=blue]End[/color] [color=blue]If[/color]
    
Rem 3 ) Extend Table ( only ) by a row
[color=blue]Dim[/color] clmsInEm [color=blue]As[/color] [color=blue]Long[/color]: [color=blue]Let[/color] clmsInEm = rngTblE.Columns.Count   [color=darkgreen]' .. Em ..  ".....Column should be added to both sections simultaneously....." - so either table will do as they will always be the same width[/color]
[color=blue]Dim[/color] rwNew [color=blue]As[/color] Range: [color=blue]Set[/color] rwNew = ws1.Range("B" & rwNext & "").Resize(1, clmsInEm) [color=darkgreen]'     Range Object of Next free cell in B column ( would be taken as Top left of whatever Ranre was specified ) has the Resize Property applied which returns a new Range Object of increased size.                    .Resize(, clmsInEm) would also work then the rows stays the same[/color]
rwNew.Insert [color=darkgreen]'Insert extra Row ( Range ) at end of chosen Table[/color]

Rem 4) Copy and Paste[color=darkgreen]'                                                                 Using Copy method, then use the Range Paste Special Method to allow more selective use of the varius copies in the clipboad when pasting        ''    http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value.html#post4071766            http://www.excelforum.com/showthread.php?t=1110162&p=4230363&highlight=#post4230540[/color]
rwNew.Offset(-3, 0).Resize(2, clmsInEm).Copy                                     [color=darkgreen]' Extensive use of Offset property is used as this suits well how Excel "works". Offset Property returns a new Range Object of the same size but offset from the Original Range Object to which it is applied[/color]
rwNew.Offset(-2, 0).PasteSpecial Paste:=xlFormulas [color=darkgreen]'                                   Top Left usually is sufficient as start of paste ..butNote**[/color]
rwNew.Offset(-3, 0).Resize(2, clmsInEm).Copy
rwNew.Offset(-2, 0).Resize(2, clmsInEm).PasteSpecial Paste:=xlPasteFormats [color=darkgreen]'           but..Note** Resize(2, clmsInEm) appears necerssary her ! ?[/color]
Application.CutCopyMode = [color=blue]False[/color]
rwNew.Offset(-2, 0).SpecialCells(xlCellTypeConstants).Clear [color=darkgreen]'remove only values from new data row ( Range )[/color]

Rem 5) Option to delete a table row range to "shift back up" the offset buldge
[color=blue]Dim[/color] rwsTbls [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]' for total Tables "depth"[/color]
[color=blue]Let[/color] rwsTbls = ((TLTblS.Row + (rngTblS.Rows.Count - 1)) + 1 + (rngTblE.Rows.Count) + 1) + 1 [color=darkgreen]' From B4 to end of first table + gap + rows in second table - now we are at ent of original tables, so then +1 to get just outside ( under)  that, then finally another + 1 to allow for the added row[/color]
ws1.Range("b" & rwsTbls & "").Resize(1, clmsInEm).Delete [color=darkgreen]' Delete the next row range ( width as in Tables )[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
'
[color=blue]Sub[/color] PrischInsertColumnAtEnd()
Rem 1) Worksheets Info
[color=blue]Dim[/color] ws1 [color=blue]As[/color] Worksheet                                                                                     [color=darkgreen]' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object. This also us to get easilly at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )[/color]
[color=blue]Set[/color] ws1 = Worksheets("Budget")                                [color=darkgreen]' use Worksheets Object collection of Worksheets to referrence Worksheet  ' values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer"".[/color]
[color=darkgreen]'[/color]
' Table Salesperson
[color=blue]Dim[/color] TLTblS [color=blue]As[/color] Range [color=darkgreen]' Top left cell of Table Salesperson[/color]
[color=blue]Set[/color] TLTblS = ws1.Range("B4") [color=darkgreen]'        "......Top Left "Salesperson" Table always be at B4....."[/color]
[color=blue]Dim[/color] rngTblS [color=blue]As[/color] Range
[color=blue]Set[/color] rngTblS = TLTblS.CurrentRegion [color=darkgreen]'  "...(might add other groups later)...."  .. So restrict us to working on a range given by the Current region property, which when applied to a range object returns a new Range object of the "box" containg all connected cells to the original Range Object[/color]
[color=blue]Dim[/color] rwsInItS [color=blue]As[/color] Long [color=darkgreen]'                                       , clmsInItS As [color=blue]Long[/color]   ".....Column should be added to both sections simultaneously....."[/color]
[color=blue]Let[/color] rwsInItS = rngTblS.Rows.Count [color=darkgreen]'                          : Let clmsInItS = rngTblS.Columns.Count[/color]

[color=darkgreen]' Table Expenses[/color]
[color=blue]Dim[/color] TLTblE [color=blue]As[/color] Range [color=darkgreen]' Top left cell of Table Expenses[/color]
[color=blue]Set[/color] TLTblE = ws1.Cells.Find(What:="Expenses", after:=TLTblS, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext, MatchCase:=True) [color=darkgreen]'   Range.Find method to get at Top left of expense table[/color]
[color=blue]Dim[/color] rngTblE [color=blue]As[/color] Range: [color=blue]Set[/color] rngTblE = TLTblE.CurrentRegion
[color=blue]Dim[/color] rwsInItE [color=blue]As[/color] Long [color=darkgreen]'                                       , clmsInItE As Long[/color]
[color=blue]Let[/color] rwsInItE = rngTblE.Rows.Count [color=darkgreen]'                          : Let clmsInItE = rngTblE.Columns.Count[/color]

Rem 2) Determine where next Column is to be added
[color=blue]Dim[/color] clmsInEt [color=blue]As[/color] [color=blue]Long[/color]: [color=blue]Let[/color] clmsInEt = rngTblS.Columns.Count
[color=blue]Dim[/color] clmNext [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Let[/color] clmNext = (rngTblS.Column + (clmsInEt - 1)) + 1 [color=darkgreen]' Next free column is start column added to one less than number of columns plus 1[/color]

Rem 3) Extend ( Tables only ) by a column
[color=blue]Dim[/color] rwsInEm [color=blue]As[/color] Long [color=darkgreen]' total row "depth" of tables[/color]
[color=blue]Let[/color] rwsInEm = rngTblS.Rows.Count + rngTblS.Rows.Count + 1 [color=darkgreen]' Sum of rows in tables plus row between[/color]
[color=blue]Dim[/color] clmNew [color=blue]As[/color] Range
[color=blue]Set[/color] clmNew = ws1.Cells(4, clmNext).Resize(rwsInEm, 1) [color=darkgreen]'[/color]
clmNew.Insert Shift:=xlToRight ' xlToRight is important or default xlDown will shift a column down

Rem 4) Copy and Paste
clmNew.Offset(0, -3).Resize(rwsInEm, 2).Copy [color=darkgreen]'[/color]
clmNew.Offset(0, -2).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats                                                           ''    http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value.html#post4071766
clmNew.Offset(0, -3).Resize(rwsInEm, 2).Copy
clmNew.Offset(0, -2).Resize(rwsInEm, 2).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = [color=blue]False[/color]
clmNew.Offset(0, -2).SpecialCells(xlCellTypeConstants).Clear                      [color=darkgreen]'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.specialcells.aspx                    https://msdn.microsoft.com/de-de/library/office/ff196157.aspx[/color]

Rem 5) Option to delete a table column Range to "shift everything" back
clmNew.Offset(0, 1).Delete
[color=blue]End[/color] [color=blue]Sub[/color]

_...........................

Let me know how you get on and if there are any problems, or mods you want..

Alan

 

[Prish]

Re: VBA Inserting columns and keeping formulas in Table Ranges

Let me know how you get on and if there are any problems, or mods you want..

Alan

Hi Alan.

Well done... Thank you for you this. I was not expecting it to be so extensive.
Would like a few changes:

The total column does not maintain its number format.
The row inserted replaces any data below the expense table instead of shifting it down. Eg. A row showing profit for the month (Total sales - Total expenses)
Inserting more than one row/column at once causes run-time error. I'm guessing this is caused when there is no values to clear.

Other than that, it does what I want... So thank you once again.

 

[DocAElstein]

Re: VBA Inserting columns and keeping formulas in Table Ranges

Hi Prisch,

...Well done... Thank you for you this. .....

Yous welcome. Thanks for the feedback
_............

... I was not expecting it to be so extensive. ...

-.. as i mentioned the final code can be simplified greatly.....
_...................................

....
Inserting more than one row/column at once causes run-time error. I'm guessing this is caused when there is no values to clear. .....

Correct ! How dopey of me. Exactly what you say.
You could just ignore it, as it effects nothing as it errors at the end, but i can do a quick mod to overcome that, no problem.
_.............................

.....
The row inserted replaces any data below the expense table instead of shifting it down. Eg. A row showing profit for the month (Total sales - Total expenses) .....

I think i follow what you are saying there. That is a little bit to do with what i meant here

........
....
So these two codes I got basically by running a macro recorder , then tweaked them a bit. (Note I am inserting whole columns and rows here, ( which i changed in my final code to just inserting specific Ranges) - . Depending on how you may be adding things to the sheet, one way or the other may be better.
_ - But concentrating on a specific Range would make it an interesting exercise to compare with a ...
........

In addition to that, which way to do it that is the best will depend on what / if / where other stuff is in the Spreadsheet, which is why I asked if you would be adding later . I maybe stupidly assumed you had nothing immediately below the table as the last bit, sorry about that.
The problem is probably because section
Rem 5)
Effectively chops that last row out. ( i think )
Depending on exactly how your data looks, removing that section, Rem 5) may be sufficient to overcome that problem.
But it might be better to redo the code to insert entire columns and rows, rather than just those in the Tables....
Again I see no problem there. Just a case of how i am inserting things, and how that effects you.

BTW: On that Inserting point: - I often learn from answering these threads. Getting good at Inserting things in different / controllable ways is what i seem to be learning from this thread... –Yesterday my spreading apart to insert things knocked me a bit off wack, and i got it wrong at first.
I need ( or would like ) to get that clear in my mind. I am going to do some Test now over at the Test Area to get that clear in my head. I want to sort that out for myself first.
Test Here
http://www.mrexcel.com/forum/test-here/936964-test-inserting-things-properly.html

If you have the time in the meantime, you can post some more screen shots showing, for example where things like your row showing profit for the month (Total sales - Total expenses) is. Keep the data to a minimum, like you did, that is better, ( even knock out another column or row or two )

If you had time to install and use the Forum Tools to do that and practice that for some screen shots that would be great in the long run. ( Play around testing and experimenting here in the Test Area if you do that..
_.. if you feel the erge You can stick stuff in my Thread there, or just start one of your own.... you can do anything there in the Test Area, – the threads are mostly ignored then deleted after a few days..
..)
I know that is all a pain initially, but well worth it in the long run to get help at MrExcel.

BTW. I did an alternative to an Add-In just now, for someone who was having problems with installing an Add-In.. What i have done is posted a simple code done by another member, Marcelo Branco, that does the same to get a screen shot to paste in a Forum Thread. Maybe worth a quick look, if you have the time.
http://www.mrexcel.com/forum/test-here/936964-test-inserting-things-properly.html#post4501034
http://www.mrexcel.com/forum/test-here/936964-test-inserting-things-properly.html#post4501015

_...............................................

.......The total column does not maintain its number format.......

I am not too clever with number formats. Especially as our different settings may influence things. I was ( am ) hoping the key to that was using the
_ Range. PasteSpecial
Stuff.. ... But if I have time I will look at that as well

_.................................................


So i will try to post back again, maybe a bit later after i have played around a bit. If in the meantime you can do some more screenshots that would be good. Or alternatively upload another file. But i think i get the jist of what is needed anyway.


Try to catch you later.

Alan

 

[Prish]

Re: VBA Inserting columns and keeping formulas in Table Ranges

Hi Alan,

I have learned quite a lot from your codes. However i tried a different approach using parts of the code.
I've used named ranges for total rows/columns. I also used a userform instead of Msgbox.
In the end, i am very pleased with results and couldn't have done it without your help.

Final file

My codes:

Sub InsertRow()

Insert_Row.Show
   
End Sub


Sub InsertColumn()
Range("coltot").Select
    ActiveCell.Offset(0, -1).EntireColumn.Insert
    ActiveCell.Offset(0, 0).EntireColumn.Copy
    ActiveCell.Offset(0, -1).EntireColumn.PasteSpecial Paste:=xlPasteFormulas
    On Error Resume Next
    ActiveCell.Offset(0, 1).EntireColumn.SpecialCells(xlCellTypeConstants).Clear
  
   Range("coltot").ClearOutline
   Range("coltot").Select
    
End Sub

Userform:

Private Sub Expense_Click()


Range("exptot").Select
    ActiveCell.Offset(-1, 0).EntireRow.Insert
    ActiveCell.Offset(0, 0).EntireRow.Copy
    ActiveCell.Offset(-1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    On Error Resume Next
    ActiveCell.Offset(1, 0).EntireRow.SpecialCells(xlCellTypeConstants).Clear
  
   Range("exptot").ClearOutline
   Range("exptot").Select
   
    Unload Insert_Row


End Sub


Private Sub Sales_Click()


Range("salestot").Select
    ActiveCell.Offset(-1, 0).EntireRow.Insert
    ActiveCell.Offset(0, 0).EntireRow.Copy
    ActiveCell.Offset(-1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    On Error Resume Next
    ActiveCell.Offset(1, 0).EntireRow.SpecialCells(xlCellTypeConstants).Clear
  
   Range("salestot").ClearOutline
   Range("salestot").Select
   
   Unload Insert_Row
   
End Sub

 

[DocAElstein]

Re: VBA Inserting columns and keeping formulas in Table Ranges

Hi

...... i tried a different approach using parts of the code.
I've used named ranges for total rows/columns. I also used a userform instead of Msgbox.
In the end, i am very pleased with results.......

Wow, great,
and thanks for sharing the codes...

I am not too good on UserForms. I think you have to create them some how and Name them .. etc...
And named ranges have to be set up in a sheet I think ( or easier than doing it with a code which i think you can also do )

I guess that , along with Named Ranges, is one thing where sharing a file can be useful...

.....
This sort of thing is almost certainly done best with “VBA Tables” and Named Ranges etc........
.... and is best done on a file....... to share.....

_.. Any chance of uploading a file with the user Form and named ranges Set in it ?
_. That would be a great Thread contribution and interesing comparison.
_. ( preferably with reduced test data, similar to what you had nbefoore, if you had the time ..

Thanks


Alan

EDIT - Ahh - I see you have a file Uploaded now
Thanks

 

[Prish]

Re: VBA Inserting columns and keeping formulas in Table Ranges

So i will try to post back again, maybe a bit later after i have played around a bit. If in the meantime you can do some more screenshots that would be good. Or alternatively upload another file. But i think i get the jist of what is needed anyway.


Try to catch you later.

Alan

I managed to fix everything as you will see in my previous post.
Have a look at the final file and share your thoughts.

Thanks. Have a great day!

 

[DocAElstein]

Re: VBA Inserting columns and keeping formulas in Table Ranges

.....
Have a look at the final file and share your thoughts.....

Brilliant !
And thanks again for sharing !!!
Have a good Sunday!
Alan

( I might just "turn the error handler off once you have finished using it, as good practice, but that is a very minor point

Sub InsertColumn()
Range("coltot").Select
    ActiveCell.Offset(0, -1).EntireColumn.Insert
    ActiveCell.Offset(0, 0).EntireColumn.Copy
    ActiveCell.Offset(0, -1).EntireColumn.PasteSpecial Paste:=xlPasteFormulas
    On Error Resume Next
    ActiveCell.Offset(0, 1).EntireColumn.SpecialCells(xlCellTypeConstants).Clear
    On Error GoTo 0
   Range("coltot").ClearOutline
   Range("coltot").Select
    
End Sub

http://excelmatters.com/2015/03/17/on-error-wtf/
_... )

But that is a very minor point - you have it well solved !!

 

[DocAElstein]

Re: VBA Inserting columns and keeping formulas in Table Ranges

Just a last info to finish off this Thread..: This helped me get the Inserting discussed in this thread a bit clearer....
http://www.mrexcel.com/forum/genera...ethod-excel-oop-syntax-error.html#post4501533


BTW. Thanks again Prish for the contribution. Taking some of the ideas from my attempt which did not work fully, then carrying on yourself to get there and ending up with a much better full solution yourself which was great to learn from
That all made for one of the best Threads i have been involved with. ( And doing it yourself meant i had more time to practice me insertions ..... )
Alan