AS you can see in the attached file, I have several collumns of data going form 2012 to 1992. I would like to know the quickest way to invert this so the data is presented 1992 to 2012.
View 5 RepliesI am trying to develop a macro in excel that CUTS AND PASTES rows of data from one worksheet in to the next available row in a new worksheet if there is a date in a particular cell. If the cells that are being checked for a date are empty, then the routine would skip these rows of data.
Here is an example of the worksheet. All rows containing a date in the Scanned column, would be cut and pasted in to a new worksheet. And any rows that have an empty cell in the Scanned column are ignored. If the Scanned column is filled at a later date, then when the macro is run, the data in that row would be cut and pasted in to the next available row in the new worksheet.
TasksNameIDRespPhaseDueScannedImportedSamLLLLO119/04/201003/04/2010KM12/02/2010BMJoe123DH1JOD12/02/2010BMAnn456WS117/03/201003/03/2010JOD12/02/2010BMJohn789DH401/03/201015/02/2010JOD12/02/2010BM
I've set up a spreadsheet structure at work which is three workbooks linked together.
I created this initially within My documents saved locally to my machine. Now though, I need to move the folder containing these workbooks onto the server to be accessed by others. My problem is that when cutting and pasting the folder, the formulas within the three spreadsheets still refer to their original location within My Documents rather than following the folder to their new location.
I am trying to cut all rows with the date less than 2 days older than the current date. Cut and paste it into a new worksheet in same workbook and save it into a specific folder.
[Code] ....
I am getting an error on the line :
[Code] .....
Saying Object doesn't support this property or method.
I am baffled because one worksheet I can cut/paste and none of the reference cells whether they are dependents or precedents change--in other words, all formulas remain exactly the same. I switch over to another tab in my back-end and the formulas change when cutting/pasting cells.
View 1 Replies View RelatedIn column A I have the age of a sale i have completed (compared against todays date) and from B - N of the same row i have the data that coresponds to the sale. As Column A changes daily by 1 i would like to only keep the rows whereby column A is between 1 - 10. For any that are above 10 I would like to cut and paste to a second sheet, strangely entitled "older than 10 days" !
as there are formulas after column N which calculates the data i would only like to cut columns B - N of the specific row.
I think I need to create a macro that first sorts column A so I have the oldest data at the top and then a loop to check if these are above 10, i then want to cut from B - N and paste into another sheet.
code to take jobs that are done on one spreadsheet and send them to the next available row of a specified range on another worksheet. I am also looking to incorporate a way to delete the empty rows that are left behind.
I have already made code to select the completed jobs, but am still unsure how to approach the next part. I can't even paste the selected range to another sheet without errors from range sizes.
The following code is done up to the point where I cut my selection.
Sub Update()
Worksheets("OpenGen").Activate
Dim c As Object
Dim rngA As Range
[Code]....
I have some values in column A which looks like those in table which is shown down bellow. I have somewhere in the middle of that column one empty cell. Of course i have empty cells after the last value. I want formulas or macro or anything which will cut that column in that empty cell and all cells bellow the empty one put in column B from A1, and all cells above the empty one put in column C from A1. I tried everything but i have problem because i dont know how to eliminate empty cells bellow the last cell with value.
ABC
117
228
339
44
55
7
8
9
I have a sheet which contains a list of products. I have created a check box next to each name with the linked cell in the adjecent column. I have formatted the cell so you can see the TRUE or FLASE.
I need to be able to select different products and then when I run a macro, it places all of the selected products onto the second sheet. I just need it so it lists the products in column A with no blank rows.
Can this be done using Index/Match? Would a macro which achieves all this be possible?
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
The code below will put "Some text" into column B when data is pasted into column D. This only works when copying data into one cell. If I copy into multiple cells of column D then the code does not run at all.
[Code] ......
i have a excel database made up of about 5000, points of interest every thing from nightclubs, pubs police stations, restaurants, etc all in alphabetical order A TO Z
my database read like this
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F
POINT NAME ADDRESS POSTCODE TYPE DETAILS ONEWAY ST
odeon cinema leicester sq WC2 cinema lve on left no
COLUMN G IMFOMATION
lve cinema on left this is next to the raddison hotel
what i would like to do is change the order so that every thing is listed in post code order W1 W2 W3 W4 EC1 EC2 EC3 EC4 WC1 WC2 etc so that i can search in pacific post code
I have a list of names and they are in the following format :
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
How can i list them in the following order ?
A1
B1
C1
A2
B2
C2
A3
B3
C3
This is quite a long list containing about 5000 names in total.
Say I have a list in Column A, down 5 rows:
test 1
test 2
test 3
test 4
test 5
I want to write a macro which does the following:
1. Count number of items in A, call it X.
2. On another worksheet, insert X number of rows.
3. copy each of the items in A, and uses those items as headings in the new columns.
This is where I get to my problem. How do you copy the items in A, and paste them horizontally across the top as headings, instead of the normal vertical?
I am trying to bulk upload some geo locations for my site, but the data I have is longtude first and latitude after, whereas the site accepts the data reverse, which means latitude, longitude.
that it's possible to change the order somehow.
Here is a example of how it looks:
"(12.595342677449292 55.66937339270993, 12.595097670457731 55.669239938648126)"
I am trying to do 2 things.
1) I want to make a drop down box with 3 differnent sort orders options in it.
- Sort by date
- Sort by Customer
- Sort by priority level
Once they select a view, it will then sort the rows to that view.
2) I want to do a filter box, so if I type a cutomers name, it will filter to only show the rows related ot that customer.
I am trying to number rows based on criteria. when the value changes add a 1. This is what I need to have happen
Apple - 1
Apple - 1
Apple - 1
Orange - 2
Orange - 2
Plum - 3
Plum - 3
I can get an if statement to number everything 1.
I'm using Excel 2010. Is there any solution which cover requirements given below.
1) I've converted a range in to table format (using Format as Table)
2) Need to Prevent pasting on data validation cell.
3) Need to Prevent change in cell format after pasting data (other than data validation cells as mentioned above) from same workbook or other workbook
4) Need to insert row in the table as per requirement.
I have a requirement as shown below, what i want is to have another column which will display the priority of each case name as per the case id value.
The expected result is shown in TABLE 2, TABLE 1 is the requirement, I just want to have some excel formula or vba that can do it.
REQUIREMENT-
CASE_ID
CASE_NAME
REF_CASE_ID
PRIORITY
[Code] ..........
Is it possible to do a custom sort on a column so that I can change the order the way the months are sorted. The order I'm looking for is:
October
November
December
January
February
March
April
May
June
July
August
September
Also the format for the cells in that column is 01-Jan, I don't want to see the year because I enter and sort the date as if the year doesn't matter so it automatically registers as 2014, if that makes sense. Basically I want that order of the months regardless of the year.
I have my Windows settings set up so that each file that I have open in Excel appear separately in my taskbar at the bottom of the screen. So for example, if I have 2 Excel files open, Excel appears in my taskbar twice instead of being grouped together.
I like to use this set up so that I know which file to click on when I'm working between multiple files. The problem is that on occasion, they will get out of order.
So let's say I have file A on the left and file B on the right. Then I save file A. After saving file A, now file B appears on the left and file A appears on the right. This also sometimes happens if I open a 3rd file.
Why does this happen and is there any way to prevent it?
I need to change the list of names in a column to another area but in the reverse order, not in sorted order (eg. I have list of names in A1:A10 and want to make a reverse order of this list in another area).
View 2 Replies View Relatedsee this example of a chart: see Image1.gif (the right thumbnail) as you can see 2 different types of graphs are combined: "lines with markers" and "clustered column".
in the legend one can see that the order inside the legend is clustered by the two types. within a type i can change the order easily (right click on a line or column - format data series - series order - up or down).
question: i would like to change the order freely. how to do this? for the order inside the legend i actually do not care at all, whether a representation of the data is a line or a column. i only want to make it as readable as possible. example what i'd like to get (made by handmodifing the picture with a drawing program): see Image3.gif (the left thumbnail)
My starting point is a big data set that needed to be validated by several users. I split the data set into smaller sets that were sent out. All users have filled in the same column called "Input"
What I would like to be able to do is to paste their input into a single column on my original data set and keep the name of the input next to it. Is it possible to paste without overlapping prexisting data ? Something of the likes of F5 ("Select blank cells/text") ?
I've attached a simple file with dummy data to explain myself.
So, I have a document 25 pages long. It has 26 sections (1-26) and each section has up to 10 (.1 - .10) sub sections which contain 16 (a-p) sub-sub-sections. There are 6 columns of varying widths. Some cells, in a row, merge into adjacent cells.
I would like to break this document into 26 documents - one for each section - so that I may recombine and regroup them in the future incorporating a few modifications. Whenever I copy and paste to a new Workbook, I loose the formatting of column widths. Resetting these alone takes lots of time.
I have come up with is to duplicate the entire document and delete everything above and below the section I would like to save. Then repeat for next section. There's got to be a better way?
I am trying to copy several columns into another sheet within the same workbook.
I want to copy Columns 1,2, 15, 19, 23, 40.
I don't know how to work with arrays.
I have a sheet made into a form with some check boxes included. If I select all the relevant columns, create a new sheet, and paste into cell A1 - all of the information carries over just fine. You can still select cells and check the boxes in the new sheet.
However, when I record this action as a macro, using the macro produces a picture of the selected columns instead of actually copy/pasting the information. I can't click on the cells or check boxes. I can only move the picture around within the new sheet.
I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?
With cht
.ChartType = xlColumnClustered
' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With
I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.
I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.
Basically, I have two columns:
In column D I have some postcodes, and in column E I have areas. They are all with their according area, but I have them mixed up.
So: How can I put Column E in alphabetical order so that Column D shifts accordingly.
Example:
POSTCODE1 --- North West
POSTCODE2 --- South East
POSTCODE3 --- North West
I want it to shift them so it ends up like this:
POSTCODE1 --- North West
POSTCODE3 --- North West
POSTCODE2 --- South West