Editing Worksheet Which Pulls Data From Another Worksheet?
Jan 28, 2013
I have a two identical worksheets in the same workbook with two tabs (Search, Database). The Search worksheet contains a search engine at the top which searches by column, and pulls rows from the Database tab, which match the search queries. However, the rows that are pulled up in the Search tab cannot be edited as they are all MATCH formulas. Only the rows in the Database worksheet can be edited.
This can be quite problematic if I am searching a row to edit it. Although the search engine is convenient enough and faster than filtering (as I have over 20 columns), when the row comes up, I cannot edit it. I have to go to the Database sheet and filter through until I find that row, and THEN edit it. What I require is that when I search for something and the matching rows get pulled up, I want to be able to click a button which will take me to those rows in the Database sheet.
View 14 Replies
ADVERTISEMENT
Jun 11, 2014
I am working on a project that I feel should be relatively simple but I seem to be stuck. My Goal: To add information to specific rows of data in the worksheet. I want to use a user form, and eliminate as much typing for the user as possible. I have already designed my user form, and written the code to identify the row in which I want to edit. Nonetheless, I do not know how to code the insertion of the new data. The data I want to add will be in empty cells at the end(right) of the data table.
I have attached a portion of my data set.InsertQuote.jpg
Here is where I am. The words in red are just colloquial words that I cannot seem to put into code.
Private Sub CmdInsert_Click()
Dim Company As String
Dim PartNumber As Variant
Dim Condition As String
[Code] .....
View 1 Replies
View Related
Mar 16, 2007
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies
View Related
Jun 3, 2009
From every sheet I want to create the same type of graph, so a macro could be very useful.
First I recorded a macro. The worked fine, but it could only be used by only one worksheet. Every time I used the macro it returned to the original worksheet where I created the macro.
With some searching I found out how to change the code so that the macro could be used for the active sheet.
But now I get stuck with the following error: "compile error: invalid qualification" (translated from Dutch).
With error tracking I get stuck at the first line "Sub grafiek()"
View 4 Replies
View Related
Sep 3, 2008
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close
Else
End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
View 5 Replies
View Related
Sep 19, 2013
I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"
[Code] .......
View 1 Replies
View Related
Dec 23, 2013
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
View 4 Replies
View Related
Feb 7, 2014
I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
View 1 Replies
View Related
Aug 22, 2014
I've got a spreadsheet with 2 worksheets in it.
On the first one we've got:
Name of the agent | petition REf num | Task
Each petition can generate several tasks, one line per task.
John Doe | XXXX-YYYY | NCO
John Doe | | RIL
John Doe | XERT-WWWW | RMT
Jane Doe | QSZE-AQWC | RIL
On the second worksheet:
Complete list of agents | number of petitions | Status
John Doe | 2 | OK
Jane Doe | 1 | [BLANK]
I want to be able to fill in the second worksheet automatically. For each agent in my worksheet 2, I want to check if they appear in worksheet 1 and if so count the number of petitions related.
I don't know where to start
View 6 Replies
View Related
Mar 17, 2013
Dropbox - Final.xlsm
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
View 9 Replies
View Related
Feb 23, 2014
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
View 6 Replies
View Related
Apr 9, 2014
Each month I get a report that is formatted by the customers and this sheet never changes. The problem is that the master sheet my job uses is not formatted this way. The master workbook has several sheets that breaks down the data from the monthly sheet. Instead of having to enter the data manually I'm looking for a way to export the data from the monthly sheets into the master workbook. As I stated the sheets are all the same but come from different providers and all contain the same amount/style of data.
View 1 Replies
View Related
Aug 13, 2014
I have a 'Summary' worksheet which is shown as:
August
September
October
Example 1
1
4
5
The table, which works correctly, looks in another worksheet 'Report' for 'Example 1' within the date range of August. This uses COUNTIFS.
Ideally, what I'm wanting to do is have an auto-filter on each of the cells - 'Example 1', 'Example 2', so if a user wants to see what rows on the 'Report' sheet, falls into this criteria, it auto filters the other worksheet, and takes them too it.
The action would be - Click on the cell of 'Example 1', the macro auto-filters based on the cells value in column F of the 'Report' sheet, and takes the user there. The macro would have to take into account the date filter as well, which uses cells A1,B1 for August, and C1,D1, for September.
View 1 Replies
View Related
May 22, 2013
I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.
View 7 Replies
View Related
Jun 19, 2013
I would like to copy the data from one worksheet to the alternate row in another worksheet as follows. I've attached a file showing the source file and the desired output.
1. Copy column B's data in worksheet "Working" to worksheet "Upload file" Column V. B2's data goes to V1, B3's data goes to V3, B3's data goes to V5, so on and so forth.
2. Copy column H's data in worksheet "Working" to worksheet "Upload file" Column F. H2's data goes to F1, H3's data goes to F3, H3's data goes to F5, so on and so forth.
The number of rows with data in worksheet "Working" varies. It could be 30 lines one time and over 100 lines another time.
View 2 Replies
View Related
Mar 26, 2014
transferring data from a worksheet (Passdown Report) to another worksheet (Data Base) located in the same workbook. In the source worksheet (Passdown Report) there are 2 cells (B2 and D2) in which I would like the data to be transferred along with the data from B4 to AQ33. All the cells contain a formula which I want to stay after the information is transferred to the target worksheet (Data Base). This will be a daily transfer to the target worksheet (Data Base), so the macros should also identify the next available open row to transfer the data to.
View 1 Replies
View Related
Feb 13, 2009
Here is the situation:
On Sheet1:
A
123
456
789
On Sheet2:
D, G
aaa123, 11
bbb456, 22
ccc789, 33
I would like the function to search strings within an array on Sheet2 for a value on Sheet1 (that is, to search for 123 within the strings aaa123, bbb456, ccc789,etc.). Once a match is found, I would like the function to return another value from the same row on Sheet2 that contains the value from Sheet1 (within the string).
Can you help me?
I must use cell references for the values on Sheet1 because I am working with thousands of unique supplier numbers. In addition, all cell formats are general. I prefer to use the INDEX(MATCH combination rather than the VLOOKUP option.
View 8 Replies
View Related
Oct 7, 2009
I receive a monthly download of individuals call-logs in one "Master File." For internal reasons, I need to separate every person's monthly call-log into individual worksheets. Unfortunately, the file is very large and copy/paste is very time consuming. I am operating on MS Excel 2007.
View 9 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
Oct 24, 2011
I have embedded a worksheet called Rawdata" into a userform. Is has a number of text boxes on the userform, all the text boxes copy correctly to a worksheet called Rawdata, however i cant get the spreadsheet data to copy from the Userform to the sheet called Datapad.
The worksheet in the userform is from A1:G600, this data needs to be copied back to the Datapad worksheet B2:F601.
code so far:
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
[Code]....
View 2 Replies
View Related
Dec 8, 2009
I have an excel 2007 work book with multiple worksheets that updates daily. I want to bring all the data from each of the work sheets into a new worksheet so that i can create a pivot table.
All the data in the worksheets have the same headings and correspond to the days of the year.
I think i need to use some sort of range look up?
View 9 Replies
View Related
Jan 15, 2010
I have a Excel workbook with two sheets - 'MainDataSheet' and 'ArchiveSheet' .
The 'MainDataSheet' has 5 columns and one of the column is 'status'.
The 'MainDataSheet' will have a command button 'MoveData' which will trigger the VBA macro to move data rows from 'MainDataSheet' to 'ArchiveSheet' . Only the rows having value set to "MOVE" in the 'status' column have to be moved.
View 9 Replies
View Related
Nov 7, 2006
I'm trying to consolidate data from many worksheet to a master worksheet. So far, thanks to the help of some experts, i've got this code. But i need to tweak it further to suit my requirements.
Sub CopyDataToConsol()
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim SrcRg As Range
How can i tweak the above code to:
1) copy the lastest updated row to the Master sheet, instead of copying the whole set of data over and over again..
2) automate it by using worksheet change?
View 9 Replies
View Related
Mar 26, 2007
I use a web query to import into an excel workbook. As data is changed on the web that the query runs against, I would like the name of the sheet that is being imported into to change to one of the cells that is being imported.
View 9 Replies
View Related
Jan 16, 2008
As I am managing a few projects at once, I would like to create a To Dos List for each project (seperated by different worksheets). To make my life a bit easier, I hope to show all the To Dos which is due on a particular day on a separate worksheet. In other words, I am hoping to write a macro which allows me to copy all the relevant To Dos (of that day) from different worksheets and compiled it into a single list on a new worksheet.
Note:
1) Each row of To Do contains 3 columns, Date, Priority and Descriptions.
2) The To Dos for each project are NOT arrange in sequence by dates.
3) Number of project will increase over time, therefore the number of worksheets will also increase.
View 3 Replies
View Related
Aug 7, 2014
I am having a problem pulling out individual data from previous worksheet (Sheet 1) to another worksheet(sheet 2, 3, 4...) individually.
For example I want to pull out the values of IP B0b/C0b Hum to another worksheet separated by each day. is there an easy way to to this using a vba code or excel formulas?
Please find attached file.
View 2 Replies
View Related
Jul 22, 2014
I have below code, but does not work correctly. The problem is when it's run in main worksheet, it doesnt match the correct workbook in every worksheet and it only generates the data in first workbook for all worksheets. It seems that it lacks something link between workbook in main worksheet and workbook in others.
Sub HzWb()
Dim bt As Range, r As Long, c As Long
r = 1
c = 8
[Code]....
View 1 Replies
View Related
Dec 16, 2008
I'm having trouble with a copy of data in one worksheet and a paste of the data into another worksheet. The problem is determining the last row of data and where to start the paste.
What I have is three worksheets. Worksheet 1 contains column headings in row one. Worksheet 2 contains 30 columns and a varing number of rows of data. Worksheet 3 contains 30 columns and a varing number of rows of data.
I want to end up with all the rows of column a in worksheet 2 copied and pasted to column a of worksheet 1. Then I want to copy all the rows of column a from worksheet 3 and paste the rows to worksheet 1, but starting where the data from worksheet 2 ends.
sample data
sheet 1 sheet 2 sheet 3
01/01/08 01/02/08
01/03/08 01/04/08
Results
sheet 1 sheet 2 sheet 3
01/01/08 01/01/08 01/02/08
01/03/08 01/03/08 01/04/08
01/02/08
01/04/08...............................
View 7 Replies
View Related
Apr 13, 2009
look for a certain value in worksheet A and copy that row of data to Worksheet B.
However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out?
PHP Private Sub GetInfo_Click()
Dim r As Long, LastRow As Long, Status As Integer
Dim Message As String, Title As String, Default As String, MyValue As String
Application.ScreenUpdating = False
MyValue = Range("A4").Value
Workbooks("invoice.xls").Worksheets("A").Activate
LastRow = Range("C65536").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 1).Value = MyValue Then
Rows(r).EntireRow.Copy
Workbooks("invoice.xls").Worksheets("B").Activate
Rows("8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Status = 1
Workbooks("invoice.xls").Worksheets("A").Activate
Rows(r).EntireRow.Delete
Exit For
End If
Next r
Application.ScreenUpdating = True
View 2 Replies
View Related
Dec 3, 2012
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies
View Related
