Insert A Row In 2 Tables With VBA Code

Apr 18, 2014

I am trying to insert via a command button a new row in 2 different tables but I am finding some difficulties . I have attached an excel sheet as example.

View 3 Replies


Creating Tables In Spreadsheet Won't Let To Insert New Row?

Apr 17, 2014

sample worksheet Antligen.xls

I have a spreadsheet that I need to add new rows everytime I have a new client. I a formula in the N column and a formula in the F column that I would like to automatically insert itself into the new rows.

The problem is this: I have arranged my table so that I can look at clients alphabetically, and according to other date... BUT now it wont let me insert a new row when I have a new client!!!

Is there anyway of keeping it so that I can insert a new row into my table, but preserve all of the formulas that I want automatically put into both the F and N column?

Attached is the sample spreadsheet.

View 1 Replies View Related

Insert Rows Between Data For Multiple Tables

Dec 12, 2013

I need to insert a blank row between each row of data under my headers (not the immediate row) until I reach the end of the particular table. I then move down to the next header and repeat the process.

My starting data looks like this:


I need it to look like this:


[Code] .....

View 3 Replies View Related

Code Loop Through Two Tables Checking And Altering

Nov 12, 2008

I have two tables of information. The first is a matrix with some distances. The second is a table of distribution. Ive been trying this for a long time now with limited success using a truth table but i've realised the only way to do this is in code. Ive got limited experience with this so please point me in the right direction.

Ive written some steps explaining what each table does here -

Step 1Check for lowest value B4:F4 in Table 1 (in example is 10)
Step 2Check corresponding column destination available capacity in table 2 (Example 500)
Step 3Distribute as much as possible from source in table 2 (500)
Step 4Reduce value in capacity line by value taken from source
Step 5If some source remains move back to table 1 and find next nearest column Dest
Step 6Repeat step 2 until all source is gone in row
Step 7 Check for lowest value B5:F5 in Table 1
if run out of capacity at all sites stop code
until Table 1 column B is empty

Ive posted a spreadsheet with some before and after tables in it aswell. Its very small and formatted o its easy to see whats happening.

View 14 Replies View Related

Delete Query Tables Macro Code

Aug 14, 2008

I am currently working on a project that uses Excel to parse a .txt document. Its working quite well for me. It functions by having two worksheets. In the first worksheet I use the " import external data" menu to import my .txt file. In another worksheet I have set up fields that show only the important information from the .txt file and leave the junk behind.

Iím looking to improve the functionality of this by adding a button to automatically clear the data in the first worksheet so that new data can be added quickly.

I have searched the forum and found a couple of threads on "QueryTables". After reading up on those, I have made a simple button with the following code.

Sheets("Sheet1"). Cells.Clear

After I load a .txt file and parse it using the formulas set up, I copy my needed information and then press this button. The cells clear, and the QueryTables are "reset" (maybe not the right word). Now a different .txt file can be imported and the process starts again.

The problem I am having is that. If there is no "QueryTables.Item(1)" to delete, I get a run time error. (Run-time error '9': Subscript out of range).

Would anyone know how to make my button conditional to having a "QueryTable" active? I.E. If I press it when there is no data loaded it doesn't do anything or give me that error.

View 4 Replies View Related

VBA Code For Showing Detail From Pivot Tables Into Different Sheets

Dec 21, 2011

a query as i'm not too good with vba codes but can record macros but not for what i need.

I have got a pivot table which has about 200 rows. In Column A is the provider and in column B is the total number of people.

I now need to create a sheet per provider and the manual way to do is to double-click on the numbers.

Is there a code that would this automatically?

View 9 Replies View Related

VBA Code To Auto-expand Columns When Pivot Tables Refresh

Mar 30, 2013

I have 2 drop downs that when changed, auto refresh all pivot tables. My problem is getting the columns to auto-adjust based on the refresh or change of the data in the pivot table. Listed below the code I have thus far for the auto-refresh on pivot tables. How to auto expand all columns simultaneously.

The pivot tables are based off of tables on a different sheet(TOS Tables). So the code listed below is in the table sheet, not the pivot table sheet (TOS Customer Level). So followup question will be, which sheet to put the auto-expand columns code?

Private Sub Worksheet_Calculate()
'If data on this worksheet changes, refresh the pivot table
Sheets("TOS Customer Level").PivotTables("PivotTable2").RefreshTable
Sheets("TOS Customer Level").PivotTables("PivotTable5").RefreshTable
End Sub

View 3 Replies View Related

