Inserting 2 Columns After Each Column?
Apr 23, 2012I have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.
View 8 RepliesI have data in 4 columns, A, B, C, D. Each column has 5 rows of data. I want to write a macro to automatically insert 2 columns, after each column in original dataset.
View 8 Replieswhat i want to do is have an area where i can input data and then i have three options of inputing the data or resetting data that is already in the list of data so i start to create a list of data but i can reset/change values if i wish for example quantity. i would also like to have a way to subtract or add a number from the quantity assigned to a code/name of some form. i know that's quite a bit
View 3 Replies View RelatedI have recorded a basic macro that allows me to copy a formulas in cells CF11 to CH11, and it pastes it into cells in columns CJ to CL. Copy of code is below if you need it.
The issue I have is that I need to insert new coumns into the worksheet, and I need to copy the formulas from columns CF to CH, and they will now need to pasted to columns CN to CP. Note that this is a monthly report where we keep the prior months, so each month will need to add new columns. There are 8 tabs in the workbook, and they all use the same macro, just over different rows (columns all line up).
Is there a way that I can get the macro to paste into the correct column without me having to adjust the macro each time?
Copy of code is as follows:
Range("CF11:CH11").Select
Selection.Copy
Range("CJ11").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("CJ20:CL20").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
[code].....
I have been struggling to figure this out for several days now and i don't really know where to go from here.
I have 2 columns and i need to break them up at certain points.
The attached excel file should hopefully be clear enough to show what i mean.
The first tab is how the data starts the second tab is how i want it to look.
To clarify i need to break it after each change in the first column. ie from 1 to 2.
Furthermore, i need to break the "sub groups" the second column up after 15, and 30. I will never have a number larger than 32 in the second column.
I think the excel file will clear up what i mean.
I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.
I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.
I have a very simple inventory spreadsheet that I used to keep track of certain products. The issue is that I often have to insert new columns and when I do, the formulas get convoluted. Is there a trick to making them follow the structure of the formula before?
My formula is =SUM(GT4:GU4) and appears in the Running Total column. When I insert two columns - a white column where an order will be inserted, as well as a running total column. When I perform the insert, the formula for the inserted columns, as well as the set of columns immediately to the right require corrections:
Inserted columns: =SUM(GT4:GW4) [Should be GV4:GW4]
Columns to the right: =SUM(GV4:GY4) [Should be GX4:GY4]
I know how to manipulate the formulas correctly, I would like to keep a clean file for my predecessor.
I have a VBA macro I recorded that won't insert and copy columns into the proper areas. When I run this, It inserts every column all grouped together.
For example: I want to insert a column before column D, and give certain cells formulas. Instead, it inserts it before column C. The whole macro seems to, when ran, insert EVERYTHING before the range of data I am trying to split up.
Here is the
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2008 by kmayfield
'
'
Range("C3:F3").Select
ActiveCell.FormulaR1C1 = "Jan-2008"
Range("G3:J3").Select
ActiveCell.FormulaR1C1 = "Feb-2008"
Range("K3:N3").Select
ActiveCell.FormulaR1C1 = "Mar-2008"
Range("O3:R3").Select
ActiveCell.FormulaR1C1 = "Apr-2008"
Range("S3:V3").Select
ActiveCell.FormulaR1C1 = "May-2008"
Range("W3:Z3").Select
I'm building a bookkeeping workbook that is only currently 800kb with TWO cells that have data validation and no cells have conditional formatting. There are a fair few formulae but they are all simply SUM, SUMIF and CONCATENATE.
View 1 Replies View RelatedI am instantiating Excel 2003 from vb.net and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?
View 2 Replies View RelatedI have a spreadsheet that I can modify. It currently has a three product column but I need to insert 2 or 3 more columns to make 5 plus the total at the end. It also have a summary sheet. I insert the 2 columns and somehow got the formulas flowing. However when I hit the summary page its not showing results for those two new columns in the total. I am lost. I wish I could post the sheet. Its a multiple product break-even analysis exercise.
View 1 Replies View Relatedlets say i have 200 used columns. if i delete 150 of them and then (without saving the file) try to add 100 more i get an excel has reached its limit error. theoretically 200-150+100 = 150 columns, which should be well within the 258 column limit. but excel still gives me the error )its like its calculating 200+100=300 > 258). HOWEVER, if i save the file after deleting, i dont get any errors at all. is this a "feature" of excel and if so is there anyway of circumventing it? because i dont want to save the file prior to adding the columns (i do the whole thing in vba). actually i dont want to save the file at all unless the user click on the save button or choose save/save as from the menu.
View 9 Replies View RelatedFor some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.
View 1 Replies View Relatedthe following issue:
I have a spreadsheet of questionnaire responses which range from 1-7
For example:
Respondent Q1
1 4
2 3
3 7
4 6
So each row is a new respondent and each column is their response from the scale.
What I need to do is code the responses into a different form. I need them to be represented as follows:
Respondent Answer1 Answer2 Answer3 Answer4 Answer 5 Answer 6 Answer 7
1 0 0 0 1 0 0 0
2 0 0 1 0 0 0 0
So that each number then represents the place on the scale from which it was chosen.
I tried recording a macro but I think this requires something a lot more complex.
firstly i am cross-posting this topic so here is the URL to the same thread in a different forum.
[url]
whats up everybody? i have to use two excel worksheets that someone else already created to make CSV (comma seperated value) files. these CSV files are to be dumped into a database. the problem is that the table has more fields than the spreadsheet does (the spreadsheet has 3 fields - network, mask, size - and the table has 10 fields). i can insert one entire column before (to the left of) network (for the ID field in the table) without a problem, but when i try to insert entire blank columns after size (to the right of the pre-existing columns) it only makes columns for the first 14 rows. i know this because when i save it as a CSV file there are only 6 extra commas for the first 14 rows. is there a way to insert an entire blank column for the entire spreadsheet (as in all the way to the bottom)? i dont want to have to manually type in all those commas. i am using excel 2002.
I have a sheet with 2 tabs. On the first tab is my data and the 2nd tab is the formula.
This is my formula;
=IF(ISERROR(VLOOKUP(A3,Data!$A:$AD,23,0)),"",(VLOOKUP(A3,Data!$A:$AD,23,0)))
When I insert a new column at 'A' the formula changes (as below)
=IF(ISERROR(VLOOKUP(A3,Data!$B:$AE,23,0)),"",(VLOOKUP(A3,Data!$B:$AE,23,0)))
How do i stop the reference changing from 'Data!$A:$AD' to 'Data!$B:$AE' when inserting the column?
I have Data in Column a There in not demarcation between two Groups of Data
I want it Insert a Row between the two Groups of data.
Each group of Data end with a cell has "Employee" in it
The Row is to be inserted after the cell having “employee”
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
Is there a quick way to copying formula's from one column to every other column? I am using about 1000 columns.
View 6 Replies View Relatedsimple little procedure to insert columns on two sheets and call each by the same name. Macro runs off a command button and inserts a column at E:E and gives the user an input box with which to name the inserted columns.
Code: ...
How in excel do I get a no. to appear in a column dependant on a reference in a different cell. For example If i type March in a cell I want the number 1 to appear in the March column of a table.?
View 9 Replies View Relatedthe Ruler across the top that allows you to adjust the column widths (A B C D etc). Is it possible to insert another Ruler, further down the same page, to allow you to adjust the column width differently. if so how. On Excel 2007
View 2 Replies View RelatedSuppose I have done a spreadsheet of,say 100 entries in alphabetical order in a column and I discover that I have missed 6. Is it possible to move the existing part from where I missed below this to make room for more entries without losing any of the entries?
View 3 Replies View Relatedmacro for inserting rows and column in Excel.
View 1 Replies View RelatedI have a column of an undefined number of rows where I need to add item numbers from 1 to however many items there are, starting from A9 downwards.
The last 3 used rows on the sheet contain signatures etc so it should not number the bottom 3 rows.
pretty sure its fairly simple code but i dont have anything similar from previous files that i can re-use to do this :p
just needs a simple
count how many rows are blank from A9 downwards (to say A200)
for num=1 to count do
Cell range(A(9+num) = num
end
i just dont know the code well enough to write it and make it work :p
I have a workbook with 2 worksheets. 1 Worksheet contains a data table and another table contains a form
Worksheet 1 (Data)
[A] [B] [C]
1) [Material #] [Batch #] [Destruct Form #]
2) 10 100A 1A
3) 10 101A
4) 20 200A
5) 30 300A 2A
6) 30 301A
Worksheet 2 (Form)
[A] [B]
1) [Destruct Form #] [3A]
2)
3) [Material #] [Batch #]
4) [10] [101A]
5) [20] [200A]
I am looking to create a macro which, when executed, will:
1) search sheet 1 column B for any instances where a "batch #" from sheet 2 column B appears
2) add in the corresponding "Destruct Form #" from Sheet 2 Cell B1 into sheet 1 column b
Therefore after the Macro is run, sheet 1 will look like this:
Worksheet 1 (Data)
[A] [B] [C]
1) [Material #] [Batch #] [Destruct Form #]
2) 10 100A 1A
3) 10 101A 3A
4) 20 200A 3A
5) 30 300A 2A
6) 30 301A
This may sound trivial, but how do you insert a column to the right of the current selection?
If I do Selection.EntireColumn.Insert that inserts a column to the left. Anyone know how to do this?
I have a long list of inventory parts and there can be additions or deletions at any time. Currently the inventory is determined using formulas from other worksheets and this uses formulas. If I have a user insert a row to create a part in inventory, is there a way to make the formulas automatically be created in the cells within the row?
View 4 Replies View RelatedI'm a novice in VB and can't work out how to solve the following problem:
I have imported NMEA-data in text format from a GPS into Excel. This data is acquired in real-time at 10Hz, which borders what the GPS in capable of calculating. As a result the data isn't quite reliable enough - there are strings missing and some lines have been skipped by the GPS. This is a typical example of what sometimes happens: ...
Whenever I insert a column in Excel 2010 it takes the formatting from the Column to the left. I want to insert a Column without formatting. Just a blank column.
View 4 Replies View RelatedI have the following sheet with reg hours and ot hours. I need to insert a column that sums these two items. I used the macro recorder and it only works when the same amount of ID's or less. If there are more ID's it does not sum the remaining ID's. What it currently looks like:.........
View 3 Replies View Related