If I type in
=A1
=A6
highlight both cells, and drag down from the bottom right corner, instead of counting every 5 cells, it assigns the next one as =A3 like so:
=A1
=A6
=A3
=A8
=A5
=A10
Why does it not see that A1 and A6 are 5 apart and do something like this:
=A1
=A6
=A11
=A16
=A21
=A26
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
Copying data that is in one coloumn in an unsorted order to another file where I have the values in a specific order I want. There I want it sorted in a new row each time I click a button in the first file.
I have a script that does this in a new column each time. I need it in a new row in a table to keep the graphs auto updating without having to use a macro in the end file. Current VBA that copies it to a new column instead of a new row in the table (modified to remove file names).
How the current VBA works. It refreshes the file it is in to update the data from SQL, then opens the other file, copies it over using sumif compared to the first coloumn in the file. Adds the current date, copies everything it added and replaces it with values so the formula isn't kept once it saves and closes the file. It then saves the start file and stops.
Code:
ActiveWorkbook.RefreshAll
Workbooks.Open Filename:= _
"File2.xlsx"
[Code]....
I have problems copying data (from notepad, values are in scientific notation) into Excel 2010 worksheet. This problem only occurs with one of my laptops. I also tried this on my partner's laptop and no problem at all.
My new laptop (which I would like to use in the lab) has Windows 7 professional installed on it. I bought my laptop in Austria/Germany, so I changed the language from German to English. I then installed Microsoft Office 2010 on my laptop (which I am also using on my main laptop- without any issues). I have changed my default language to English UK.
The issue is as follows: Here is a small sample of the data from notepad
3.33343e-03 1.51357e-03 0.00000e+00 0.00000e+00 4.96507e-01 3.84643e-03 6.24332e+00 1.81305e+03
I select & copy the data from the notepad (also tried notepad++) and paste it into the Excel 2010 worksheet. This is what I get in Excel:
3.33E+02 1.51E+02 0.00E+00 0.00E+00 4.97E+04 3.85E+02 6.24E+05 1.81E+08
When I use another spreadsheet package (MagicPlot Student version), there are no problems. I have also installed Notepad++ and experience the same issue. So somehow Excel is increasing the value by 5 orders of magnitude. When I copy the values from another Excel sheet into a new Excel sheet, there are no problems. I have re-installed the Microsoft Office suite several times and the problem is still there. I can't re-install Windows 7 as I don't have the installation discs.
I have extracted a lot of data (from a webpage), onto Sheet 1 of my Excel 2010. The results I have obtained of this data I have extracted might occupy cells A1 to F1.
I want to keep doing this over and over, copying and pasting data from a webpage onto Sheet 1, then obtaining various results and adding those results under cells A1 to F1, which would of course be A2 to F2.
Is there any way these results can be transferred to say Sheet2, but of course occupying a new line each time so that at the end of the day, I have a list of results.
Of course I could just copy and paste them to say Sheet2, under each other, just wondering if there was some tricky way.
I have some data coming in from another source system which has the date format as "DD-MM-YYYY" but while pasting it to Excel (2007) sheet some date fields has just DD-MM-YY format which disrupts my macro. I did changing the language settings to "English(UK)" ,which i wanted, and changed the same in Excel options as well. But unfortunately it doesn't work.
View 3 Replies View RelatedI have two workbooks I am using. Essentially I am copying values out of one workbook, opening a second workbook, manipulating data and pasting it. Everything is working great but one issue! The first workbook has named ranges in it that I don't want in the second workbook (just values). I don't want them because when I try this operation the second time it asks if I want to use the same name or choose a different name. For some reason it is still copying over all the named ranges (all 343 of them!). Is there a way to not allow it to do that or simply just delete the names before I close the second workbook?
Win 7 64, Office 2010
Sub CopytoDB()
Application.ScreenUpdating = False
Worksheets("Setup").Select
Range("A2:A766").Select
Selection.Copy
Workbooks.Open Filename:="D:Server MirrorDatabaseSetup Database.xlsm"
Worksheets("Database").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
[code].....
I need something that will take data from columns in one Spreadsheet and put in difference cells in a row. I know this could be done with recording a macro but the number of column will never be constant.
Below I attached examples of the Spreadsheet
Financials SpreadSheet
Need to have the data in column B to F put their respective cells in row in the
Master Spreadsheet
So we would have 5 rows.
Excel 2010ABCDEFGH5Job Number17542000250030003500Total6Date Booked01-Mar-1215-Mar-1215-Mar-1223-Mar-1223-Mar-127SalespersonJames ThorntonHoward StandenHoward StandenIan BullimoreSylvia Walton8AdvertiserNestleTalkTalkLloyds BankSkodaHonda9ProductNature ValleyBroadbandMortgageApril
[Code]....
I am using Excel 2003.
I have 2 worksheets.
Worksheet 1 is called "Master List Data". Every cell within this worksheet contains a formula so that it matches the cell value contained in a worksheet held within another workbook.
The formula for reference is as follows:
=IF('[CCL Breakdown.xls]MASTER LIST - Active Customers '!A1="","",'[WFX CCL Breakdown.xls]MASTER LIST - Active Customers '!A1) .
e.g.
If Cell A1 on Master List-Active Customer contains no data, Cell A1 in Master List Data would be blank.
If Cell A1 on Master List-Active Customer equals John Smith, Cell A1 in Master List Data would display John Smith.
There are currently non-blank values contained in cells A4:A750. But next week there may be non-blank values is cells A4:A790 (i.e. it will grow each week)
Worksheet 2 is called "Master List Flat. At the moment, I am manually copying and pasting the rows which have non-blank values in cells from Column A from Worksheet 1 into this report (e.g. A4:IV4).
I would like to automate this process and I have created a Macro, but I do not know how to word it so that it will only copy cells with actual data in.
The Macro I have written is below:
Sub IMPORTANDFLATTENDATA()
'
' IMPORTANDFLATTENDATA Macro
' Macro recorded 01/10/2012 by walesb
'
'
Application.ScreenUpdating = False
Sheets("Master List Flat").Select
Rows("4:759").Select
[Code]....
I have an master excel file with 20 sheets with names x,y,z,a,b,c,f,.... Each and every sheet has data which start from Row 7 and Column 2. Now i need to consolidate this data in one sheet in another excel file.
Consolidation should be like
Suppose X sheet has 20 rows and 4 columns of data which starts from Row 7 and Column 2, this data has to be copied and pasted in my new excel file copied on my desktop. Now first 20 rows are occupied in new excel file.
Now code should move on to master excel file Sheet Y which has 45 rows and 4 columns of data which starts from Row 7 and Column 2,this data has to be copied and pasted in my new excel file from row 21, which means Master excel file sheets has to be clubbed to one consolidated excel file.
In All the sheets in Master file Data starts from Row 7 and column 2.
Data range varies row wise in each sheet but column length is fixed to 4.
I am trying to develop a code which extracts the data from text files inside a folder (Folder test in my desktop) into one sheet. The Macro is in the workbook “Text Extract” which is an excel 2007 file. The data of each text should be copied to Sheet1 of this workbook one below the other. For testing purpose I have kept only one text file in the folder and was trying to copy the data from the text data extracted sheet to Cell A1 of Sheet1 of workbook “Text Extract”. The code works fine till copying the data, but shows below error in the line “Selection.Paste”:
Run time error 438: Object doesn’t support this property or method.
Below is the code:
Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
[Code]....
I have attached a workbook (excel 2003), I have few userforms in it.
I tried to copy data from all the tabs in the workbook to "Master" tab but getting an error.
You have to login to file details are as below:
My Excel file crashes whenever I attempt to make a copy of a tab, within the same workbook, and then save. This problem only occurs with its existing naming convention. In other words, the file crashes with the name "Cash Flow Model 5 Year Plan". If I rename the file to "Copy of Cash Flow Model 5 Year Plan" then it works perfectly, with no issues copying a tab within the workbook. The file size is not too large, as this issue never occurred when the model was twice the size.
View 1 Replies View RelatedI am trying to write a macro to do the following. I have data validation list in column G which will come back as either "Closed" "Pending" or "Completed". What I want to happen is when anyone changes a value in that column to "Closed" or "Completed" I want to cut the entire row (Columns A-G) and paste those rows on the "archive" tab which exists in the same worksheet. Then on the main sheet, I then need excel to shift the contents of the row beneath the row that was just cut up to fill in the blank row.
View 4 Replies View RelatedI'm having a problem with copying numbers from web browser to excel.
For example I want to copy number 2013072210000117 but Excel displays it like this 2013072210000110. I tried to change Cell formatting but no success.
I'm trying to write a macro that looks in cell A1:VGH1 for the text "Name:" within the row, then copy that cell and other following cells and paste into a new row. This macro can loop through the row until all instances of the text "Name:" and the following cells are on their own row.
What I have currently.
Name:xxx From:xxx Name:xxx To:xxx From:xxx Name:xxx: To:xxx
The result
Name:xxx From:xxxx
Name:xxx To:xxx From:xxxx
Name:xxx To:xxx
I have code that is trying to open a word document and paste it into an email body. If I use plain old control v it works fine but using the method below, I loose my images and my formatting. Is there a way to use send keys here or some other method to preserve my formatting when getting the text to the body of the email?
Code:
Private Sub DREmail_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim attachmentQ As String
Dim oWord As Object
Dim wdapp As Word.Application
Dim DRloc As String
Dim DRText As DataObject
[Code] ........
(Excel 2007). I have a report that I download which looks like this: The actual report has about 10000+ lines
I don't really want a macro for it but a formula that would copy the name in column A down in Column C but only to the point where the next name starts. So for example James Brown would be copied down in Column C till it reach Account Number 81914 and so on?
A B C
NameAccountJames Brown16836546259386014759716624957862380714557311623681914Steve Smith825168050422745213781459686708210378813700484308138459Holly Wood152663787051472924045442366165332455059500133525111211
I am running Excel 2007.
I have set up a table and for ease of explanantion Column C has a formulas in it to add together the values found in Column A and B.
EG C3 foumula = "=sum(A3+B3)"
Now in cols d & e I have manually entered figures and in colum F I want to add up those figures so F3 should read "=sum(D3+E3)"
In the past I have always been able to just copy the cell C3 and paste it into F3 and the formula would automatically copy and offset the various cells to the correct cells for the new posiitoning.
However, for some reason when I copy the cell, it now pastes the value only into the pasted cell and does not copy the formula, if I click on paste special to try to just copy the formula I now get a pop up asking me if I want to paste as Unicode Text or Text.
I have no recollection of changing any settings.
I am creating many tables in excel using a macro, and I want to copy them into a word document for later use. All the tables come from a long list, so for simplicity, they all get created on the same range starting at F1. Before deleting the existing table and adding the new one, I'm trying to copy/cut the table and paste it into a word document.
In case it makes a difference, I have office 2007
VB:
Sub ExcelToWord(LastRow)
Dim objWord As Word.Application
Range("F1:F" & LastRow).Copy
With objWord
.Documents.Add
.Selection.Paste
.Visible = True
End With
End Sub
cannot seem to get a function that works.
I have a sheet named "Master" with student reference numbers (A2), student names (B2/C2) and their four subjects (D2, E2, F2, G2).
I need a separate sheet for each subject, so potentially 30 additional sheets, and i would like for where a subject is mentioned in one of the four columns of the master, the students reference and names would then be copied and pasted into a row on that subject specific sheet. Leaving the subject sheets with a list of names and numbers of the students studying.
So you would have a student on the master sheet and then they would appear on four additional subject sheets.
Ideally it would also update subject sheets as new students are added. And additional information is being added to the subjects sheets so they cannot be cleared.
For Mac Excel 2011, though I think my question would apply to any modern version of Excel.
This is a little odd. I have a sheet where, for various reasons (see below ### if interested), I need to copy a hyperlink, replace a substring, and then return the new hyperlink (with old friendly text) to the original cell. (I actually have to do this with an arbitrarily-long column of links.) This was my plan of attack:
* Pull the URL from the hyperlink cell.
* Use FIND and RIGHT to strip out what I need and replace it
* Use HYPERLINK to build a new cell with the original friendly text and the new hyperlink.
* (Manually) copy this new link (really, a column of links) back to the original cell (really, column of cells).
Here's where it breaks down:
a) If I simply cut-and-paste, the formula used to pull the hyperlink becomes circular (as do several others), and Excel complains.
b) If I cut-and-paste-special (values only), I get just the friendly text, no link info. Likewise if I try to reference the "fixed" hyperlink.
The erroneous hyperlinks are in column K. My calculated correct hyperlinks are in column S. (There are similar errors and fixes in L and T, respectively.)
MS Office Professional Plus 2010
Excel 2010, 32bit
When making a copy of a sheet within the same workbook, I receive several messages "A formula or sheet you want to move or copy contains the name . . ."
How do I identify and remove these names?
How do I prevent new ones from being generated in the future?
I used the button in Excel 2010 to record a macro that allows me to format a workbook font and stuff because i do that many times a day for several workbooks. However I want to be able to just push a button and have all open workbooks run the macro I recorded.
I am able to make a button- thats easy, but how do I get it to run for ANY workbook? My problem is that apparently it only runs the workbook that is named a certain way. should i post the code here?
Scenario is - n number of files are kept at a location say D:excelfiles
Each excel file contains, say, 10 sheets among them one sheet is called "custom".
Example -
file 1 - sheet1, sheet2, sheet3, custom, sheet5, sheet6
file 2 - sheet7, sheetB, custom, sheet9, sheet10, sheet11, sheet12
file 3 - sheet13, sheet14, sheet15, sheet16, custom, sheet17, sheet18, sheet19
and so on.
Requirement - all cell values of each "custom" sheet should get copied into a master excel sheet in file "import-sheets.xlsm".
To achieve this I started with copying each custom sheet from all excel files to the "import-sheets.xlsm" but I'm getting error.
Any way to directly copy paste the cell values of each "custom" sheet into a single master sheet of excel file "import-sheets.xlsm".
Attachment - dummy files to test macro. Macro name is copypaste.
I receive an run-time error message 1004 "autofill method of range class failed" in the following setting, in the 2nd line.
In this instance, I want to copy 1 cell from the cell 2 columns before.
Code:
Range("M3").FormulaR1C1 = "=RC[-2]"
Range("M3").AutoFill Destination:=Range("M3"), Type:=xlFillDefault
I am trying to copy two columns to a different sheet in vba code. I will ultimately need to do this multiple times but I think I have the iteration part ok, it's just the copy part that seems to be wrong.
Code:
Worksheets("Lookup").Select
Range(n & lngRevStart & ":" & n + 1 & lngEndRow).Select
Selection.Copy
Worksheets(strDeptSheet).Select
Range(Columns(o), Rows(m)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
The original code I used for this works great on one column but I knew the letter column each time I used it. Now I am trying to get two columns at a time and will be iterating through several based on user selections. I figure that will be faster to do both columns as the two columns are side by side.
I am getting an error on the PasteSpectial line "Run-time error '1004' PasteSpecail method of Range class failed.
I'm not sure where to go with this but I am assuming I have a problem with the Range selection lines on either the source or destination or both. The support is just for Macro errors.
I need to copy a column of data from Sheet1 to Sheet2, but skipping every 5th row in the original data on Sheet1, but without leaving any gaps in the resultant column in sheet2.
At the moment I've been manually changing the formula but there must be an easier way.
eg. Copying data from Sheet1, A1 to A11, I would have in Sheet2, starting at A1 :
=Sheet1!A1
=Sheet1!A2
=Sheet1!A3
=Sheet1!A4
=Sheet1!A6
=Sheet1!A7
=Sheet1!A8
=Sheet1!A9
=Sheet1!A11
Any formula I can enter that would be dragable and still skip every 5th row ?
Windows XP, Excel 2003
I Have a macro which combines five excel sheets from five workbook into single worbook with five tabs containing five excel sheets.Everything is fine except that color of all five excel sheets is totally changed in the combined excel workbook.I tried this manually i.e copying excel sheet from each workbook and pasting the same in the new work book.
Here also color comes correct in only one excel sheet and rest all sheets color is lost.Please any one knowing solution to this problem explain. macro is used is shown below
Sub CopySheets()
Dim fileFolder As String
Dim fso, fsoFiles, f1
Dim w1 As Workbook
Dim w2 As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim flag As Integer
Set w1 = Application.Workbooks.Add
Application.DisplayAlerts = False
Application.ScreenUpdating = False
fileFolder = "D:documents est est" '
I have a workbook "revision.xls" i have two excel workbooks "A.xls" and "B.xls" in a folder ( revision ) in my desktop. the location of this folder is " C:Documents and SettingskrishnaDesktop evision )
Both the excel files inside this folder ( "A.xls" and "B.xls" ) have columns B to F in each of them filled with some data.
The no.of rows upto which the data is filled in these two files can vary.
What i want is a macro which can open both these files automatically and can copy the contents in column B to L in both these excel files into revision.xls in such a way that the column B TO L of the excel file alphabetically first in the folder has to be copied to A to K column of revision.xls then after leaving one column blank i.e leaving L blank column ,the column B TO L of the excel file alphabetically 2nd in the folder has to be copied to M to W column of revision.xls.