Macro/Code Required For Calculations Base On Pivot Tables Sum

Jan 29, 2009

What I required is either a Macro or Code for formulas in column 'F' in the attached spreadsheet that correspond to the SUM of each description and divided by 37.5 e.g. in F10 the formula should be =D10/37.5 the formula should be F12 D10/37.5 and so on all the way down the Pivot table

My problem is as the amount data increases on the Data Tab the formulas in column 'F' will become out of line with the corresponding Sum of each description so I guess I need some code or formula that check every time the Pivot table is refreshed.

View 3 Replies View Related


Oct 28, 2008

I have a worksheet named mylist, that I delete, then recreate everytime I run a specific macro. However, I have code in this worksheet that I require, so is there a way for me to insert code to "insert code" on the "re-creation" of the worksheet?

View 9 Replies View Related

Excel 2010 :: Creating Multiple Tables In VBA And Referring To Newly Created Tables?

Jul 1, 2013

Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.

Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.

Sub Auto_Open()
Dim sht As Worksheet
If Range("A1") = 1 Then


View 2 Replies View Related

Insert Raw After Certain Code?

May 12, 2014

i need to insert a raw after a certain code like "TRUE" is the code which is mentioned in column A at different intervals , after that i need a blank raw.


I have a huge data (approx 45000 lines) items .

View 2 Replies View Related

Insert Vba Code With Vba

May 15, 2007

I have a code that needs to be placed on any new worksheets created. All new worksheets are made using VBA code already so they just need to be told to add the code into each new sheet. How do I do this?

View 2 Replies View Related

VB Code To Insert Row With Certain Criteria?

Apr 27, 2014

I need a simple VB Code which will check the vale in cell A11 in a closed workbook.

If A11=Lunch insert a blank row otherwise do nothing

View 3 Replies View Related

Insert Code Into This Workbook Mod

Apr 7, 2009

I want to put the vba code into each "ThisWorkbook" in each workbook that is in a folder. I want the code that is placed in each "ThisWorkbook" to run whenever that workbook is opened.

How would I go about doing that? I have this code so far to open the workbooks that are in the folder.

View 6 Replies View Related

Insert Photo Using Code

Feb 20, 2010

I'm trying to find some simple code that would that would allow me to select a picture from the picture dialog and insert it to active cell at a small size, like 2 1/2 by 2 inches

View 4 Replies View Related

VBA Code To Have Tabs Insert On The Right

May 27, 2014

I have the code below and it runs a report for me but it puts the new tab to the left of my existing tabs. How can I alter the code to put the new tabs on the right?

If Me.cmbCat.ListIndex > -1 Then
'Add a new worksheet
Set WSReport = Worksheets.Add(before:=Worksheets(1))
With WSReport
'Check sheetname and keep adding 1 until not found.
If SheetExists("Report " & Me.cmbCat) Then
Cnt = 1


View 3 Replies View Related

Some VBA Code To Insert Pictures

Jul 15, 2009

I am doing up a spreadsheet for work. I have a master sheet which has important 'feeder' info on it. I also have 6 other sheets where the code will reference.

Here is the Data Entry sheet setup.

ColK --> Contains numbers starting in row 2 and ending in row 116 atm, could expand but no further than 500
ColL --> Same as above but with different numbers
ColM --> contains formulas which produce the sheet name that the data in ColK and ColL is related to (6 sheets)


I require some code that will look at each row from 2 to 500 in ColM for something other than "out of range" or nothing (the "out of range" or empty ones should be ignored), and for each relevant row it should identify the sheet name in ColM and then identify the cell that is at the cross reference of the 2 numbers from ColK and ColL and then insert a picture at that cell. Each sheet (the 6 of them) has numbers in ColA from A2 and Row1 from B1. The numbers will exact match those from ColK and ColL from the Data Entry Sheet.

View 9 Replies View Related

Insert Formula With Code

Jan 7, 2007

Event type Houring rate Hours worked Total amt to be paid
A 30 2 XXX
B 10 1
C 20 4

Using this example, i wish to add a formula using VBA in cell XXX. Hence, the formula in EXCEL in cell xxx would show =b2*c2+b3*c3+b4*c4.

View 3 Replies View Related

Code To Insert Object?

Feb 9, 2007

Is it possible to use VBA to call an existing Excel function? I want to have code that calls up the "Insert - Object" popup window in the same manner as it would if the user clicked the toolbar.

View 2 Replies View Related

Insert / Delete Row Causes Error In VBA Code?

Mar 15, 2013

