Clear & Copy Ranges Based On ListBox Choices

Aug 21, 2007

I have a saved file at d: named Report.xls from another file I am copying column and pasting data to this report file, this is done by a macro. While pasting data to the report file, a pop up message is coming up A file named D:
eport.xls already exists in this location? Do you want to replace it?

In case yes is clicked it pastes the data But the problem is that the macro that I have in the report.xls vanishes. The macro I need in the report.xls?

View 6 Replies


Copy Paste From Sheet Based On ListBox Choices

Jan 14, 2008

On sheet1 I have four listbox and when user select items in listbox then macro copy some range from sheet vj and paste them on Sheet1.

But I have to problems with this macro:

1. I want to copy range instead of row

2. How to change macro if I have two or more listbox.

Private Sub CommandButton1_Click()
Dim mpRow As Long
Dim i As Long

View 9 Replies View Related

Allow Multiple ListBox Choices

Jan 24, 2008

There is any way to select two item one from listbox1 and anotherr from listbox2 and paste on sheet

View 9 Replies View Related

Hide Unwanted Columns & Clear Ranges Based On Cell Value

Sep 17, 2008

I have written a VBA procedure which is supposed to hide unneeded columns (based on the a number entered in a certain cell. This is working so far. Since I need to sum only the visible cells in a row i need to clear the contents of a range of cells if they will be hidden. Unfortunately the code i have written runs only to the ClearContents and then starts over from the beginning. If i take out the .clearcontents and put .Select for example instead, the code runs perfectly.
Here is the

Sub worksheet_calculate() 'Hide columns that are not needed

Sheet1. Unprotect

Application. ScreenUpdating = False

Dim i As Integer
Dim r As Integer
Dim s As Integer
Dim rngRange As Range

i = 2

View 8 Replies View Related

Send All MultiSelect Listbox Choices To Sheet

Feb 19, 2007

i have this code to populate the worksheet from listbox selections, but it only works on a 'one at a time' basis.

Private sub cb1_click()
Dim i As Integer
Dim Ltm As String

With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
If Ltm = "" Then
Ltm = .List(i)
Ltm = Ltm & ", " & .List(i)
End If
End If
Next i
End With

With ActiveSheet
.Range("B" & .Rows.Count).End(xlUp). _
Offset(1, 0).Value = Ltm
End With
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False

what i am trying to do is once i have checked several items in the listbox is have all of the checked choices fill iin column B at once.

View 9 Replies View Related

Fill Cell With MultiSelect ListBox Choices

Nov 20, 2006

I refer to the following article from last year, with a similar question.
Multiple Selections Drop down list to one cell.

I would like to be able to choose multiple values from a list box and print them into one cell (or at least print them anywhere).

For example: I have a list with products A, B, C, D, E, F
Those products are shown in the list box. If e.g. B, D and E are chosen, I would like to have this selection shown together in another cell. That means the content of the cell should be "B, D, E".

If I use the INDEX function (which works fine for single select list boxes), only one of the selected items is displayed in the cell and not even the one I selected.

View 3 Replies View Related

Populate Cells From Multiple ListBox Choices

May 22, 2008

I am having a problem with some code I am trying to work with. My problem is that I need to source information which is selected on a userform in 3 list boxes and copy this information into the excel sheet behind on the same row.

View 2 Replies View Related

Create "Master" Listbox From Choices In Another Listbox

Apr 18, 2006

I have two listboxes on a userform. One is the "choice" listbox, the other is the "master" listbox. Each item selected is a billing object on a sales invoice.
problem: The master list works fine when the item is selected in the choice list. But when it is deselected, how can you REMOVE it from the master list? question: How can I add a text box automatically to the userform to allow the user to enter quantity info?

For I = 0 To CodeList.ListCount - 1
If CodeList.Selected(I) = True Then
obj = CodeList.Column(0, I)
p = 6
test = 0

Do While test = 0 And p <= 25
If sheetsales. Range("C" & p) = obj Then
test = 1
test = 0
End If
p = p + 1
If test = 0 Then.........................

View 2 Replies View Related

Copy Or Clear A Range Based On Checkbox

Jan 30, 2008

I am good at excel but an infant with VBA. I have inserted a checkbox onto my worksheet. I want it, when checked, to select a row of data ("k5:k16") from Sheet 1 and copy it to ("a1:a12") in Sheet 5. When the checkbox is unselected, I want the ("a1:a12") to be cleared. I've tried several different ways to copy and continue to run into errors. I know there is a simple solution, but it's beyond me.

View 2 Replies View Related

Clear Listbox Selection

Feb 6, 2008

I've got a userform with a listbox, and want to clear the listbox's selection after the user clicks on it (and an operation is performed)

I've tried setting the listindex to -1, but it behaves strangely and calls the listbox_click function again...

Private Sub LocationsAddable_Click()
AddNewLocation (LocationsAddable)
'LocationsAddable.ListIndex = -1
End Sub

with the second line commented, it only runs thru the sub once, but selection not cleared....
if i uncomment the second line, then i get thrown back into this same sub....

View 9 Replies View Related

Copy Ranges To Another Based On Criteria

Jan 17, 2008

I have a worksheet that has data in columns a through d. Acct#, Name, Usage Code, and Other Code. Acct# and Name are self explanatory, usage code is a two letter code, and other code is a field I added to use with my macro. A specific account number could be repeated many times, but it is the usage code I am needing to work with here. My acct numbers are sorted in numerical order and column d has a 1 each time a new account number appears and a 2 if it is a repeat. I am wanting to copy the account number of any account that does not have a specific usage code listed at least once. That specific code is "ta". I am wanting it to only list the account number once in column f starting at row 2. Here is my code that is not working.

Sub test()

Dim CopyNum As Integer
Set CopyNum = 2
Dim Switch1 As Integer
Set Switch1 = 0

View 3 Replies View Related

Copy Rows To Another Workbook Based On Listbox Values Returned

Dec 7, 2009

I'm trying to copy Rows from a worksheet ("sheet1") in Workbook ("SourceData") to another workbook ("Final") and worksheet ("Regions") based on critieria selected in a listbox ("Listbox" located in a user form in "Final". The listbox selection is pasted in another worksheet, "Steps"). The trick is, the selection can be a single choice (Region2) or multiple choice (such as Region1, Region 3 and Region 7) and I'd want to copy any rows containing the selection criteria. I've got a bare bones start, but I can already see it's going to give me trouble. Here's what I've got:

View 9 Replies View Related

Copy Over Rows To Sheets Based On Value According To Ranges

Oct 9, 2009

I am having trouble with IF, ELSE and END IF statements. In Column H I am trying to copy over rows to sheets based on value according to ranges. I am trying to use the code below but everything seems to get copied in to the first sheet "0-500". d= worksheet name.

View 6 Replies View Related

Data Base UserForm - Clear ListBox

May 9, 2006

I have the existing code below. What I would like to do is clear the ListBox of all previous records found prior to the next Find All event occurring. For Example I search for "M" and it finds 3 records and these are listed in the ListBox for the user to select from, then if the user searches for "Grealy" it finds 1 record and puts it in the list but the 2nd and 3rd record from the previous Find All event still remain.

I tried using the following code


which clears the listbox but then as soon as you hit Find All following the above mention sequence you get the result as outlined.

Private Sub cmdFind_Click()
Dim strFind, FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Set rSearch = Sheet1.Range("b2", Range("b65536").End(xlUp))
strFind = Me.TxtEmpName.Value 'what to look for
Dim f As Integer

View 4 Replies View Related

Clear RowSource Of Multiple ListBox Controls

Mar 17, 2008

I have some problems in VBA Excel List Box which I want to rectify. See an attachment " listbox. zip" file

I craated a Userform and Seven List boxes in it. the following problems I am facing.

1) When I change List Box 1 other List Boxes do not show with related data.

