Excel 2003 :: Macro To Insert Cells When Paste Data
Jun 11, 2013
I have two spreadsheets in Excel 2003.
Spreadsheet 1 has 10 columns of data (A-J). I want to copy a variable number of rows from spreadsheet 1 to spreadsheet 2.
When I paste into spreadsheet 2, I'd like to automatically insert blank cells in three places, taking the total number of columns to 13. I'd like columns C, F and I to be blank, and the last column with data to be M.
I will perform this task regularly, and add the copied cells to the bottom of spreadsheet 2, so I'd only like to insert blank cells within the range that I'm copying, not the entire spreadsheet.
I will then populate the blank cells with a VLOOKUP function. Do I need another macro to automatically add the formula to the cells, or is there a way to include this in the cell-inserting macro?
View 1 Replies
ADVERTISEMENT
Nov 27, 2012
I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.
Currently, I have a column that has the correct ranges but finds the average for each month
(=AVERAGE($H7214:$H7243))...
And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.
In effect, I am looking to solely switch the begining of the column command
(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...
While keeping all of the specified ranges from the AVERAGE column.
View 2 Replies
View Related
Dec 19, 2011
I need to find the last instance of "IO" in column E and copy cells in columns B to E for the row below to another tab called "OP" cell O9.
I need the VBA code for Excel 2003.
View 1 Replies
View Related
Feb 23, 2008
I often need to use Paste Special . .. Values, using Excel 2003. So I recorded a Macro into "Personal.xls" and assigned a shortcut key combination "CTRL-Shift-V" so that it would always be available. I first select one or more cells and hit CTRL-C to copy to clipboard, click on another cell, then hit CTRL-Shift-V to run the following macro:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
It often works . . . and it often fails. When it is working it seems to keep working over and over. When it is failing it keeps failing. So it is almost as if there are two "modes" of operation, which for lack of terms, for this post I will coin the terms "fail mode" and "fixed mode". Once I am in one of the two modes, it stays there for a while. When it is in the "fail mode", I always get the dreaded:
Run-time error 1004: "Pastespecial method of Range class failed".
Since this problem is very intermittent, I believe this is why there are so many posts in the Internet about this, and so many people are replying back "it works fine for me". I have seen many solutions offered but none work so far for just a simple shortcut key to do a PasteSpecial :Values. Some solutions "work" . . . such as adding the PasteSpecial button to the Toolbar, or pressing ALT-E-S-V <Enter> - but both of these bring up the Paste Special dialog box which I want to avoid.
I spent days trying the many solutions offered but so far all I have found is a trick to convert Excel from "fail mode" to "fixed mode". The trick is to add a line to the macro to first do a "Paste Special: Format:
VB:
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Then select and copy any cell/s, click on another cell, and run the macro just ONCE - it will paste the formatted value successfully and you are now in the "fixed mode". Now you can remove the PasteSpecial:Format line that you just added, to get back to the original macro with just the "Paste Special: Values" line:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Now Excel is in the "fixed mode" and the original macro will once again start working properly. BUT only for a while !! Then some time later, usually within a day or a couple hours . . . Excel goes back into that "mode" where once again the macro fails repeatedly. I have been unable to isolate what causes the mode to return to failure.
Any simple way to either change the code so that it ALWAYS is successful, why this often is not successful ? I believe that the fact that adding in the Paste Special : Format code and running it just once to fix the problem, also could be a clue as to what causes this problem to "sometimes" occur.
View 9 Replies
View Related
Nov 24, 2012
Copy over data from different workbooks and using paste special values to paste it into a new workbook using a macro. Here is what I have and what I am looking for:
My file path is
C:Documents and SettingsMy DocumentsProjectCostsDecember12
In this folder I have workbooks called:
Function1
Function2
Function3
In each workbook I have 4 worksheets
Cashable12-13
NonCashable12-13
Total12-13
GrandTotal12-13
I also have a workbook called DecMonthlyTotal in the same folder with the same named worksheets.
I am looking for a macro to be placed in the DecMonthlyTotal that will pull the data from the Cashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name Cashable12-13, it will also pull the data from the NonCashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name NonCashable12-13
Both the Cashable12-13 and the NonCashable12-13 have Columns A - G The row that the macro should start the copying from is Row 3 for each of the workbooks; however I don't have an end row for the workbooks as this will vary.
I am using Excel version 2003.
View 1 Replies
View Related
Aug 23, 2012
I'm looking for a way to write a macro to insert 5 lines at the end of the data in column A. Then I want to copy a range into the newly inserted lines. I would press a button anytime I need this to occur.
Current last line of data A39
Need to insert 5 rows after A39
Then copy range BA30:CB34 into the newly inserted rows.
View 4 Replies
View Related
Jun 8, 2014
I have a dropdownmanu in sheet1 with different countries taken from Column A in Sheet3. I need a macro to run when i select a country example Denmark. It will fill out transmittal code and also country code in named cells for it in sheet1. Info taken from Sheet3
Transmittal code is in below testsheet in Sheet1 cell E12.
Country code is in Sheet1 cell.
But this have to be possible to change. Also the range for the country have to be possible to change.
This vba code i need to run as soon as i select a country in the dropdown manu.
But one thing i would like to solve also is. When i select a country it will create a dropdownlist in I13 taken from the info in column, i have in Sheet3 column F. So if i select example Denmark, it will show a dropdown menu in sheet1 I13, with the ledger codes 10 and 6x. I have tried to make this work but cant make it work good.
I use excel 2003. Please have a look and upload the testsheet back.
View 14 Replies
View Related
Feb 5, 2012
I have three workbooks that contain various types of information. I have an Overtime workbook that lists employees and calculates the straight time and overtime worked for the day. This is the main log that information will be pulled from.
I need writing a macro that will copy over information to an Absent Log workbook and a Production Model workbook.
The Absent Log workbook:
This workbook contains worksheets from each month. I need excel to do a lookup of the name in the Overtime workbook and copy the information from the "OT" column but if that column is empty I need it to pull the information from the "Personal DT" column.
The Production Model workbook:
I need this workbook to automatically pull the total straight hours and overtime hours to the production model for the correct day. This will also pull from multiple sheets.
I am going to be writing this in Excel 2010 but it will be primarily used in Excel 2003.
I uploaded the workbooks to filefactory.
OT Workbook: [URL] ......
Absent "Log" workbook: [URL] .....
Production Model: [URL] .....
View 1 Replies
View Related
Mar 6, 2012
I have a workbook that has 30 tabs in it. Each tab is a report card for students. What I'd like to do is create another tab with a button on it that when I hit the button it will search through each tabs range of D12:D40, D48:D76, D84:D112, D120:D136, J12:J40, J48:J76, J84:J112, and J120:J136. And if any of these cells has an MS in them then this new sheet I have created will list each students name which is in cell E5 and list what they recieved the MS for. This will be in the same row number but in column B. So if cell D12 has an MS in it then this report will list the students name and what's in cell B12.
Windows XP
Excel 2003
View 3 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Aug 3, 2012
I have unprotected cells in excel where I need to change the content (number) and cell color, but I don't want the cell borders to change. I'm a beginner with vba. I've tried protect sheet and workbook options, but looks like I need a vba code and don't know where to start.
Win 7
Office 2007
View 4 Replies
View Related
Jun 10, 2014
So essentially I need a VBA Macro code for Excel 2003 that can take raw data that is input every month via a text document and move it to the next sheet in the workbook. This data is essentially broken down by a "platform" in the raw data section ("P") with monthly totals for each month but it is broken down by platform in total which goes in descending monthly order on the "dat" tabs (which is the final place for the data).
So what needs to happen is the data for the last two years must go from the "P" tab to the "PDat" tab under the correct platform and the correct spot for the newest month. The data that is input each month is in the same format month to month, just starting two rows down from the last month.
At the end of two years, the oldest year of data must be deleted and the other data moved up to the vacant area so that the sheet never grows too large (i.e., at the end of this year 2012 will be deleted and all the data will need to get moved up).
Attached is a sample data workbook.
View 3 Replies
View Related
Oct 21, 2013
i have a macro that opens 10 other workbooks and copies cells onto a "master" workbook. Until recently, everything worked fine. Suddendly, while it opens the 9 books and copies as it is expected, but when it opens the 10th wb, the macro breaks at a very simple copy/paste.
The weirdest thing is that after it breaks, I cannot select any cells neither in the opened wb nor in the master wb. This continues even after I press the "reset" button in the vba. So, I am only able to select objects (text boxes etc) in my wb and not any cells.
Also, the "arrow" icon on the design toolbar is not active. And I've tried pressing and de-pressing the F8 key, but I still cannot select any cells.
I have option explicit in the beginning of my macro. And i'm using excel 2003.
View 2 Replies
View Related
Feb 24, 2011
I have to file an insurance claim due to a fire in my home. I have a folder with .jpg photos located at C:InsClaimOnlinePhotos . Im working with Excel 2003. I am trying to put the picture of the damaged protery in Column A on the same row as the detail about that damaged property using the ActiveSheet.Pictures.Insert function. The name of the picture is located in Column B. There are multiple sheets in the Excel book and each contains from 10 to over 700 rows. I would like to run the macro based on the active sheet to add the pictures one sheet at a time. I would like the pictures to be about 1.2" High by 1.6" Wide and be actually be within the cell of Column A of the row where the detail information is located. I have played with writing my own code, pieces of others code and full code sets I found in the Forum. Nothing works. Below is the best I found so far but, it uses Column C for the placement of the pictues.
When I try to run the below code I get Run-Time Error "1004" "Unable to get the insert Property of the Picture class".
I'm not sure if this code should work in Excel 2003 or not.
View 11 Replies
View Related
Oct 6, 2011
I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.
I had a go at the code, but keep getting Runtime error 13.
I'm using Excel 2010
Code:
Sub new_entry()
'
' NEW_ENTRY Macro
Dim rowNo As Integer
[Code]....
View 6 Replies
View Related
Apr 11, 2013
Excel 2003 / WinXP. I have a list object with 5 columns, A - E, and all cells have a border round them. I need a macro which will:
1. insert a row below the current row (whatever cell happens to be selected)
2. then remove the left hand border of the cell in column B in the new row,
3. then select the adjacent cell in column C in the new row ready for data entry.
Obviously I cannot specify any absolute addresses in the code, and this is where all my attempts to record a suitable macro fall down.
View 7 Replies
View Related
Jan 8, 2012
I'm trying to use a VBA Macro to copy and paste a row of cells from 14 worksheets into in another worksheet in a Colum. The worksheets are entitled Data 1, Data 2, Data 3 and so on until Data 14 and the worksheet that I want to paste the data to is entitled sheet 1. The row of data that I want to copy and paste is in the same place on each worksheet C4 - IR4. I can do this using the Macro recorder but there is a lot of code and ultimately I want this to be part of a much bigger Macro once I get better at figuring this stuff out.
I have read that this can be done by using a Do Until and then using a loop. By the way I am using Excel 2003 with XP.
View 6 Replies
View Related
Nov 1, 2010
I made a planner for shared use.
Is there any way users can cut and paste while keeping the Conditional Formatting of the source cells intact? It is the same as copy-paste and then delete the source cell contents after copying.
Excel 2003
View 2 Replies
View Related
Mar 28, 2014
I have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
View 9 Replies
View Related
Jul 8, 2008
I’m simply trying to copy all the data on one sheet, open up a new excel application:
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
and then paste all the information to the new workbook. When I try, the charts don’t paste, only the values and formats.
View 9 Replies
View Related
Jul 31, 2012
I would like to have a macro, to copy the cells and paste it 6 times in a column. below are the format required.
Column A
Input
abc
ert
uty
Column B
Output
abc
ert
uty
abc
ert
uty
[Code] ......
View 2 Replies
View Related
May 8, 2014
I need in XL for a macro to run, open my word file, copy the entire thing and then paste it back in XL.
So leaving it general, Word.doc and Excel.xlsx
It's all because my company's POS system will only export to Word but everything we do is in XL.
View 6 Replies
View Related
Nov 25, 2011
I'd like to know if it's possible (Excel 2007) to insert a function/button into a cell, which when clicked, will paste whatever is on the clipboard?
You can see a screenshot of my document here, and I'd like to insert a paste funtion in the red circle. Is it possible?
[URL]
View 9 Replies
View Related
Dec 8, 2012
I have a spreadsheet witht the following design:
Column A = Date
Column B = Value 1
Column C = Value 2
I have a folder with excel files named by date (e.g. 081212 = 8th December 12). Within each of those files is Value 1 and Value 2. They are always in the same cell (B6= Value 1, B16= Value 2).
How can i create a macro/script where the Values in Column B and Column C in my spreadsheet are automatically updated where there is a Date in Column A but no values in Column B or C?
In other words, i need the script to read the date in Column A and if Column B and Column C are blank, then it needs to find the corresponding date excel file and copy Value 1 and Value 2 into the cells in my spreadsheet.
View 3 Replies
View Related
Aug 18, 2014
Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.
View 1 Replies
View Related
Mar 25, 2012
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
View 4 Replies
View Related
Apr 1, 2014
I am trying to adjust the below macro so that it will work in Excel 2010.
Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer
[Code]....
View 3 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Feb 9, 2007
The 'Add new material' button at the SMX sheet paste a range of formatted cells and formulas. The user is suppose to type in the description into the yellow filled cells. The problem is once the user enter sthe description in the yellow filled cells, i need the data to be copied into the FastCheck Sheet. Before that, a new row must be generated to copy for the data to be pasted.
View 2 Replies
View Related
Oct 27, 2011
I am running Excel 2003.I have a spreadsheet with two worksheets, each worksheet has a Refresh button that runs a Data Extract Macro.The Data Extract Macro imports Data from another spreadsheet.I want to have the spreadsheet automatically run my Data Extract Macros for both worksheets at a specific time each week. (Scheduled task) Regardless of whether or not the excel file is open.
View 4 Replies
View Related