I need to change the colour of cells depending on contents. The following code works perfectly until I try to insert or delete a row in the worksheet. Then I get Run-time error 13, Type mismatch.

Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target, Range("E5:E100"))
If Not I Is Nothing Then


View 2 Replies View Related

Insert Clear Highlight Code

Dec 30, 2013

I got a code in internet that highlight a cell with equal value after double clicking a cell.

But my problem is I want to clear highlighted cell after clicking another cell (making double clicked cell inactive).

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Dim FirstAddress As String
Dim wksh As Worksheet
Static ColIdx
Cancel = True

[Code] ..........

View 3 Replies View Related

Vba Code To Insert A Formula To Certain Cell

Aug 14, 2008

I try to put a formula to a certain cell using this code, but I get application defined or object defined error:

HTML Selection.Cells(1).Offset(0, 5).Formula = "=INDEX(range1;MATCH(""D""&C13;range2;0);MATCH(""S""&D13;range3;0))"

The weird thing is, that if I type the same formula itself to a cell, it works, so the problem shouldn't be with the formula:

HTML =INDEX(range1;MATCH("D"&C13;range2;0);MATCH("S"&D13;range3;0))

On the other hand I tried to replace the formula in my VBA code with simpler one, like this one and that worked aswell:

HTML Selection.Cells(1).Offset(0, 5).Formula = "=a1+a2"

View 10 Replies View Related

Sendkeys To Insert Code Into Codepane In VBE

Apr 23, 2009

I need to use sendkeys to insert code into codepane in VBE.

I use sendkeys "MyFunction()", but the "()" doesn't show up just "MyFunction"
I use sendkeys "MyFunction" & chr(40) & chr(41), still only get "MyFunction"

View 6 Replies View Related

Code To Insert A Formula In Cell

Sep 2, 2009

I'm using the below to code to insert a formula in cell E1, what am i doing wrong here.

View 5 Replies View Related

Insert Empty Rows Code

Jun 26, 2007

Can VBA code that inserts between written rows a certain number of empty rows (and to be able to specify somewhere in the code the number of rows to be inserted)

View 9 Replies View Related

Insert Rows Within Loop Code

Apr 3, 2008

I have a requirement where I have to add a row with x columns using vba. this is the code I am using now

k = 17
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord
Sheets("Products").Rows(k).Insert Shift:=xlDown
Sheets("Products").Rows(k + 1).Insert Shift:=xlDown
Sheets("Products").Rows(k + 2).Insert Shift:=xlDown
ThisWorkbook.Sheets("Products"). Cells(k, Prod6Col) = prodetails
ThisWorkbook.Sheets("Products").Cells(k, Unit4Col) = sSeats
isRecord = .NextRecord()
End If

In this code I have declared Prod6Col and Unit4Col as constants. instead I need to use them as variables like in this modified code. However if i use this code, I get an object definition error.

k = 17
m = 20 (20th column)
l = 18 (18th column)
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord

View 4 Replies View Related

Insert Sheet & Name Macro Code

Apr 8, 2008

I am trying to insert 50 tabs (at different points in time during a macro) that each has a state abbreviation. I know how to get the macro to add a tab, but to name it, it wants to select " Sheet 4". The problem with this is, the tab that was created might be sheet 15 or sheet 1, and I need it to be able to name the tab no matter what sheet # it is.

View 7 Replies View Related

Insert VB Code Using VB Code

Apr 20, 2009

if its possible to insert VB code into an open worksheet using VB code from another workbook.

I have a script which is cycling through workbooks in a folder inserting formulas into cells etc as there are so many. I would also like to add in some VB code to these work books at the same time so as to avoid doing it manually.

View 9 Replies View Related

Insert Jpg Format Into Cell And Vlookup Them According To Code

Mar 29, 2014

In the sample i have list of codes,also i have picture according this codes: Can i somehow conected pictures and codes? My pictures are in jpg form,can i insert them into cell,where picture should has form as cell has.

When the code from the Data sheet in cell A2 sheet 1,than show the picture conected with this code in Data sheet.

View 2 Replies View Related

VBA Code To Insert Row When Search Criteria Found?

Jan 22, 2014

I have data in cells B2:E2 and this can go down 100+ rows.

In column B i have invoice numbers but some cells contain the word "Deposit".

I have sorted this data so that the invoice numbers appear first and then all the Deposits.

I need a code to find the first instance of the word "Deposit" and to insert a row so that all the invoices and Deposits are seperated by a single row.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved