Macro- Spreadsheet With 28 Columns
Apr 15, 2008I have a spreadsheet with 28 columns. I need some column headings to be amended when I run a macro and I also need some columns deleted.
View 9 Replies
ADVERTISEMENT
VB Macro - Sort Data In Columns Within Active Spreadsheet?
Feb 12, 2014I am new to VB Macro creation and I am creating VB Macro which will:
1. Sort data in Columns within an active spreadsheet
2. Create new Worksheets
3. Delete Values in rows based on value in Column.
I have listed my "Step by Step" instructions in the tblTest Excel file on attachment. The instructions are clear and straight to the point.
Below are some VB Macros I was experimenting with but it is not complete.
Sub Sort_Ascending_With_Header()
'Sorts a worksheet in ascending order and assumes there are headers on the data
Range("A1:DZ20000").Sort _
[Code].....
Create A Macro That Find And Calculate Based On Details Of 4 Columns On Same Spreadsheet
Jul 21, 2012create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Attached is the spreadsheet..
Getting 15 Columns In Spreadsheet To Sort On One Columns As It Alphabetizes
Jun 10, 2014Have a spreadsheet with 15 columns. In one of the columns is the name of the company and that column is not in alphabetical order. The city, state, zip code, business type and all the other pertinent data about that company is in the same row as the name of the company. My intent would be to put the company names in alphabetical order and keep all the company information in the same row as the company name.
View 1 Replies View RelatedMacro To Unhide Columns - Call A Macro Then Revert Columns To Previous State
Jul 17, 2014I have on sheet1 a number (72 at the moment) of Form CheckBoxes.
In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)
Then, go through and Check All checkboxes
Call MyMacro
Once MyMacro is complete (Filtering & Printing)
Revert the checkboxes to their original state.
The purpose of the checkboxes:
When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden
Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.
Updating Columns From One Spreadsheet To Another
May 9, 2014I have to update a master spreadsheet weekly. I would like to have a button that i can click to update the spreadsheet.
Here is sample data of my spreadsheets
SPREADSHEET 1
col1;col2;col3;col4;col5;col6
abcd;on time;dtjkl;05/06;CM,DR;ghgh
efgh;delay;hjhj;04/12;CD, EF;jgkg
ijkl;on hold;gfhj;02/15;CF,GD;klj
mnop;on time;njhj;04/15;CD,FG;kgkij
SPREADSHEET 2
col1;col2;col3;col4;col5
abcd;on time;05/06;CM,DR;ghgh
efgh;delay;04/12;CD, EF;jgkg
ijkl;on hold;02/15;CF,GD;klj
mnop;on time;04/15;CD,FG;kgkij[table="width: 500"]
I need to update the columns from spreadsheet 1 to spreadsheet 2.
Spreadsheet 2 has less columns. Also the data in col3 for spreadsheet 1 is not there in spreadsheet 2.
The mapping of the columns is as follows:
S1 S2
col1=col1
col2=col2
col3=col4
col4=col5
col5=col6
col6= data already in another column.
Totaling 2 Columns In Spreadsheet
Aug 21, 2013Any way to total 2 columns in a spreadsheet.
I need a formula that will sum the cells in the the first column below but if there's a value in the 2nd column I'll need to use that number instead. Is there a formula/combination of formulas that will accomplish this?
$91,170
$68,372
$89,015
$61,954
$767,379
$773,712
$247,266
$0
$94,370
$0
Total
$1,245,674
Sorting Spreadsheet By Priority Columns
Jun 14, 2014I'm managing a World Cup Prediction League and have been trying to create a spreadsheet that will eventually feed in to a league, positioning each player in descending order. However, I would like to use three columns to determine how to position the players.
EG.
Priority 1 - Column D - Total Points (Rank from Highest to Lowest)
(if there are players with equal Total Points in column D, then)
Priority 2 - Column F - Correct Scores (Rank from Highest to Lowest
(if there are still players with equal value after Total Points and Correct Scores, then)
Priority 3 - Column F - Incorrect Scores (Lowest to Highest)
I've tried for hours to work this out but can't. It would save me hours of work each day
I have attached the document : WC Prediction League EXAMPLE.xlsx
Locking Columns On A Questionnaire Spreadsheet
Sep 16, 2009I am trying to develop a questionnaire spreadsheet using a ranking system. I have different questions (based on certain criterias) and four columns for each question: NEVER | SOMETIMES | MOST OF THE TIME | ALWAYS.
I want to see if there is a way that I can lock the other three cells when the user has chosen a particular column by typing an X. BY doing so, I want to ensure that the user will not choose two options (or columns) instead of only one, because if he/she does it will mess the other calculations that are not shown within the table. Only the input and output are shown.
VBA Code To Hide Columns In Spreadsheet When Run?
Feb 9, 2012I need some VBA code to hide columns in spreadsheet when run. Basically, in row 7, I've entered HIDE in the columns that I want to hide when the macro runs (i.e. columns F,J,H,I,K, O,P,R).
View 7 Replies View RelatedAnalysis Using Sumif - Spreadsheet With Two Columns
Oct 31, 2012I have a spreadsheet with two columns, in column A i have text and in column B i have time format (01/01/1900 13:30:00) im trying to pull out information that if in column A says London to sum up the hours in column B is there analysis that would calculate this for me.
Area
Hours
Newcastle
37:30
Newcastle
37:30
[Code] ..........
Automatically Delete (or Hide) Columns In Spreadsheet?
May 15, 2014how to automatically delete (or hide) columns in a spreadsheet given a list of columns that I want to retain (or see).
These are the columns I want to retain (or see):
Name
1st Phone Number
2nd Phone
Country
Conditions
Email Address
Enrollment Status
Room not available
Roommate
Mailing address
Payment Record
Payment Status
Gender
Requested room type
Total Payments to Date
What is your meal preference?
The attached has two tabs......columns I want to retain (or see) and the raw data of all the columns.
Choose Columns To View In A Large Spreadsheet
Oct 3, 2008I have a large spreadsheet with over 50 columns. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.
Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.
Import Specific Columns Paste Certain Row Into Another Spreadsheet
Aug 10, 2013I can't figure out how to delete the attached files that are irrelevant and I can't figure out how to upload new ones. I also don't know how to word what I need to happen
I want the data from everything, starting from row 5 and going to row 100, and not include column B or E.
How do I grab just that data? I have different formulas in each document. This is for invoicing, so the columns with the $ numbers can't be changed and I haven't been able to figure out how to not overwrite them when importing another spreadsheet.
Copy Two Specific Columns From One Spreadsheet To Another In Same Workbook
May 29, 2014I'm looking to write a macro code that copies two specific columns to a separate spreadsheet in the same workbook, and then sort the rows by values in ascending order.
View 5 Replies View RelatedCopying Filtered Data Columns To New Spreadsheet
Oct 14, 2008I have a spreadsheet with two tabs (Customer Survey Data) and (Customer Rollup). The data starts on line 5 (headers in rows 1 thru 4) and is found in columns B thru J. In column B I have dates with no blanks. In column C I have customer names - no blanks. In column J is are the customer comments - not all customers made a comment so there are blanks.
I created a macro that autofilters the data to show only those entries within a specific date range (dictated by dates found in L1 and L2 - begin and end dates respectively) by column B (no problem with this). The macro also auto removes all rows where there are no comments (column J = no blanks). This leaves me with only those surveys recieved within a specied date range that have comments.
Here is my problem: the Customer Rollup sheet is the finalized report. I need to copy only the customer names and their comments into the finalized report. The following is the macro I put in which only half works and I have no idea why. It copies the names fine...but then only copies the first comment.
Oh yea...I don't want to copy the WHOLE column...only from the first visible row down to the last. Btw...I put a lot of notes for myself...I capitalized the notes where I think the problem is...Any suggestions on how to fix it or why it just doesnt seem to work like it should?
Select Columns To View In Large Spreadsheet
Oct 3, 2008I have a large spreadsheet with over 50 columns but usually only need to view a small selection of them. Many colleagues use the sheet and often use the format, hide, column function to hide columns they do not need but then the next user has to unhide them and re-hide others until they are left with the different columns they require.
Is there anyway of creating a menu which allows users to select from a list of all the columns those they wish to display, all the others therefore being hidden.
Dropdown List In Spreadsheet Which Combine Info From Two Columns
Oct 24, 2012I'm trying to have a drop down list in a spreadsheet which combines the info from two columns.
For example:
A B
1 John 29
2 Sandy 40, etc
And the drop down list turns into
John 29
Sandy 40
Is this possible?
Limit Number Of Rows / Columns In Spreadsheet (beyond Just Hiding Them)?
Nov 27, 2013I would love to be able to limit the number of rows in a specific sheet so that I can quickly carry formulas to the bottom of a worksheet without carrying them to row 1,048,576. I'm aware of options to hide rows and/or to limit the scrolling with the "view code" option on the worksheet tab; however, I'm mainly worried about worksheet performance. When I carry a formula down, I don't want to see a spinning blue circle run for minutes at a time, and I'm hoping to avoid the same spinning circle when I undo my formula. Is there any way to actually limit the number of rows, or is there any other limitation I could put in that would increase worksheet performance?
View 1 Replies View RelatedLarge Order Spreadsheet With 3 Date Related Columns
May 15, 2007I have a large order spreadsheet with 3 date related columns. One for customer requested delivery date, one for factory quoted delivery date and one for actual delivery date.
I want to be able to set something up that if it gets near either the requested or quoted delivery date (by say a week), that the cell flags red so it's a visual reminder to check whether the order has been dispatched etc.
Loop That Grabs The Cell And Prints Into Columns On The Spreadsheet
Dec 20, 2009how to create a loop that grabs the ( t'th ) cell and prints into columns on the spreadsheet adjacently that cell and the ones before it (total = t) and repeats it consecutively until there are no more cells to transcribe into columns. For example - I have n=100 cells in a column that need to be placed into n-t columns (where t = 5 for example).
See image below for clarification, code to follow:
As you can see I need it to fill out in the yellow highlighted cells the rest of that pattern. Right now it only does it for one column, but every time I try to get it to do the rest of the columns, it prints the same first column over and over again (I'm having some severe mental block with implementing the correct loop code).
For clarification also, the user selects the t'th cell and it prints that cell and the previous (3 in this case)... and then it shifts down one cell and prints those 4 cells into the next column - which is what I want it to do.
Sub populate_range()
Application.ScreenUpdating = False
Dim rowoffset As Integer
Dim offsetnumber As Integer
rowoffset = Range("F2")
offsetnumber = Range("F2")
Range("d5:iv5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheet1.Select
Range("b4").Select
ActiveCell.Offset([offsetnumber], 0).Select
Range(ActiveCell, ActiveCell.Offset(-([offsetnumber] - 1), 0)).Select
Selection.Copy............................
Spreadsheet For A Couple Hundred Rows Of Data And 6 Columns
Jan 4, 2010I have a spreadsheet for a couple hundred rows of data, and 6 columns. The fifth column contains a date. What I need to figure out is this:for every 3 rows of data, if the date diffes in column 5 (E), highlight this row and the previous 2 rows
Currently, I've been doing this all manually, row by row - needless to say, it takes me a few hours or depending on how much other work I need to do, a few days.
Macro Fixes Macro: Copies Tabs From The Spreadsheet
Jun 26, 2007I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.
View 2 Replies View RelatedListview In 2007 (populate With A Range Of Several Columns With Data From A Spreadsheet)
Sep 1, 2009My question, and it's my first one here, is regarding the use of ListViews in Excel forms. I need to populate it with a range of several columns with data from a spreadsheet.
View 4 Replies View RelatedCount The # Of Rows In A Spreadsheet In Which There Is Non-blank Text Data In 2 Separate Columns
Dec 5, 2008I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT
Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else
Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet
Dec 27, 2011I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.
If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.
Running Query To Access: Pulls Data From 2 Columns In A Table And Pastes It To The Spreadsheet
Jul 10, 2009I have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
View 2 Replies View RelatedMacro Not Working On Other Spreadsheet
Feb 7, 2014The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.
Is this due to a security setting or missing code line items?
copy&paste in next blank cell2.xlsm
Macro To Unprotect A Spreadsheet?
Jun 16, 2014We have a macro that opens a spreadsheet and pastes data from another sheet into it.
Is there a macro statement that will unprotect the spreadsheet if it is saved with protection (it is not password protected)?
Reset A Spreadsheet With A Macro
Sep 23, 2008As mentioned I want to create a button that can reset this/a spreadsheet.
DWOR_2.zip
I have locked all the cells and unlocked the cells with the light green background. I.e. where a user can input data.
I haven't added a password yet nor protected the sheet. (the macro needs to work when the sheet is protected.)
I am looking for a macro that can be used to reset the sheet to its original state. However, the sheet needs to be protected.