I am working on a macro, which should cycle through a column and find all rows with a given value. Then for these rows it should paste values from other columns into another worksheet.
Example:
1 2 3 4 5 6
Find all cells in row 6 with "yes" and paste the value from row 3 into another workbook.
Issue:
I cant seem to get the the value from row 3.
ActiveWorkbook.Worksheets(datasheet).Cells(rowtocopy, 6).Value.Copy has an error. Currently "Object required". Ive tried: .cells().value.copy that gives me a different error.
Script is started in the workbook where the extracted values should go.
Below its called "Template (2)".
The workbook with the rows is called "Datasheet" (which may be "Volume" or "SNiC data" depending on what is written in Worksheets("Template (2)").Range("B4").Value
Code:
Sub copychanges()
'################ EXTRACT ORIGINAL PAYMENT DATA
originaldata:
Dim currentwb As String, SuppXls As Excel.Workbook, datasheet As String, i As Long, targetcolumn As Range, modcorrect As String, fromcolumn As Range, rowtocopy As String
currentwb = ActiveWorkbook.Name
SuppPath = ActiveWorkbook.path & "" 'file path (assuming all file are in the same folder
SuppName = Worksheets("Template (2)").Range("D4").Value 'filename of corrected file
We currently have a macro that has a limitation that it only works on 8 sizes, and only upto 12 Colours. We want to change this to make the number of sizes and colours totally variable. how to change this bit of code below so that the cells selected are not fixed but are variable depending on the number of sizes and colours as entered in sheet 2.
Current Code for When 8 Sizes and 12 Colours
Code: Sheets("Sheet2").Select Range("B30").Select ' This is Fixed Selection.Copy Sheets("Sheet3").Select Range("G2:G109").Select ' Want this to be variable based on the number of rows needed starting at G2, this can be found out from Sheet2, Cell G28 ActiveSheet.Paste
[code]...
On Sheet 2 Cells G23 is used to enter number of Sizes, and G24 is used to enter number of colours. Cells G25, G27 and G28 then have formulas to work out other values that will be needed in the macro.
I am trying to write a routine (via two comboboxes) that will enable users to select a choice of charts from one of three possible worksheets and have the charts on the selected sheet copied to PowerPoint. My difficulties are these:
a. I cannot determine how to pass the selections to Excel (see b. as well)
b. In some instances my charts are actually groups of a chart and a picture. how I should reference these? They are obviously not ChartObjects: when I use the macro recorder all I get is a range.
I am using code that will copy several formulas in adjacent cells to the end of range; however, when I try to use it to select and copy formulas in non-adjacent cells, I get an error. Tried to modify, but I'm new to VBA...still learning. Here is the code that I currently have.
Dim LastRow As Long LastRow = Range("A65536").End(xlUp).Row Range("D3").Formula = "=RC[4]/RC[3]" Range("F3").Formula = "=RC[-1]*RC[1]" Range("J3").Formula = "=RC[-1]/RC[-3]" Range("D3,F3,J3").Copy Range("D3,J" & LastRow)
I am trying to take two random Mouse click selections and swap a set of ranges associated with the cells that are selected. For example if the user clicks on A1 it will resize(3,22) and store the selection as a variable to be swapped with another selection. Here is what I have so far but I keep getting a object required error on rngEmp1.
VB: Sub SwapGroup() If Selection.Cells.Count < 8 Then MsgBox "Please Select two Groups to swap. Press and hold 'Ctrl' in between your selections", , "Swap Groups"
I'm trying to use combobox1, say, department, to filter items listed in combobox2, say, employees. I don't want all employees from different departments listed in the combobox2 so as to make the user spend time looking, but only names of a particular department once that department is selected in combobox1.
Can a Function give two or more output variables. e.g.
Sub a() x = 5 result = Y(x) End Sub
Function Y (x As Integer) As Integer Dim B B = ... * x Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that? I need this because function works with large matrix and I want to extract some values appeared in between.
I have a userform with multiple buttons and a listbox. When any button is clicked on, a listbox is created. The user selects various items on the listbox. Then the user clicks another button and selects other items based on a new list. If user goes back to click the original button, the original list shows up, but the selected items are not highlighted. Is there any way to keep those original selections highlighted (selected)?
On one sheet I have a long list of items which I need my users to scroll down and, when needed, fill in a value next to the appropriate item in the list. Most of the list items will not need anything inputting against them. Then on a different sheet I need a nice tidy summary list which only shows those list items which have an input against them.
The bit I can’t do is getting these few items to display (on a separate sheet) in a nice neat all together, contiguous way - ie with no gaps. Also I need to populate this summary list automatically. ie after each entry.
how to track selections from a dropdown menu, I have 8 selections under the dropdown that are being selected from a list. Next to the list I want to have the total of the number of times it was selected on the dropdown. I've tried the data>subtotals, but It only seems to create 0's under the dropdown box.
I dont want the totals in the dropdown box I want it next to the list of 8.
I have not used Inputboxes or Userforms much. I have read through some of the messages here, but I need to learn more about them. I need to create some type of user interface, where the user would be asked to select between two choices. Based on the choice made, a macro would be executed. The two choices run different macros. I need direction or an example of an Inputbox that shows two choices instead of a blank input space.
This should be very easy, but I am not getting it to work. I have a userform with a combo box and I just want to hard code the values to it in VBA. I want the value in the combo box to be 1 - 10. Here is what I have so far.
I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.
Cell B2 contains the numbers I through 8 Cell D2 contains a pre-calculated number (e.g. 53.012) Cell F2 is the cell that I need an IF function or something similar – the function would be as follows =IF(B2=1,D2*1.000) =IF(B2=2,D2*1.00057) =IF(B2=3,D2*1.00171) =IF(B2=4,D2*1.002281) =IF(B2=5,D2*1.003421) =IF(B2=6,D2*1.003991) =IF(B2=7,D2*1.004215) =IF(B2=8,D2*1.004538)
Selection.AutoFilter Field:=10, Criteria1:=mycheck, Operator:=xlcontains xlAnd The problem is that I have a value that is collected and put in to MyCheck. This value is only contains a few letters that I want the autofilter to filter.
e.i: The value i want to filter contains following. 01jan2007 What the autofilter is supposed to do is to show all cells that contains the value 01jan2007. The cells that are supposed to be autofiltered looks like this. 2143 01jan2007 and so on... I can not use "=" course the value is not the same as the others.
I execute custom Insert and Delete macros whenever when the user has selected a single contiguous range of rows to insert or delete.
But I don't know how to detect if the user has selected multiple, non-contiguous rows. Is there a way to detect this situation, and if so, is there a way to "loop" through the individual selections?
On a multiselect listbox, is there any way the user can select a "block" of selections, instead of having to select them one by one? The list box I have has about 100 choices and it would be possible to select blocks of choices at a time. This is similiar to MS Outlook email where you can push the shift key to isolate a block of emails to move to a different folder.
I have a workbook with 3 listboxes: Listbox1, Listbox2 and Listbox3. Selecting an item in Listbox1 will query a database which populates a hidden worksheet. Listbox2 then populates itself by drawing on entries in the hidden worksheet. Selecting an item in Listbox2 populates Listbox3 in the same way.
At the moment I have the spreadsheet so that nothing is cleared when I close or open the workbook, allowing the listboxes to remember what they were populated with when I closed the workbook. However, the listboxes will not remember what was selected when I closed it. Is this something which I can easily fix with a listbox property? If not, what is the easiest way for each listbox to remember what was selected when I closed it?
updating validation selections. I have attached an example of a bigger worksheet I use.
Sheet data: this sheet contains the raw- date. Data will be added and changed continiously.
Sheet pivottable: this sheet is used to sort the drowdown list and filter out double entries. A Dynamic named range is set to the pivottable
Sheet form: in this we will choose a code by a dropdown list and the column data 4 is filled by a vlookup formula.
If the data is changed in the sheet data and we’ve refreshed the pivottable, the code on sheet form contains previous data, so the vlookup formula can’t find the data (resulting in n#a depending on using a ISERROR formula)
The following solutions I’ve already tried:
- A userform with a lisbox/combobox in combination with listindex and an index formula. The problem is that when I sort the pivottable the index and data do not match.
- I also used the information in this thread but this didn’t work.
Maybe I have to change the construction of my worksheet, but it is important that it works.
I'm trying to see if it's possible to create a list box where I can select multiple values. For example, if a create a list box with the alues, "ant", "dog", and "cat". Is it possible for me to give the user the option to choose, "ant" and "cat"?
I have a form with 3 checkboxes. When any of the 3 checkboxes are checked, a set number of items are selected from a listbox. If a checkbox is checked, and then a new checkbox is checked, the listbox selection is cleared and selected with the new values from the new checkbox.
Problem: How can I code this so that if I check a checkbox and then deselect that same checkbox, my selected listbox items are cleared? Only when I select a different checkbox are they then cleared and repopulated.
Private Sub CheckBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim lngitem As Long For lngitem = 0 To ListBox1.ListCount - 1 ListBox1.Selected(lngitem) = False Next CheckBox1.Value = False End Sub..............................
Here is what I'm trying to create: I've put a command button on my worksheet that pops up a user form with a list box, an "insert" command button, and a "close" command button. I want to allow a user to make multiple selections in the list box, click the "insert" button, and have the selections inserted into the worksheet at a specific cell and fill down. I'd also like for it to allow the user to re-open the user form and add to the list after closing it.
I've hap-hazardly set up the list box for multi-select, but I'd like to see how it's "supposed" to be done! Also, as it stands, my list box selections will populate any cell that happens to be active at the time the initial command button is clicked. I've searched this forum and found that folks have posted similar questions. Those who replied posted a link, which I tried to follow but the page that followed could not find the file.
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset) Dim fd As FileDialog Dim xl As New Excel.Application Dim wb As Excel.Workbook Dim ws As Worksheet Dim iFilePicked As Integer Dim strFilePath As String fd.Filters.clear fd.Filters.Add "Excel files", "*.xls" fd.ButtonName = "Select" iFilePicked = fd.Show If iFilePicked = -1 Then strFilePath = fd.SelectedItems(1) Else ..................
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
Is there a way of selecting several print areas on several worksheets and printing it on ONE page? Selections will not be the same size.
Also is there a way to lock your printareas? I would like to save the spreadsheet and open it up next month and the printareas are still there ready for me to print. It seems to reset after i have printed it
I have created a named range that selects about 30 different cells (non-contiguous) and I am trying to copy over all the formulas and paste with values. However excel is not letting me to do this. It says this command cannot be applied to multiple selections. Can I get around this?
