Copying Rows Based On Range Selection
Aug 27, 2006
I would like to copy selected rows in a range D19:I49 to another sheet, by clicking a button to bring up a User Form containing a List Box, seven Option Buttons (one for each sheet), an OK and Cancel Button. When the User Form is displayed, the required rows are selected in the List Box, then the required sheet is selected via an Option Button. Upon clicking the OK button it pastes (only the values) to the selected sheet, but to the next empty row (if there is already data entered previously) in the same range as above (D19:I49). All seven sheets are identical and are named Monday, Tuesday, etc. to Sunday. If the option button selected is the same as the active sheet a message box could be displayed to ask the user to select another option button and upon clicking the OK button on the message box return the user to the User Form once again so that the correct option button may be selected.
View 2 Replies
ADVERTISEMENT
Apr 30, 2014
I have a workbook consisting of 5 sheets.
Sheet 1 is the master sheet, and (among many other columns) it has a series of yes/no columns. The yes/no options are the equivalent of:
"Are you in sheet 2?"
"Are you in sheet 3?"
etc.
When this is the Yes option, I would like the rest of the contents of the row to copy into sheet 2.
These need to remain linked, so if I change stuff in Sheet 1 I would like it to change everywhere.
View 4 Replies
View Related
Jan 8, 2005
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View 9 Replies
View Related
Jan 8, 2005
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View 9 Replies
View Related
Dec 16, 2006
I know there are a lot of hide row threads... i've searched for ages... but (unfortunately) i can't a solution that addresses my problem and I'm stuck. Please help!
I have a data between rows 10 and 1310 and I would like to use a combo box option to select and view certain rows of data only. I don't want to use auto filter because i have a separate macro using an auto filter on the same data - they won't play together.
So in short - combo box will contain values from column A. When value is selected from combo box hide all non matching rows based on column A values.
So i have added a combo box to sheet1 and populated it with the following workbook code...
Sub Workbook_Open()
With Sheets("Sheet1").ComboBox1
.AddItem "Select Product Type"
.AddItem "Apples"
.AddItem "Mangoes"
.AddItem "Oranges"
.AddItem "Clothes pegs"........
View 9 Replies
View Related
May 21, 2014
I have a sheet with a lot of different data. In column M in workbook "Maximo report" there are 5 different values. I want to copy the rows that contain CDC_HVAC and that have the value "INPRG" in column G to the sheet HVAC_INPRG. I want to do this for all 5 values. Once the rows are in their respective sheets I want to filter them based on a formula and then sort from oldest to newest. I dont want the file to be too big and I think that VBA is the way to do it. I am new to VBA
View 6 Replies
View Related
Feb 1, 2013
I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...
Code that will allow you to unhide other worksheets in the same book????
View 1 Replies
View Related
Oct 14, 2008
Here is my
ThisWorkbook.sheets("sheet1").Range("M").Select
For Each cell In Selection
If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then
cell.EntireRow.Delete
End If
Next cell
Range("a1").Select
End Sub
I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
View 9 Replies
View Related
Jun 11, 2014
I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
View 1 Replies
View Related
Feb 22, 2008
Is there are macro that will allow me to: Clear Print Area, then Set Print Area based on user selection and finally print the Print Area to fit 1 page? I tried to search for solutions, but couldn't find any that matched my problem.
View 2 Replies
View Related
Jan 10, 2009
Here is my table which is just a test sample of the larger table, but in the end, it is column 1 I want to base the new ws on.
Before Macro
AY *AB1UnitCount2533210431 LGR SQ10584631 LGR SQ7726 Excel tables to the web >> Excel Jeanie HTML 4
The code below works fine through the first instance of the match and adds a new ws based on the name, but when it gets to the second match the macro tries to add the ws all over again and I get a run time error 1004 which states you can not add a ws and name it the same as one that already exist. I only have one sheet in my wb titled "AY". How can I also have the two column headers transfer to the new ws?
Public Sub CopyUnit()
Dim N As String
Dim i As Long
Dim ws As Worksheet
Set ws = Sheets("AY")
N = Worksheets("PAS Codes").Range("L14").Value
For i = Range("A65334").End(xlUp).Row To 1 Step -1
With ws
If Cells(i, 1).Value = N Then
.Rows(i).Copy
Sheets.Add.Name = N
Rows("1:1").Select
ActiveSheet.Paste
End If
End With
Next i
Application.CutCopyMode = False
End Sub
After Macro
31 LGR SQ *AB1UnitCount231 LGR SQ7331 LGR SQ10 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Aug 14, 2009
On sheet "Create Package" in cell "AA14" I have a value (lets say 2).
I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)
I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.
Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".
View 9 Replies
View Related
Feb 6, 2013
I have two pieces of code that perform virtually identical functions on different sets of data.Basically, the code deletes rows of data based on a selection in a listbox. One code works perfect, the other has an issue.
The Issue:
When I select a single row for deletion, or multiple contiguous rows, everything works fine. It is when multiple Non-Contiguous rows are selected that the problem occurs. I get a "Delete Method of Range Class" error.
The data for the code that doesn't work is in a defined TABLE, the date for the code that does work isn't. So I assume this is where the problem lies.
I converted the data for the code that did not work from a table to a range, and everything works fine. However, I would really prefer that this data remain a table. There is consistently going to be rows added and deleted, and a table will take those adjustments into account, whereas a named range shrinks each time I delete a row, and doesn't grow as I add rows.Here is the code that works:
VB:
'Delete Selected Offers
Else
For r = 0 To ListBox1.ListCount - 1 [code].....
View 6 Replies
View Related
Aug 16, 2007
I have a question that I am not able to answer.
Basically I have a worksheet with the following:
C4 -a cell where a user can input a number
the rest of the worksheet is data in a normal fashion.
I am seeking a macro that will select & copy cell G6 through whatever the value of C4 is. This selection would be a variable range with the offset being whatever is typed into C4.
Ex:
If the value of C4 is 5 then I would like G6:G11 to be selected and copied.
I am aware of offset, but don't know how to implement it in such a dynamic way.
View 9 Replies
View Related
Aug 19, 2013
I have an Excel file containing data about employees. Each row contains a lot of information that is relevant for a specific date range, denoted by the columns "start date" and "end date". I would like to copy each row so that I have it accessible per date that is within the date range. The only columns that would change in the copy are the date columns. For example, for 1 row that has a date range of 01/08/2013 - 05/08/2013, I want to copy the row 5 times except the date columns become "01/08/2013", "02/08/2013", etc.
View 2 Replies
View Related
Jul 27, 2012
I have eight sheets that have lists of questions in, which I want to copy across to a results sheet if the answer to a question is 'Yes'. Each question takes up rows B:H inclusive, and I would want to copy them to rows B:H in the results sheet. The "Yes" value will be found in column F of each row.
How can I set up a macro to copy the entire rows (without formatting) into a results sheet properly? I've tried every solution I can find but always hit a roadblock somewhere.
Ideally I would like to have a 'populate' button on the results sheet that would find every question that was answered 'yes' across the eight survey sheets and import them into results sheet.
View 2 Replies
View Related
Mar 10, 2014
Is it possible to be able to select a range of dates, and a time interval, and then to automatically fill in cells in a column? to be more specific, I want to be able to select, say 1 Jan 2013 to 31 dec 2013 in one box, then specify a time interval, for example "hourly" and then a column is populated with each interval, i.e. 1 jan 2013 00:00, and then the next one is 1 jan 2013 01:00 etc. I have tired the normal automatic fill tool but it never seems to get it right. It works for the first few days then it all goes funny.
View 3 Replies
View Related
May 16, 2014
The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 Replies
View Related
Mar 23, 2014
First sheet has a client name and several cells of data along the same line. All of the data on this sheet is kept long term.
The second sheet is completed copied and deleted. In completing the second sheet the client name is selected of a drop down list and pulled from sheet one.
What I want to do and not sure if there is a way is that when I select a client and populate it on sheet two I would like the related data on that clients line from sheet one to populate to same named lines on sheet two.
if this is possible and if so provide a brief description of how it would work
View 3 Replies
View Related
Jun 3, 2013
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies
View Related
Jan 30, 2007
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
View 9 Replies
View Related
Feb 22, 2008
I have a worksheet with some arbitrary selection - it may be 1 cell, a 5 by 4 rectangle, or even a noncontiguous splattering here and there on Sheet1. I want to select the identical cells on another sheet. So I'm thinking something like
dim sht1 as worksheet, sht2 as worksheet
'set them appropriately, then...
sht1.selection = sht2.selection
View 9 Replies
View Related
Mar 1, 2012
(Excel 2007). I have a report that I download which looks like this: The actual report has about 10000+ lines
I don't really want a macro for it but a formula that would copy the name in column A down in Column C but only to the point where the next name starts. So for example James Brown would be copied down in Column C till it reach Account Number 81914 and so on?
A B C
NameAccountJames Brown16836546259386014759716624957862380714557311623681914Steve Smith825168050422745213781459686708210378813700484308138459Holly Wood152663787051472924045442366165332455059500133525111211
View 5 Replies
View Related
Jul 20, 2007
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
View 6 Replies
View Related
Sep 21, 2009
I am using a form which has 3 text boxes and a list box. How do I copy the selection of a list box to the text box based on my active text box. So, if I was in text box1, and I click a selection in listbox1, it copies that selection to textbox 1, etc.
View 4 Replies
View Related
Feb 20, 2009
I am working on Excel document(Microsoft office-2007) with 2 sheets(Sheet1,Sheet2).
In sheet1 i have a dropdown list and the respective data is saved in Sheet2.On selection of dropdown lists in sheet1 ,respective rows should get copied from Sheet2 to Sheet1.
I am having trouble with the macro command. This is a first for me and I am stuck. I just dont know the how to write the Macro for this.
View 9 Replies
View Related
May 1, 2014
I have two lists mainly TV Brand & There Models.
List 1 (TV Brand)
Sony
LG
Samsung
Depended List 2 (Models)
Sony LG Samsung
EX420 55EB9600 PL43E450A1FXZP
EX430 77EC9800 PL43E490B4FXZP
EX550 55EA8800 PL43E400U1FXZP
EX520 KN55S9C UN32EH5300FXZP
EX645 55EA9800 PL64E8000GFXZP
I'm using two Listboxes (Form Control) with multiple selection options namely Listbox 1 (Brand) & Listbox 2 (Models). I want listbox 2 input range to be depended on selection made on Listbox 1 (Brand). For example, if user selects Sony then box2 should show only Sony's models and if user selects Sony & LG, box2 should show models for both Sony & LG.
View 3 Replies
View Related
Jul 14, 2006
which I have modified from one that I use before for sheets 1 & 3 (instead of 4&5)
But it isn't working! I have defined a dynamic range in sheet 4, which is the range I want to search (its named "search") should i be using this range in the formula somewhere?
Private Sub CommandButton1_Click()
Dim rngFind As Range
Dim strFirstAddress As String
With Sheet4.UsedRange
Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If ..................
View 9 Replies
View Related
Aug 13, 2014
I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't.
[Code] ....
View 6 Replies
View Related
Oct 2, 2006
I can't seem to make SumIF or Vlookup do what I want here.
I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column.
If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3)
Period Target Actual %Target
1 74 68 91.9%
2 81 71 87.7%
3 76 87 114.5%
4 76 68 89.5%
5 71 89 125.4%
6 69 81 117.4%
View 3 Replies
View Related
