I am currently writing a macro that does a variety of things, one of these being that it automatically sums up columns I,J and K. The dilemma I have is that I need to be able to see the sum of the data in a cell at the bottom of the column, after manually filtering it according to what I want to see.
View 4 Replies
Running Windows XP; Excel 2003
I have a fairly simply macro.
It copies certain sheets out of a workbook (for a new workbook)
Unprotect the sheets in the new workbook
Deletes a particular cell on each sheet
Unhides a sheet
Sets all of the new worksheets to normal view
Protects the new sheets
Lets the user save and name the new workbook.
All is working fine Except it will not change the new workbook from Page Break View to Normal view.
The code I'm using = Sheets("name").Activate
Activewindow.View=xlNormal
Here's the strangest part. When I run the macro from a command button, the newly created workbook retains the page break view (as is in the original).
When I run the macro line by line via F8, IT WORKS JUST FINE
Is there any way i can display the columb number without actually manually counting across.The columbs are A to Z,AA to AZ,BA to BZ,CA to CZ & DA to DN....about 120 columbs.Is there a way to have the columb number diplayed by just placing the mouse cursor on the columb letter (not press though as it will select the whole columb..going to use for VLookup function
View 3 Replies View RelatedI have a sheet where I have a column of cells where I manually enter in a date. the format is d-mmm-yy. Before the end of the year rolled around I was able to enter in say only 12-31 and it would automatically change that to 12-dec-09, now when I do that it spits out 12-dec-10. It's not a big deal to type in the extra -09 to get it right but was wondering if there was a quick/easy fix to get it to know that I don't want a date in the future?
All dates I type in will be in the recent past...never more than 3 months or so old and never later than today's date.
I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:
VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range
[Code]......
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?
View 8 Replies View RelatedI have the file here i work with, basically the first column is a legend and the column to the right of it is a pointer column to help me find out where a legend is located in another file. So i was wondering if a macro could be made to basically find where the "legend column" A, C, E ect ends ( every other column is a legend column , one next to it is a pointer column ). and then combine the ends all of the columns contents and put them into 1 column.
In the file with this question i have showed you what i start off with, i highlighed in yellow where each column legend ends, ( normally these are not highlighted and i find them manually ). In the 2nd tab i show what the end result should be. All the columns are now consolidated into 1 column. 1 after the other.
I have a spreadsheet with multiple columns:
Column B - Host Contact
Column C - Company Name
Column D - Attendees
Column E - Contractor Position
Column B will have anywhere from 1 to 10 names.
Column C will have 1 -2 names.
Column D will have anywhere from 1 to 10 names.
Column E will correlate with column D.
This list will go to multiple people who will want to filter column B for their name. When they filter their name, they should see all values in columns C, D and E.
When I try to filter this spreadsheet by a persons name in Column B, it only shows me the first value in columns C, D and E.
One of the attached pictures shows a mock-up of the unsorted list, the other shows the sorted list.
Sorted Format.PNGUnsorted Format.PNG
I have a macro that copies data from one sheet to another. I would like getting the macro to figure last row used in column B from B3 on and put the word Sum below it. For example, if last row used is B64, on B65 would like the word sum on it.
I cannot just input in as the number of rows could change when macro is run. Data is dynamic.
Here is simple code I have:
Sub Weight()
Sheets("Final Month").Select
Range("A2:B1055").Select
Selection.Copy
Sheets("Final Weight").Select
[Code] ...........
I am trying to do is filter out one column of data with another. Basically if a value from column A is in column B... delete it.
Here is code that I am using:
Sub filter_out()
' filter_out Macro
' this will scan one column and remove cells from it that
' are in it that are in another column
Dim INV As Range
Set INV = ActiveSheet.Range("A2")
Dim MW As Range
Set MW = ActiveSheet.Range("B2")
I keep getting object errors and it fails...
I regularly receive a file with a column called ‘Project Key’ which for some unknown reason can be located anywhere in the worksheet i.e. in any column from A: to BZ and beyond.
What I would like to have is a macro that finds the column ‘Project Key’ and moves it from it’s current position and moves it directly after a column call ‘XXX’ and delete the blank column where it orginally was. There would be no need to check if the columns exceed Excels maximum as my file never gets that big
I'm trying to write a macro to do the following:
1) Take cell A1 and copy adjacent cell B1 in new sheet.
2) Then search cell B1 to find a match in column A.
3) If there is a match, I want to copy that adjacent cell in B and into the new sheet.
4) If there is no match, then leave as is from before and move onto the cell.
5) Continue for the rest of the data.
Here is an example of what i am trying to achieve This is in the first sheet:
Column A Column B
Car Saw
Dew Jacket
Pen Key
Saw Screen
Hand Shoe
Jacket Window
Screen Sock
Now I want this in the next sheet:
Column A Column B Column C Column D
Car Saw Screen Sock
Dew Jacket Window
Pen Key
Saw Screen Sock
Hand Shoe
Jacket Window
Screen SockData 1.xlsx
I'm trying to write a macro to do the following.
1) Take cell A1 and look for a match in column A.
2) If there is a match, I want it to run the sub i've already written(Macro8), to update the data in its respective row.
3) If there is no match other than itself (cell A1), I want to automatically select the first empty cell in column A and run my sub (Macro8) to insert the data
How (or if) you can put multiple filters into one column? For example, I have a main cell with sub cells (that need to be filtered), and those sub cells have sub cells (that need to be filtered). All withing a single column. I have an example of what my data looks like in the attached.
View 4 Replies View RelatedWhy after the first loop (Z=2) all of a sudden column A in WS ATT disappears. Worth adding that column M in WS L is a "dynamic" range (at first it contains 7 cells) in the sense that it shrinks with each loop (M2:M8, second loop: M2:M7 etc.).
[Code] .....
I have been trying to transpose a piece of code that was put on this forum back in 2005 into my own Workbook but have been unsuccessful. The original piece of code filtered a column for unique entries and then filtered the main sheet for each unique entry and cut and paste each row that the unique value occurred in and in a new worksheet. A new worksheet would be created for each unique filter.
So what I am trying to do is look up a list of values that i have in a list on the worksheet. Then for each entry in my list, create a new page and cut and pasted the filtered main worksheet into that page.
Finally return to the main worksheet and then remove all filters.
I have attached the original file that was submitted so you can look at how it works and I have posted a sample my worksheet (with some things disabled to reduce file size but hopefully you will get the layout. However I don't currently have that sample on this computer. It is posted on another thread under the title "Selecting rows with specific values in a column".
I have set up a spreadsheet with a drop down box. Which says "Show All", "Show Pending" and "Show Completed" This is in Cell H7
Below that I have a table of information from A10 to I1200. These are a list of jobs
In column I is the date the job was completed, if it has a date the job is done, if it has no date its still pending. I wanted a macro that meant if you click on Show all, all jobs show, if you click on Show Completed on the jobs with a date in column I show and if you select Show pendning only the jobs without a date show
I did try this by recording a macro but failed miserably
I have a excel which contains 5 columns in which 5th column data cells has to be replaced with another set of data which have relationship with other 4 columns data.
View 3 Replies View RelatedI am attempting to do is have a macro (Via Command button) go through column A to find the word "TOTAL". Once that is found I want it to offset by 3 rows (from the word "TOTAL") to grab the number and place it into column B.
View 2 Replies View RelatedI am trying to write a macro that will do a bunch of stuff then go to the next blank cell in a particular column.
The rest of the code for the macro is irrelevant I just don't know how to code it to find the next blank cell in the column. It could be anywhere from cell A2 to A1000000. Basically I want the macro to select the cell that is next on the list to enter data into.
I have a file here included which has 8 + columns. I normally have to go through each column and find when the references start and delete everything below it. If i could have a macro find where the references start in each column and delete on down that would be a huge time saver.
#REF!
#REF!
#REF!
#REF!
Thats the example of the reference i run into, i was thinking of a macro to find where the REF starts and delete that cell and everything on down.
This may be a basic question but I haven't been able to find the solution:
I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example
0001 a
0001 b
0002 c
0002 d
0003 e
0003 f
I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example:
0001 a
0002 c
0003 f
I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
Column A
Row1 Orange-Apple-Pear
Row2 Orange-Grapes-Melon
Row3 Berry-Apple-Melon
Row4 Banana-Grapes-Melon
Row5 Orange-Grapes-Banana
Given the information above:
I want to find a macro that will auto filter on for example
"Banana" would filter to rows 4,5
"Orange" would filter to rows 1,2,5
"Apple" would filter to rows 1,3
I have created a macro to filter results from a set of data, paste that data to a new file, and then create charts based on that data. Right now, my macro is basically multiplied 20 times to do this for each heading. Is there a way to create a loop that will just redo the same macro but for the next data heading? Here is a sample of the first part of the macro:
View 14 Replies View RelatedSee attached doc "Find and Delete.xlsx". Inside, the value in F12 is a very important cell. Using the text value from F12, I first want to use visual basic code to find the range in column B because it will vary. This means the 'text words' column might have 50 words or it might have 2000. I have racked my brain trying to find a universal way to find a variable range.
Then I would like to search for the exact text value within this range in column
B. For example, in F12 is the text word 'windows xp registry cleaner' and you can see that this value is in B9. There will only be one exact match in column B.
I then would like the vb script to delete and remove this found value, then move left one space in column A to also delete the ID number associated with the text word. Then shift both rows up to fill the empty gap. I have tried numerous different codes but to be honest, I'm not really sure what I'm looking for. I've tinkered with find and replace, vlookup, match, find, search, and many more.
So I have the macro written, but I don't get the complete data that I am looking for. This is what I am trying to achieve
Here is an example of what I am trying to achieve. This is in the first sheet:
Column A Column B
Car Saw
Dew Jacket
Pen Key
Saw Screen
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
Now I want this in the next sheet:
Column A Column B Column C Column D
Car Saw Screen Sock
Car Saw Screen Mouse
Dew Jacket Window
Pen Key
Saw Screen Sock
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
In my current macro, It displays most of what I am looking for, but not a duplicate ID if there is one. For example Screen, there are 2 IDs. My macro is only getting the first one and then skipping over the second ID. I would like it to where if there is a double or if it is there more than once that it gets all of the data.
Here is my macro.
[Code] ....
I will also attach the document of what I am looking for : Data 1.xlsx
I am in the process of writing a VBA code(I should I am fairly new to this code writing!!!).What I need to be able to do is below.(enclosed please find a output in an xls file) What I should be able to do is below:
1.Find the text "Cash(No Listing)(Monthly)"
2. Offset to the 13th Column after that
3.Select the value in this column ,copy the value
4.Go to a row above and move one column to the left i.e.offset (-1,-1) and move 12 column backwards i.e.offset(0,-12)
5.Copy the value in 3 above to this entire range
6.Repeat this process for the entire file
My code is as below.Let me know where am I making a mistake. Just to let you know that this code performs the job well for the first entry i.e in the yellow makde area and doesn't do the job for any further entries .Llooks like I have some problem with the loop but not sure as to whats happening!
Short and simple. What is the quickest, easiest & most efficient way to find the first blank cell within a column using VBA?
How would I fix my formulas from not working when I filter other columns? It is a simple =A1/A3 cell and this calculation is down the full column. When I filter any column, the calculation does not stay with the assigned cells.
View 2 Replies View RelatedI need to create report (just summing up) based on values in two different columns. I need a VBA to do it as the column values may vary at any time.
I have attached a sample workbook for your kind reference.