2) List Box 1 and List Box 2 show their header others List boxes not. Why?

3) I have defined Names of all ranges. How Can I use it in VBA Coding?

View 3 Replies View Related

Auto Data Entry Based On Dropdown Choices?

Jul 31, 2014

I need to know how i can select an option from a dropdown list and have it drew data from a table and place it in a certain cell based on my choice.

i have attached a sample form.

in the sample form i have two dropdown lists.

Countries and POD

What I need is when i select an option from the POD list the data on the Rate table will appear on the left side in order of course.

Attached File: test1.xlsx‎

View 3 Replies View Related

Hyperlink Based On Data Validation List Choices

Feb 6, 2008

I want to select a Hyperlinked file.These files are named using Data in ColumnA,B & C. The realtionship between filename and data in columns is; A-B-C.ext. After selecting data A from the dropdown list,i should be able to select corresponding data B and then again another dropdown list for corresponding data C.This should show the desired already hyperlinked file. I want to select the hyperlinked file name by simply selecting all the three data independently.

View 3 Replies View Related

Clear Named Ranges

Jan 5, 2009

There are over 100 named ranges in my excel 2007 workbook. Is there an easy way to clear all of these out?

View 9 Replies View Related

Clear Ranges On Different Worksheets

Dec 20, 2006

Clicking the button opens the MSgbox then on clicking Yes to run the code it stops on - Range("E3:BU98").Select - saying range selection method failed, and i cant understand why it seems to be such a simple piece of code.

Private Sub CommandButton2_Click()
Dim msg2 As Integer
msg2 = MsgBox("Has a back up copy been saved?" & vbCr & "Are you sure you want to clear all existing products and their results?", vbYesNo, "Delete Products?")
If msg2 = 6 Then
Worksheets("Input Record").Activate
Worksheets("Results record").Activate
Worksheets("Input Page").Activate
End If
End Sub

View 2 Replies View Related

Clear Non-Contiguous Ranges

Feb 28, 2008

I'm getting an error on Range("AB6:AS12,..."). The error is: "1004 Runtime error, Method 'Range' of object '_Global' failed". My code should copy the values from place A to B. Then clear the content of B. Heres my code:

