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 RepliesI am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies View RelatedI'm relatively new to VBA and require programming help with the following:
I have created a button to add a specific worksheet template ("TE - Template") after another worksheet ("CO - Cockpit") and then name it:
Sub Add_worksheet()
Sheets("TE - Template").Select
Sheets("TE - Template").Copy After:=Sheets("CO - Cockpit")
ActiveSheet.Name = "AL - Class 1"
End Sub
However, I would like to let Excel check (via VBA) if the "AL - Class 1" worksheet already exists. If it does, the same template sheet should be added but named differently: "AL - Class 2". This should be possible for X worksheets (i.e., "AL - Class (X + 1)" everytime I add a new template worksheet. Thus, I would like to keep the same name (i.e., "AL - Class"), but with an increasing number (i.e., 1, 2, 3, X).
how I should amend the above code or supply me with a better (and efficient) way of programming this query?
i would like to use a macro which copies a worksheet from a workbook to the same workbook and paste it at the end and then renaming it... everything using a macro...
View 9 Replies View RelatedI would like to create a macro that would bring up an input box or preferably a list box that will allow me to input information for a sheet/tab name where where "TBL NPL NGRPL" appears in the code at the end of this message. The macro needs to be available to any new file created
The only worksheet names needed are below.
TBL NPL NGRPL
TBL NPL NIAU7
TBL NPL NIAU8
TBL NPL NIA10
TBL NPL NNDU4
This is the extent of my ability:
Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "TBL NPL NGRPL"
Range("A1").Select
End Sub
I created six macros, but there must be an easier way.
I have a workbook with 37 sheets in it. 36 of them are three per month (e.g JanCash, JanWeekly, JanMCR, etc) Since we make updates/ changes to the file throughout the year I cannot reuse the files. Also since we have accounts on different fiscal years, I need a way to rename the sheet tabs from a list. Does anyone know how I can do this with VBA code?
View 2 Replies View RelatedI'm attempting to add a variable number of worksheets (dependant on the number of entries in an existing list), then rename those sheets to the name of the entries in that list. i.e.
LIST
A
B
C
With the list above, i'd want 3 new sheets added, one named 'A', one named 'B' and one named 'C'.
The trouble i'm having is that because the number of entries/sheets varies, i don't want to refer to 'Sheet1' 'Sheet2' etc. to rename them.
***Additional Info*** the first record in the list is always "W4" on sheet "Data"
Below is where i've got to so far (however, i'm pretty new to this so it's almost certainly not all that efficient!)
Sub setup_1()
Sheets("Data").Select
Range("W4").Select
[Code] .......
I am desperately in need of a macro that will count the number of items in column A of each worksheet and then summarize the results next to the names of the worksheets (i.e. in two adjacent columns) on Sheet 1 of the book. Obviously, Sheet 1 needs to be ignored by the macro.
View 3 Replies View RelatedI 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".
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?
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
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.
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".
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 RelatedI 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.
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 RelatedI 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.
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 RelatedHere 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.
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 RelatedI am trying to rename a chart in Excel 2013, but it keeps reverting my names. It looks like there might be a 32 character limit on the name length, but I cannot find any documentation supporting this.
In case it's not clear, I'm trying to rename a chart from Chart38 to "Productivity - Tractors CY/OP - Month", but once I press Enter, it changes back to Chart38. If I can shorten the name to less than 32 chars it seems to work, but that will be nearly impossible for some other charts.
Is there a way of linking the worksheet tab names to a cell in the spreadsheet
View 9 Replies View RelatedI 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?
there is a named range on the worksheet in cell A1- "GiGS"
I want to type "GIGS" in 1st cell and then in 2nd one to write formula "=A1*5". So excel need to understand that in A1 there is a name and not just a string
I would like to change the worksheets names (many, anywhere between 50-100) to a cell (A2) value in each of the worksheets.
View 9 Replies View RelatedWhat I want to do is have a ComboBox (I think it should be a ComboBox, I want the user to be able to select multiple options at once) and display the names of the worksheets in the ComboBox. I could not find a way to do this directly with rowsource and thought about writing a loop to check the current worksheets with their names and put the values into cells, to this way have the names displayed in the ComboBox via rowsource?
View 9 Replies View RelatedI have this logic that clears cells in all WS in WB.
The logic is using each sheet name to reference the logic to clear the contents.
Look:
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:=""
With Application
.ScreenUpdating = False
.EnableEvents = False
Response = MsgBox("This Action Will Prep For A New Week. Do you want to Continue?", vbYesNo)
If Response = vbNo Then
Exit Sub
End If
Range("H7") = Now()
Sheets(Array("Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday", _
"Tuesday")).Select
Sheets("Wednesday").Activate
Range("B9:H25").Select
But I also want to change each sheet tab name to reflect a cell that has the date. But how to do this with the sheet reference in the logic? Couldit be dynamic?
Is there a way in VBA to get a list of all worksheet names in a workbook?
View 2 Replies View RelatedI 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.
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]....