Macro To Act Differently On Selected Multiple Columns?

Jan 18, 2014

I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.

I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...

So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..

I made my own small code but dont know how to make it act differently on selected columns....

I would select the entire matrix and then want the macro to run differently, taking into consideration the column names

[Code].....

I also need to add a vlookup formula for some column based on the column B's value...

Ex: This is for Col C

[Code] .....

So how do I write the code in such a way that the code acts on its own differently.

View 14 Replies


ADVERTISEMENT

VBA Code To Copy Selected Multiple Columns To Multiple Rows In Excel

Mar 13, 2014

I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.

My original data are in the form of the following:

invoice_no
product
sales
qty
total

[Code] .....

I want to display the data in another sheet in the following format:

sales_a
sales_b
sales_c
sales_d

[Code] ...........

View 2 Replies View Related

Adding Sort Filters To Multiple Selected Columns

Nov 14, 2009

I have a row of sub-headings at row 12 that require Sort Filters. My problem is that I need the filters on selected columns only. It seems that with the standard filter button I get all or nothing.

View 8 Replies View Related

Macro To Copy Selected Columns

Aug 24, 2009

OK, I want to run a macro that will:

(1) go in to sheets 'A', 'B' and 'C'
(2) find all cells in row 1 that equal "1"
(3) copy the entire column and paste as values

View 13 Replies View Related

Worksheet Change Macro Runs Differently

Dec 3, 2008

When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?

Original
Sub sessions()
Dim a As Integer
Dim x As Long
x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row

Sheets(1).Unprotect Password:="password"

For a = 2 To x

If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = True
ElseIf Cells(a, "m") Cells(a, "d") Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = False

End If

Next a

View 9 Replies View Related

Macro To Copy Selected Columns And Paste In New Sheet

Aug 24, 2012

I have a sheet that has columns from A to BS, and the column headers start in Row two.

I need to 1st filter the data by Column E where the data in Column E should not contain a particular Value, like"Sleeve".

Then it needs to copy the Columns A,D,G, BN, BO, BP,BP,BR & BS.

And it needs to delete Rows 3 & 4 from the current sheet, before pasting it in a new sheet.

And at the bottom of the sheet it needs to give me a count of the rows and the month end date for each month.

View 1 Replies View Related

Text File Import With Text To Columns Splitting Same Text Differently

Nov 21, 2007

I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))

View 6 Replies View Related

Macro To Multiple Two Columns Then Sum?

Aug 10, 2009

I have a work book with 97 sheets, all of the sheets are identical in format but contain different data. I need to multiple column F (which is a price) by column G (which is a quantity). I then need to sum the results and spit this number out on a new sheet. So in then end I would have 97 entries on the new sheet with this new sum which would give me are total stock value on hand.

View 4 Replies View Related

Macro - Copy Multiple Columns Down To End Of The Data?

Feb 18, 2014

I am trying create a macro that will create a new work sheet on an existing workbook. I have minimum experience writing a macro, but I do know how to effectively record one. The issue is, I need the macro to copy multiple columns down to the end of the data and pasting it into the new workbook at the end of the data already pasted-- rows of data are frequently added to the workbook.

View 4 Replies View Related

Delete Multiple Columns In Different Range Using Macro

Aug 6, 2012

Code:
Range("A1,A:A,C:C,E:E,D:D,G:G,H:H,I:I,J:J").Select
Selection.Delete Shift:=xlToLeft

When I wrte a macro as above, i get error "the command cannot be used with selections that contain rows or columns and also other cells. Try selecting only entire rows, columns or just group of cells".

Actually i want delete all columns except B:B and F:F

View 2 Replies View Related

Macro To Group / Ungroup Multiple Columns?

Feb 21, 2014

What I need to do is group and collapse certain columns when Cell D4 in my Date tab reads Bud. If it doesn't read Bud, I need it to ungroup those columns and group and collapse other columns.

So for example: If I have 6 columns (A - F), when Date tab cell D4 reads 'Bud'. I want to group columns A and B, and only show C-F. If the cell doesn't read Bud, I want columns A and B to be ungrouped, and Columns E and F to be grouped and collapsed.

I've been racking my brain, using the Record Macro and just cannot get it correct. Granted I have created something similar to HIDE my columns, however I need it to be grouped so that higher management can ungroup to see some details if need be.