Sub Ny_uke()
' copy cells
Range("B21:K50").Value = Range("AX21:BG50").Value
Range("B54:K83").Value = Range("AX54:BG83").Value
Range("B87:K116").Value = Range("AX87:BG116").Value
Range("B120:K149").Value = Range("AX120:BG149").Value
Range("B153:K182").Value = Range("AX153:BG182").Value
Range("B186:K215").Value = Range("AX186:BG215").Value
Range("B219:K248").Value = Range("AX219:BG248").Value
' clear content of cells
End Sub

When the error occurs I can press 'Debug' and continue the script whiteout changing the code and the code will continue without any errors. If I place the line 'Range("...")' on top of the script no error occurs. I I split the 'Range("...").Clear' into several Range("AB6:AS12").Clear the problem solves, but this produces several unwanted lines of code.

View 7 Replies View Related

Random Unspecified Error On Listbox Clear Method

Oct 24, 2006

On a userform, I have a listbox named "eList" containing Employee Names.
I have 2 other listboxes named "List_eTrained" and "List_eNotTrained" which list the Training completed and Training not yet completed for the selected Employee.

When I click Name items, it updates both listboxes with information (using "AddItem").
Before it updates, I use this code to clear the info listbox items:


When I run the form and click on various entries, it works as expected. At random though, I get this:

Run-time error '-2147467259 (80004005)':
Unspecified error.
And only this line is highlighted in Debug mode: TrainingDetails.List_eNotTrained.Clear

Sometimes it happens after going through several names, sometimes it happens when clicking the 2nd or 3rd name. It's only happening on the "List_eNotTrained" line though--the other line is fine--which has me really puzzled.

I'm hoping someone else has run into this before and might know what's causing this random, unspecified error.....

View 6 Replies View Related

Auto Filter Based On User Input With Multiple Choices

Apr 29, 2014

I have here an example of how I might extract data from a source workbook and paste it onto a template workbook, using autofilter to find the rows that are needed. It is currently set-up to take rows that match "AREA 1" or "AREA 2" in column A, along with a number of other criteria in other columns.

What I want to do is add into this macro, a way for the user to be able to choose the filter options will be used for column A of the source workbook. So if they want only "AREA 1", they can choose just that, or if they want "AREA 2", or perhaps both "AREA 1" & "AREA 2", or further additional options. They need to be able to specify just one, multiple, or all of the filter options with regards to column A of the source data.

In effect what I need is a user input box or form with a dropdown menu that will give the filter choices based on column A of the source data, and then for the code that copies the data across, to do so based on which choices the user makes in the filter menu of the input box.

I have uploaded examples of the source data workbook and the target template workbook. The macro exists within the template workbook.

[Code] .....

How it may be possible for the user to be able to specify, one, multiple, or all available filter options for column A of the source data, via a user input box or form.

Attached Files :

Sales Analysis Template1.xlsm‎

View 2 Replies View Related

Clear Contents Of 2 Ranges On Multiple Sheets

Mar 29, 2012

I need to clear the the text in the same cell ranges on multiple worksheets. on a regular basis.


Is there a simple way to do this?

View 7 Replies View Related

Clear Contents Multiple Non-Continuous Ranges

Nov 16, 2006

what is described in the post clear "contents of rows based on cell contents in column" clear contents of rows based on cell contents in column

What is different is that for rows that I wish to leave untouched, I have in column A the text "Active". If that text is not present (""), then I need to clear contents for only certain cells in each row because others have formulas. For example, if A32 does not say "Active" I want to clear contents of cells D:32, G:32, etc while leaving the others in row 32 in tact. I need this to loop through about 400 rows. If it would make the macro easier, I can have the data in column "A" say "Inactive"/"Active" instead of blank/"Active"

View 4 Replies View Related

Data Validation - Creating Subset Choices Based On Adjacent Column Choice

Oct 27, 2013

I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.

1 Fruit Oranges
2 Veg Lettuce

Data Validation source for Column B (H2:H4)

Data Validation for Column C: (I2:I10)

View 3 Replies View Related

Creating Range Of Listbox Based On Selection Made In Another ListBox

May 1, 2014

I have two lists mainly TV Brand & There Models.

List 1 (TV Brand)
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

Select Listbox Entry Based On Another Listbox?

Mar 18, 2014

I have 2 userforms. UF1 and UF2. UF2 has a rowsource set to its Listbox. UF1 has a search function that searches the original sheet. Now I want to double click on an entry in UF1's Listbox and select the same entry in UF2's Listbox. I want to then work with that entry in UF2.


I do all of this to circumvent Excels restriction. I can't search in a rowsource Listbox, but any edits done to my new Listbox wouldn't be made to the Excel sheet.

View 6 Replies View Related

Move Ranges To Corresponding Ranges Based On Column Having Data

Oct 25, 2007

I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.

View 5 Replies View Related

Fill/Populate ListBox From 2 Ranges, Sort & Remove Blanks

May 18, 2008

I need a listbox or combobox in an excel form whos list is created from 2 ranges. for example first name is range a2:a500 second name is rangne b2:b500. the list box should list both coloums in a single list and return the row number. better still if it does not list blank ranges. i have never used a list or combo box before so please explain in terms a simplton can undrstand

View 7 Replies View Related

Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)


When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Copyrights 2005-15, All rights reserved