Macro To Add Empty Row And Partial Copy Formulas
Apr 5, 2012
I am looking for a macro that will insert a new row and copies the formulas from the row above, but not from all the columns.
This is the current situation:
A
B
C
D
E
F
G
H
I
J
K
L
M
Name
Project Nr.
Work Order
hours
[Code] ......
What I want is that if column G contains "11374340" an empty row will be inserted above and the formulas from column E until M to be copied a row down. And the sum function in the last row of M needs to be changed automaticly as well (if possible).
I already have the macro to insert a new empty row which is as follows:
Sub InsertRows()
Dim i, LastRow
LastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "G").Value = "11374340" Then
Cells(i, "G").EntireRow.Insert
End If
Next
End Sub
View 4 Replies
ADVERTISEMENT
Dec 11, 2013
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
View 11 Replies
View Related
Mar 25, 2014
I have a row with about 230+ columns, a little over half of the columns have complex IF or Vlookup formulas. The goal would be to click a button on a UserForm (to create a new Client) to copy all row formulas to the next empty row.
View 5 Replies
View Related
Feb 21, 2014
I've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.
View 1 Replies
View Related
Sep 1, 2012
I made a macro to copy data from my data entry sheet to my database sheet:
Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim NextRow As Range
Set NextRow = Sheet4.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
[Code] .......
The main to problems are that its not pasting in in the right collum and its not passing the next entry in the next free row.
Have attached a screenshot of the two sheets, at the sheet has some personal data, i won't be able to upload it.
View 9 Replies
View Related
Apr 22, 2014
I am looking for a macro that will copy the row above it, to include formatting and formulas, however not the actual contents. I need the row that's being copied to be hidden from view, which is where the problem stems from. The macro is also copying the 'Hide' formatting and I want to exclude that. This is the current macro I am using:
[Code] .....
View 2 Replies
View Related
Nov 11, 2007
I have 2 speadsheets, I want to copy rows of Formulas from Spreadsheet 1 and copy them into Spreadsheet 2 but convert them into Values.
Is it possible to create a button on Spreadsheet 1 and assign macro to do exactly what i want? I also want the macro to look for the first empty row in the database so that the information being copied are on the next row and not overwritten.
View 10 Replies
View Related
Oct 13, 2009
I have an existing macro that copies a worksheet and pastes it into another workbook, renames it and then attaches it to an email. My problem is that it pastes just the values. I need it to paste part of the original worksheet as values and part copy the formulas. So on the new workbook Columns A through F will be values only and G through Z will copy the formulas.
View 10 Replies
View Related
Jul 13, 2009
I was searching through this site and found this macro ...
View 9 Replies
View Related
Mar 15, 2013
I am looking to create a macro to be assigned to a button that copies the last row of data entered and then pastes it to the last empty row on a different worksheet. This is a dummy spreadsheet to work with (I have more data, but the concept is one in the same). Sheet1 ("Branch1"), Sheet2 ("Branch2"), and Sheet3 ("All"), the names in brackets are names of the sheets, but for ease I'll refer to them as Sheet1, Sheet2, and Sheet3. I have columns beginning in B as follows: Date, Branch, Currency, Coin, and Total (the branch and Total are tied to formulas, however I just need to the text values and formats to come over to the other worksheet). have the portion regarding the copy of the last row in Sheet1, however it won't PasteSpecial.Selection in Sheet3 as it says the cells are not sized or formatted correctly.
VB:
Sub CopyB2()
lr2 = Sheets("Branch2").Range("B" & Rows.Count).End(xlUp).Row
lr3 = Sheets("All").Range("B" & Rows.Count).End(xlUp).Row + 1
Sheets("Branch2").Range("B" & lr2).EntireRow.Copy Sheets("All").Range("B" & lr3)
End Sub
View 1 Replies
View Related
May 8, 2014
I'm never done a macro before and of course I'm very new to excel. I have a worksheet for lessons learned that have multiple sheets for each state. I want to create a macro that every time I enter a new data into a row and hit "Add to summary sheet" and it copy and paste the the next available row in summary sheet.
See the attached file for my workbook : Book1.xlsx‎
View 9 Replies
View Related
Mar 20, 2007
first time posting. Need some help. I have a workbook with two sheets - (say sheet A and sheet B) that I need to copy a range from and paste (concatonated) into a new workbook. The thing is I want to copy the "non-empty" cells in columns d and e of each sheet - another words - I dont want to specify a specific cell range because users will be periodically adding new rows. Then I want to paste the "combined" from both sheets into a new sheet in a new workbook.
View 9 Replies
View Related
Sep 8, 2009
I have about 10 rows of data that have columns of information describing computer hardware and pricing. There are 10 rows, because each row represents a different country and different tax rates and pricing changes per country. So each column of a row has a specific formula to calculate information for that row's country information. All these 10 rows of formulas are kept as a reference at the bottom of the sheet.
At the top of the sheet I need to have a drop down menu to choose a country and then it will automatically copy and paste the row of that country to the current row at the top.
Is there any easy copy/paste macro for such a thing?
View 9 Replies
View Related
Apr 27, 2009
I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.
I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.
(1) Name (2) Address (3) ID# (4) Control#
Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)
Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.
MAIN DATA (columns A-D)
Investor1 (columns E-H)… Investor2 (columns I-L)… Investor9 (columns AK-AN)
Lawyer1… Lawyer9
Compliance1… Compliance9
Finance1… Finance9
Support1… Support9
MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.
The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.
I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.
Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.
I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.
So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.
Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.
Sub transposedata()
Sheets("ConsolidatedYTDReport").Select
Range("E2:H4202").Select
Selection.Copy
XXXXXXXXXXXX
ActiveSheet.Paste
End Sub
View 10 Replies
View Related
Oct 7, 2008
I'm attempting to copy some files into an archive folder in the same main folder. These files appear weekly in a folder that contains numerous other files.
Each of these files has a filename such as:
RAH_MRL_TOP_40_(ddmmyy_TIME)
with time being the time that the report was run.
Every week the filename stays the same but the time and date changes. I was hoping to be able to simply use the "Filecopy" command and specify the source file with a "*" after the main file name. However this does not seem to work.
Is there a simple way to do this? (I dont want more than a few lines of coding really as I will be duplicating it for each file and then changing the file name as needed.
View 9 Replies
View Related
Nov 19, 2007
I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:
a list of about 800 street names
a list of about 1000 property names
example
church street flat
church street apartment
house church street
house king's road
etc
what I would like excel to do is the following:
look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.
this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.
for example:
church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road
that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.
I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.
View 9 Replies
View Related
Feb 19, 2008
I have data in columns a - d; the number of records can vary from a few to a huge number which (to me) makes the problem more complex.
I need a macro that will copy every 3rd record into rows e - h; and every 5th record into columns i - l, etc.;
View 9 Replies
View Related
Apr 5, 2007
I am trying to copy partial data from Column A into Columns B and C, then I want to delete Column A without affecting the results in Column B and C. Column A consists of a stock number in the format "1234-56-789-0000" (including the dashes) and is formatted as TEXT.
I'd like to paste the "1234" from Column A into Column B and the "56-789-0000" from Column A into Column C AND remove the dashes (-). I need to do this for the range (or number of rows) that is populated in Column A. This could be as many as 50,000+ rows of data. After this is done, I want to delete Column A. I tried using the LEFT and RIGHT formulas, but they are dependent on keeping Column A intact.
........... A .................. B .............. C ......
1234-56-789-0000 ..... 1234 ..... 567890000
and then eventually like this:
. A ............. B ......
1234 .... 567890000
On occasion, leading zeros in Column C have disappeared, and it's necessary it stay in a 9-digit format. There are 2 additional columns of data, but they shouldn't be affected by the above.
View 5 Replies
View Related
Jan 17, 2008
I'm trying to write a macro for extreme value analysis. I need a macro which will do a number of things! the attachment should explain it.
View 2 Replies
View Related
May 27, 2014
Basically, i have a workbook that contains 2 worksheets with stock data for two stock holding sites, we're consolidating our stores and need to confirm what stock is held at both sites. I need to compare column C on both sheets to find if an item exists on both sheets, and if so copy the entire row from Sheet 2 onto the next free row on a new sheet. Col C on each sheet contains the stock number, however, on Sheet 1 the stock number is abbreviated ot the final 7 letters, with Sheet 2 showing the FULL stock code.
I need whatever is in "Sheet 1:Col C" (for example '1234567') to be compared to whatever is in "Sheet 2:Col C", and if a match is made (for example 9999-00-1234567) then the row containing the match be copied to the next free row on Sheet 3.
View 1 Replies
View Related
Jul 30, 2013
Is this possible using code: Copy Cell A1 and Paste in first empty Cell down Column D. This would be connected to a command button. Both Cell A1 and Column D are on the same spreadsheet.
View 3 Replies
View Related
Oct 2, 2007
I have a range that is filled with formulae and the the number of rows in the range with data changes as the formulae reevaluate the inputs. So the rows with visible data changes between one row and 200 rows. By looking at the output in the first column of the range, Col M and finding the last row where the formulae returns data, I need a way of selecting across the columns of the range (M1 through to Qi) where i is the number of rows that have data in.
View 8 Replies
View Related
May 8, 2008
I am developing a spreadsheet that, once all the code is run has numerous sheets added. On these sheets I have a significant number of rows that contain no data and could be hidden (I dont want to remove them, because later I need to re- import all these rows back to my master sheet). I tried code I found in the forum to hide a row if it is empty but this doesnt work as some of the cells contain formulaes referencing back to another sheet that is hidden.
I am trying, and failing, to write code to hide a row that contains no actual data, but still has formulas in some of the cells.
View 4 Replies
View Related
Nov 12, 2009
I have had much assistance with this complex (I think it's complex) macro in the past, however, there are still a few bugs that need to be worked out in order for it to be completely efficient. Any help would be really appreciated, and I'll do my best to explain what's going on at this point:
I have a worksheet called the 'Data Entry Page'. You will find it attached. Here are the requirements:
The PO Number cell will only allow ten digits with a dash, and will receive values such as 1200012345-1.
The Part Number cell will receive a value which will usually be six digits long with a dash, and will receive a value such as 654321-1.
The Identification Type cell will receive at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):
I
I I
I I I
IV
IA
I IA
I I IA
IVA
or (some combination examples):
IA & I I I
I I & I I IA
I VA & I IA
View 13 Replies
View Related
Oct 21, 2008
I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this:
Column A Column B
1100 Jay
1101 Jack
1102 Jackson
1103 Jacksony
1104 Jefferson
For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2.
If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.
View 9 Replies
View Related
Apr 9, 2009
I am looking for a macro to delete entire rows based on the a partial text contained in column A. For example, I have a list of names that I need to keep but I need to delete the rows in which column A contain cells starting with the following.
BOOKLET 201, PROMO
LOTTO, PRIZE 2
PBT 2, STW WEEK 4
the numbrs in these cells increment therefore I can not make it a specific to the cell but need to select based on Booklet, Lotto and PBT.
View 5 Replies
View Related
May 20, 2014
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
View 11 Replies
View Related
Mar 2, 2008
I tried searching for code for each part of the task separately and trying to piece together multiple macros, that did something similar, but I’m not have a lot of luck, so I’ll break it down into two treads, I hope this will follow the rule of one question per post, but if I should have separated this request even farther as two treads, please let me know and I’ll be happy to comply.
I was given a spread sheet with a macro that loads data from a SQL Data base (not mine), it returns anywhere from one row to 100’s of rows data. Since it’s not my spread sheet and macro, I copied this sheet to my destination workbook. The Data loads in 7 columns starting in B4:H4, with the column headings titles: Acct#, Id#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals. Data cells in spreadsheet not formatted (general)
What I’m trying to do:
part 1:
I need a macro to copy the data only for columns Id# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another sheet. The destination sheet range starts with rows B13:G13, with formulas below and to the right in columns H13, and J13:N13
(column I is blank).
The formulas below the range are a subtotal of the copied columns (D:G). They could be remove and totaled after they’re copied, so there's nothing below the copied range? Since I need the column totals as a subtotals, my thought was to have the row heading and formulas already on the destinations sheet and just insert a blank rows between as each row was copied?
part 2:
As each row of data is copied from the source sheet, I need the macro to copy the formulas to the right of the copied range (H13, and J13:N13) on the destination sheet down for each new row of data copied from the source sheet
View 10 Replies
View Related
Oct 10, 2008
I have a database that I need to extract information from. Column "D" has a string that runs from 25 to 50 characters long, and I need to pull specific information out and need it to be inserted into a new column for each row of my database. The data that I need to pull from column "D" is always located between the first occurance of "=" and the first occurance of "&"
View 2 Replies
View Related
Jun 14, 2014
So i was asked to do a spreadsheet for "counting" electrical items from a project. For instance, quantity of cables, cable trays, electrical equipment, etc.
One of the sheets is called "Cables" and is structured in a database form, i mean, the first row contains the name of the columns (fields) and from the second row will be the records (much like a table in Excel).
One of this columns has to be calculated, for example, Qty (B3) * Measured (C3) = Subtotal (D3). If i put the formula in the whole column filling D:D the size of the excel file grows up to 10 mbs. So to keep the size small, I want Excel to introduce this formula only if it can find a value in Qty (B3).
Is liking adding records in Access, the table should grow with each row completed. We cannot use access, it has to be something the team can handle (they know a bit of Word and Excel, nothing else).
View 2 Replies
View Related