Code:
Sub GroupColumn()
Dim s As Worksheet
For Each s In ActiveWorkbook.Sheets(Array("Name1", "Name2, "Name3", "Name4"))
If Worksheets("Date").Cells(1, 4).Value = "Bud" Then

[Code].....

View 5 Replies View Related

VBA - Displaying Values In Multiple Lines If Multiple Checkbox's Are Selected

Sep 10, 2012

In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?

Current code

VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption

Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'

Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing

View 1 Replies View Related

Track Changes On Multiple Selected Ranges On Multiple Sheets

Jul 6, 2007

I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.

is there a way to track changes on multiple selected ranges on multiple sheets

View 9 Replies View Related

Transpose Multiple Columns To Rows Based On Match Via Macro Or Formula

Jul 10, 2008

I have two columns of data as follows:


10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......

View 9 Replies View Related

Calculate Differently Based On Value

Nov 26, 2008

I'm selling a product to clients. The first one is $20, and every subsequent one is $10. If they don't order any, I need the cell to read $0. I've been trying to get the IF function to work, and can't quite make it work correctly.

View 3 Replies View Related

If Negative Number Display It Differently

Jun 8, 2009

I currently have the following in a cell:
=BDH(AHD!A3,"px_last","-5cm","-5cm")

This currently displays as -32 pulling this number out of bloomberg.

I now want to write a formulae in this cell that will display this as 32c if it is negative... and 32b if it is positive.

So if the number pulled out is negative I want it to have a c after it (and please have it delete the minus sign) and if positive, then have a b after it.

View 9 Replies View Related

Import Selected Columns?

Apr 21, 2014

I want to import one worksheet from different workbooks to Master workbook. In the source workbook, I need to import only ' Code' sheet and in this code sheet , I need only 4 columns(A, E, F, and I ) to import. I also need cell C3and E3 in source worksheet to master workbook

I want to import like 'Code(1), Code(2), Code(3),, and so on. I don't want to import whole thing in the one sheet in the master workbook.

IN the master workbook, the header is on A3, so starting row will be row 4 in master workbook.

Codes below is for 'stacked' style, I guess.

[Code] .....

View 8 Replies View Related

Delete All Columns Besides Two Selected

Aug 12, 2014

I'm prompting the user for what two ranges they want to keep in a excel sheet and then I want to delete the rest of the columns. There may be 5 total columns and there may be 30, it will vary. The reason I want to do this is because I will then save data to CSV file and it can only have two columns of data to be passed on for other data processing.

View 5 Replies View Related

Sum Selected Columns For Single Row

Aug 13, 2013

I'm trying to sum selected columns of a specific row in a spreadsheet. The row name is to be matched to the same name in a different sheet; the column headings are also found on that separate sheet. I've tried SUM using INDEX and MATCH, but it's not working (most recently I get #N/A). I'm trying to do this sum for each separate row, first through sixth, with varying columns for each row.

I'm not opposed to getting into VBA, but wanted to see if there was something that I missed with the regular formulas.

My most recent iteration is:
=SUM(INDEX('Current'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($B6,'Current'!$A$1:$A$16,FALSE)):INDEX('Curre nt'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($C$2,'Current'!$A$1:$A$16,FALSE)))

[Code] .........

View 2 Replies View Related

Printing Selected Columns

Nov 6, 2013

I have a data sheet which I need to print everyday, I need to print Column A plus other individual columns on separate pages. For eg. Column A + B, Column A + C, Column A + D etc until the last column. Besides hiding and unhiding, is there any way to do it via vba? For eg, pop up to ask user which column to print?

View 6 Replies View Related

Weighted Averages Across 2 Differently Sorted Ranges

Aug 16, 2014

I am looking for a way to get a weighted average to work without the need of helper columns, when my weights are stored in one range, and the actual values are stored in another range, but might not be sorted in the same fashion.

For example; range 1 might be:

Weight
Homework 120%
Pre-Exam 30%
Exam 50%

But the range for student's scores might be in a slightly different order:

Scores
Exam 90
Pre-Exam 72
Homework 1 81

(Note that this is a greatly simplified example and that just resorting would be impractical).

View 5 Replies View Related

Merging Data From Differently-shaped Streams

Nov 19, 2012

I have two sources of data (of electricity use) that I need to merge to plot detailed consumption. (Source B below needs to have calcs done on it and then be subtracted from Source A. Trust me, it just does). But their formats are transposed relative to one another, and one takes readings every half hour and the other once a minute. Plus, I'll need to append to the data periodically as more gets generated.

I'd describe my Excel level as "novice plus", i.e. IT- and database-literate but as far as Excel's concerned still trying to fully master pivot tables...

Source A looks like this:

DateMeasured in00:0000:3001:0001:3002:00
28-Oct-2012KWH1091087
29-Oct-2012KWH77878
30-Oct-2012KWH108877
31-Oct-2012KWH99877

Source B looks like this:

Time<More columns> kW_Raw_Data

28/10/2012 00:04<More columns>194.541667
28/10/2012 00:05<More columns>180.32
28/10/2012 00:06<More columns>156.17
28/10/2012 00:07<More columns>157.243333

View 5 Replies View Related

Pulling Data From Differently Written Names

Nov 4, 2013

I have a spreadsheet with numerous transactions that contain the same name but are often spelled differently or inconsistently. I would like to pull all the transactions with the similar name and in a new sheet, make all the inconsistent ones, the same e.g. (space)mr smith or mr smiths or mr smith's or mr smith would all be mr smith. I am thinking of using the match command as a possibility but want it to repeat each row.

I am attaching a spreadsheet to show the existing table and the desired result which is similar to what I am showing below.

Example:

Existing Names and data
A B C D E
1mr smiths xyz10020%
2 mr smith abc12430%
3mr smith's def20040%
4mr smith xyz7550%
5simon inc zbc14065%
6simon's inc xyz20070%
7simon abc9525%
8shows zbd18045%

Want it to show Names and data:

A B C D E
1mr smithxyz10020%
2mr smithabc12430%
3mr smithdef20040%
4mr smithxyz7550%
5simon inczbc14065%
6simon incxyz20070%
7simon incabc9525%
8showszbd18045%

View 2 Replies View Related

Rearrange Selected Columns Automatically

Jun 21, 2013

Daily I am getting a file which have so many columns with different names. I used to select and move my required columns to separate place. For exp.

I have the file with various columns, such are.

Sales Qty, Year, Tax, Company Code, Discount Paid, Sales Amount, Company Name

The above column order are not constant, daily the order will changes. But we have to make the order as below

Company Name, Company Code, Year, Sales Qty, Sales Amount, Tax, Discount Paid

Like this we have so many columns in the file and we used to rearrange as required.

Any formula or any macro to avoid this manual work with error, so that i will save my time and free from error.

View 2 Replies View Related

Hide/Display Selected Columns

Jul 13, 2009

My boss wants me to create a macro to hide columns if there is a "X" on top of the column labels. The macro should be in toggle mode whereby the next pressing of the macro will unhide the hidden column.

There are more than 50 columns in my actual worksheet. The macro has to check column by column whether there is "X" marked on top and hide it if so.

The next pressing of the macro should then unhide all the hidden columns.

View 12 Replies View Related

Print Selected Columns And Rows

Apr 27, 2013

I have a worksheet which contain a lot of data in it. I want only certain rows and columns to be printed when I click a button. Actually I am trying to print a report.

Suppose I have data across A10:M100. Lets say from columns A to D are permanent, if the cell colour of any cell from E to M is not Red then that is to be printed along with the contents in A to D. Since I have bunch of data in that sheet almost 10000 rows I separated data by giving them name. If it is possible to have an option to choose from before printing that would be much much better. Because even though there are many cells left uncoloured I don't want them to be in the print as they are belong to another table (I have given name for each table)

If all columns from E to M in row 20 are Red then that row should not appear in the print. If J20 is left uncoloured then it has to be printed along with the data from A20 to D20 ( which I said permanent). This colour I fill by a macro as when required only for the range E to M.

View 5 Replies View Related

Insert Two Columns For Selected Rows

Dec 6, 2008

I would like to select a number of columns and then run a macro that would insert two columns after each of the selected columns.

Or is there perhaps a faster way than using a macro?

View 9 Replies View Related

Excel 2010 :: Sorting Different Sections Of The Same Worksheet Differently?

Aug 14, 2013

I am using Excel 2010 64-bit (with SP2 and all other updates installed).

I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)

I need to sort this worksheet as follows:

1. By background color, so yellow is on top, white is on bottom.
2. Then, within the yellow rows, data in column J.
3. Then, within the white rows, data in column A.

Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.

Is there a way to combine these steps, perhaps with a macro?

I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.

View 2 Replies View Related

Excel 2003 :: Rank With 4 Criteria That Are Ranked Differently?

Nov 20, 2012

How can I rank with 4 criteria that are ranked differently? (Greater/ lesser etc)

Ranking - Online Spreadsheets - EditGrid

I had a look into this, but I got a bit confused with how isnumber, big, small and rank actually work

Using Excel 2003.

View 2 Replies View Related

How To Change Filepath Or Dir When Users Have Drive Mapped Differently

Oct 23, 2013

I have written code that allows the user to open a file. To make things easier I changed the file path to point directly to the folder where all the needed files will be placed. The problem is everyone that will be using this work book map their drives differently. One user may have it as H, and the other as J. Also users can have the same drive mapped but at two different starting points. Ex: H:DataReportsxxxyyy or H:Reportsxxxxyyy this causes the code to crash. Is there a way do default the path so it will not matter how the drives are mapped?

Here is what I am using so far:

Dim filt As Variant ' flit to be used in the getopenfile. variant is need for its multiple
Dim FilterIndex As Integer
Dim Title As String

[Code]....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved