Free Microsoft Excel 2013 Quick Reference

Move rows to another worksheet based on a cell value

Hi. I have a worksheet called "ALL DATA" that contains a list of methods that are marked by a validated column that can only contain "Lift, Evacuate, Assist or Wheels."

Is there a way to move the rows with a value of "Evacuate" to another worksheet called "TRAINING REQUIRED"? Also when this move is done, that row is still required in ALL DATA and should remain. So I guess its like copying a list of names who are evacuators to another worksheet in preparation for training the named people.

It would be brilliant if i could also prevent names from being duplicated in the TRAINING REQUIRED worksheet.

Thanks for your help in advance


Hello,

I currently have a consolidated worksheet (thanks Bill!) called "Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up?

Edit: here are some additional information:

The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)

The "status" column is at column 11.

Thanks for your help in advance,
Henry

Hi Excel experts,
I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.

Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.

Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.

Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.

Can anyone assist?

Thanks!

Dear All,

I am a new user to this forum, but have read many great solutions and I am pretty sure there are people in this forum that can help me.

I have a excel file to keep track of tasks or actions that need to be performed. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows (so an entire task) of completed tasks to another sheet called, "Completed Actions". In Actions sheet I have a column for " status" and here you have to select from a drop down menu, either "On-going", "Urgent" or "Done". What I would like, is that once you have selected "Done", the entire row or entry, will be automatically moved to the "Completed Actions" sheet.

Look forward to reading some answers and/or comments.

Carsten

hi all

Just started to have a look at vba as I am trying to use excel more efficiently, the problem I have is this, my spreadshet consists of rows like the following

DATE NAME FACULTY REASON LESSONS
8/7 MR JONES ENGLISH ILLNESS 6
8/7 MRS MEL MATHS LOA 5
9/7 MR TOM SCIENCE MEETING 3

I want to copy the rows to other worksheets dependent on column C (faculty), so someone from english would go onto a worksheet for english,a row with maths would go into a worksheet called maths etc.

thank you in advance of any advice

I would like to write a macro to loop through each row of a worksheet titled
'Data' and copy each row into seperate worksheets, based on the value in
column A.

Col A Col B Col C etc
Test1 ... ......
Test2 .... ......
Test3 ...... ......
Test2 ...... .......

Row 1 would copy into worksheet 'Test1'
Row 2 would copy into worksheet 'Test2'
Row 3 would copy into worksheet 'Test3'
Row 4 would copy into worksheet 'Test1'

The worksheets to copy into are known and are already set up.

Any help on this problem would be very gratefully received.

Thanks

I have a spreadsheet that receives constant updates and I need a formula to copy a complete row to another spreadsheet based on a single condition:
A B C D E F G
TEAM
136 2/24/2010 Carlos Miro XOXO 138 Paid online
119 Lisa Lamora XOXO1 128 599
103 Brittany Lumpkin XOXO3 128 188
136 Carlos Miro XOXO1 1289
136 Amaury Alvarez XOXO111
103 Carlos Miro XOXO112
103 Candace Sowdon XOXOXO2 Paid online
119 Mauricio Gomez XOXOXO 1209 Paid online

(Please see attachment for details)

Based on the input of this table, I want to be able to extract the rows that belong to team 103 (for example) and copy them into another table (for that team alone).

I don't want to use Advance filter, but would like to use a formula. I've tried vlookup, but it would these arenot unique rows, I will only get the first line.

How do I automatically hide columns in a worksheet based on a cell
value?

I intend to set up an automated hide for a column (D) in sheet1 based
on values of some cell in worksheet 2 (say cell B2).

please provide any suggestions.

Thank you in advance.

Regards,

Deepak

How do I automatically hide columns in a worksheet based on a cell value?

I intend to set up an automated hide for a column (D) in sheet1 based on values of some cell in worksheet 2 (say cell B2).

Any suggestions?

Thanks you in advance.

Regards,

Deepak

Hi everyone,

I m trying to build a macro in order to filter pivots based on a cell value. I have found the following coding on this forum but I don't understand why it triggers an error "1004 - Application-defined or object-defined error".

The cell value I m using for filter is a date, i.e. "06.09.2011".

I have noticed that If I do the same exercice but with text instead of date it works perfectly. Should be kind of format problem.

The code is the following (the underlined code is the one highlighted by the debugger) :

Sub VALUEFILTER()

Dim PT As PivotTable
Set PT = Sheets("Sheet2").PivotTables("PivotTable1")
PT.PivotFields("Value date").CurrentPage = Sheets("Sheet1").Range("H3").Value
Set PT = Nothing
Set PT = Sheets("Sheet3").PivotTables("PivotTable1")
PT.PivotFields("Value date").CurrentPage = Sheets("Sheet1").Range("H3").Value
Set PT = Nothing

End Sub
I precise that I m using Excel 2007.

Thanks in advance,
Scarface

I have a wkbook with 3 wksheets (Tracking, Closed, Hist)

When an item is marked "closed" I would like the row deleted from Tracking sheet and pasted to Closed sheet. Note - "closed" is selected from a drop down menu.

now my "and then some"

When I enter an item into Tracking sheet for the first time I would like it to automatically duplicate on the Hist sheet. In addition, my Tracking sheet shows me the current location and date and I would like Hist to keep a history of these dates and locations, i.e. each time I change "joe's" location and date in Tracking, I would like "joe's" location and date on the Hist sheet to keep adding to the first available cells on his row.

A million thanks for any help you can provide.

BJ

Any body from excel experts,plese help me how to add a picture based on a
cell value.I have excel sheet has persons personal data,I would relates that
data with their photo.How to create picture table,add pictures,looking up and
displaying pictures based on a cell value.

I'm trying to update links based on a cell value.
For Example: A1 thru A4 has the values 21,22,23, and 24 respectivly. My
link in B2 is ='C:temp[Cash Report 12-22-05.xls]Sheet1'!$D$5.

Is there a way that I can have the link in B2, change to 12-23-05(A2's
Value) without doing a manual find and replace for each cell? If there is a
macro way to do this that would be great too.

Thanks in Adavance!

Vick

Ok I need some help with getting a marco functioning on a spreadsheet that my office uses to input patient EOB data for record keeping. In the past data was entered manually but eventually the amount of data becomes overwhelming to manage.

Basically what I need is for a marco to run when you click the “Run Me To Sort” button on the Temp worksheet. At this time we need rows to be moved from the Temp worksheet to other worksheets based on what month was entered. The other works sheets are named by month.

So for example a row with

Ins1 123 PC 4/2/2012 21.89

Would be moved to the April worksheet because of the date 4/2/2012. New spreadsheets will be created for every year so error checking on the year is unnecessary.

Dates are always in the 4th column.

The marco needs to only run when the button is clicked. That way data can be manually reviewed on the Temp worksheet first.

Once the row has been moved to the appropriate worksheet the line can be removed from the Temp worksheet.

I have included an example spreadsheet setup just like the one we are going to use.

Thank you ahead of time for helping me with this, it will allow other employees other then myself to keep up with the data.

I have a workbook which contains an input worksheet and four output worksheets. There is a macro button on the input worksheet which, when pressed, exports the contents of the four output worksheets to PDF. There is also a cell on the input worksheet which contains a Boolean True/False value.

I am trying to customize which output worksheets will be exported by the macro depending on the value of the True/False cell.

I already know the code to select a sheet and export it as a PDF into the desired directory. I am missing the code to determine WHICH sheets to export based on the cell value. The cell containing the value has a named range of CheckboxFMIStorageRequired.

I don't know the syntax, but the code would sound something like this:


	VB:
	
, Export Worksheet 1 & Worksheet 2 & Worksheet 3 
If Cell.CheckboxFMIStorageRequired = False, Export Worksheet 4 & 5 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance, y'all

I have a worksheet, and I want to select rows if they contain specific data in various cells and copy them to another worksheet(or maybe filter them in-place). It is a rather complex selection. I have looked at autofiltering and although I do use it, I don't think it will work this time.
Here is the selection criteria: If A:A contains "Project" Or IF A:A contains "Scope" Or IF J:J contains "1" Or IF F:F contains "X" and J:J is not Blank.
I have searched and cannot find anything that will work. I feel pretty confident that it will require vba to accomplish this. Any assistance would be greatly appreciated.

I have a "Temp Sheet" with a range in Column A:

ColumnA
Invoice #
S9700441

it will always be at least one but could be well over 100.
I have a code that creates a new tab for each invoice #.

But now I need a code to delete them.

Is there a way to delete worksheets based on a range?

How do you copy an entire row in excel to a new tab based on a cell value. Then delete the cell in the first sheet

I am using spreadsheets to track a portfolio of investments. I input all the
data on one worksheet which is linked to two other worksheets. On one of the
other worksheets, I would like to organize the rows in an order determined by
the value of one cell. Specifically, I list the asset catagory of each
investment (eg. Large cap stocks, small cap stocks, high grade bonds,etc). I
use 20 different asset categories. Wheb I list all the assets on the first
worksheet, they are in random order, but I would like to list them in a
specific order based on the asset class. Is there a way to organize the
entire row in the order based on one cell's value?

Thank you!

Hi,

I have over 2,000 rows of data, each with 4 columns. The 4th column is a number, different for each row, which represents the number of instances for that data item.

What I would like to do is copy each row to sheet2 'x' times, where 'x' is the value for that particular row contained in the 4th column.

So on sheet2 I would end up with a fully expanded set of data (and would no longer have a need for the 4th column).

I hope that makes sense.

I found several solutions for copying rows if a cell matches certain criteria but not to copy a row 'x' times based on a cell value.

thanks
Burd

Hi, I had this as a private message and thought it best to post it here.

AL

Zeb Sanders's Avatar
Zeb Sanders Zeb Sanders is offline
Junior Member

MS Office Version: 2003
Op System: Windows XP
Assumed Experience: Just above poor (Basic Formulas SUM etc)
Join Date: 30th August 2006
Posts: 5
Smile Hide a row before printing based on a cell value.
As I am still learning VB I have been unsuccessful. I have attached the code that I am using, and was hoping if anyone would give me ideas on what I should do, or what I am doing wrong, I would appreciate it.

How do I modify this piece of code to hide Rows ("31:37") when the value in B30 is grater then one? If the value in B30 is less then one I want to print Rows ("31:37") with the rest of the work.


	VB:
	
 ' If ActiveSheet.Name = "Sheet1" Then
 ' Cancel = True
 ' Application.EnableEvents = False
 ' Application. ScreenUpdating = False
 ' With ActiveSheet
 ' .Rows("31:37").EntireRow.Hidden = True
 ' .PrintOut
 ' .Rows("31:37").EntireRow.Hidden = False
 ' End With
 ' Application.EnableEvents = True
 ' Application.ScreenUpdating = True
 ' End If
 'End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you,

Hello everyone.

I would really appreciate if someone would be kind enough to help me out. I have a spreadsheet that has columns of info. I would like to copy columns into other sheets, based on the a cell value. For example if A1 = Glasgow, copy Column A into sheet Glasgow. If B1 = London, copy Column B into sheet London.

Any assistance will be brilliant.

Thank you.

I'm trying to create an input file for a mail merge that will create multiple
copies of rows based on a cell value. For example my sheet1 has:
name address qty
henry 123 anystreet 5

I need a sheet2 that contains 5, (the qty), rows of the name and address.
Each record may have a different qty.

Hope I have explained this well enough.

TIA,
Henry

I have a spreadsheet that I am trying to copy a range within a row to another sheet based on its value

I dump data into the spreadsheet from A to L. I assign the values below based on a lookup table in M.
Values
A+, A, B+, B, C, D, F, U

Now I want to copy the range A2:L2 to a different sheet based on its grade, i.e A+ goes to the A+ sheet, A goes to the A sheet and so on. There are approxamatley 4000+ rows in this sheet.

I have attached a small portion of the records.

Any help would be appreaciated, thanks in advance

Hello,

I am creating a financial worksheet for a client in which the user can
choose options from a drop down menu. What I want to happen is, based
on the option the user chooses, the model sends them to a certain cell
on the same sheet.

Ex:
If user chooses 1 from the drop down, it sends them to cell A4. If the
user chooses 2 from the drop down, it sends them to cell A5.

Also, I would like to know how to trigger a macro based on a cell value

Here is the code I thought might work
=if(A1=1, Run_OLM(),"")

Any advice on these topics is much appreciated. Thanks in advance.

Tushar