I have a workbook called MASTER. MASTER has a macro I need to run in another workbook(wb2) I receive thru email. The problem is that wb2 can have any given name because someone else creates it.
With MASTER and wb2 (which can have any name) open, I need a macro that would select and/or make active whichever other workbook is open OTHER THAN MASTER.
trying to import another excel workbook with its location being flexible as the file-name will change.
This is what i got so far
Code:
Dim selectFile As String
selectFile = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls*" _
, FilterIndex:=1, Title:="Open Excel file" _
, MultiSelect:=False)
[Code].....
I am working with two files everyday: today's and yesterday's. Each report has the day's date in the name of the file.
So I'll take today's (11-5) and import it into yesterday's (11-4). Then, tomorrow I'll take 11-6 and import into 11-5. The day after, I'll take 11-7 and import into 11-6. So on and so forth. I'm trying to figure out how I can get VBA to accommodate these changes in names. So that it sees the date in the name of today's report, sees that it is today's, then selects yesterday's (or says "Open yesterday's report!"), and imports the information.
I can figure out everything else, just not the naming issue.
I guess I could always stipulate to the person running it that they should only have these two workbooks open and that they should run the macro starting in yesterday's workbook. Just writing VBA code to select the only other workbook so VLOOKUP can be done from it. But I am trying to make it fool proof.
I want to make a range variable ("testrange") that I can use to define a range of non-continuous cells and then use that range variable to select that range of cells in different worksheets within the same workbook.
I tried the below code (simply coloring cells red), but get an error when I try to select "testrange" after moving to the next worksheet.
[Code] .....
The next worksheet is also supposed to have the same range of cells colored red, but I can only get it to work on the first active sheet.
I would like to avoid having to repeatedly rebuild the range I want selected each time I move to a different worksheet.
I have x number of sheets in a workbook, and I am looking to copy sheets a, b and c into a new workbook that I will create.
The a, b and c are in no order, or consecutive (although they could be). I want to be able to count the number of sheets in the workbook, and traverse through that to find sheets that I need, and select them to copy over to a new workbook.
I have an employee schedule that is a whole year. I'm trying to extract only the next two weeks worth of columns. I'll schedule it to run once a day. Some information... The calender has the dates listed in Row 2 on Sheet called Daily Schedule The 2 week calendar would be on sheet 7 called Two Week Calendar There are only weekdays on sheet 1 Daily Schedule so if there is nothing matching today's date just stop and do nothing I would like it to save the whole file when complete
I have written some of the simplest code but I don't know how to select by today's date then copy that column plus 9 to the right.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:Schedule.xlsx")
[Code]....
is there any way to open a workbook and select a sheet within the book without having the entire name?
I've got a unique workbook and worksheet identifier but don't know how to use wildcards to get my macro to open the right workbook.
I have a worksheet which is used by multiple users at the same time, at the end of the day I can have upto 10 files. The layout of all the files is the same, same number of columns and rows, the only difference would be the file name eg:Test1.xls, Test2.xls etc. information in test1.xls will not be any other file. At the end of the day, these files are emailed and saved in a particular folder.
What I need is get information from all the files saved in a particular folder and keep columns which have data. Additionally, if there is no data for any columns after combining the columns to display an alert. The final file should have information with only data in individual columns and arranged in the order such as Center 1, center 2, Center 3
Eg:test1
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 center 3....upto 30 centers
1 4
2
3 5
test2.xls
Column A Column B Column C.....upto 30 columns
Center 1 Center 2 Center 3.....upto 30 centers
5
The final file will look like this
Finaltest.xls
Column A Column B Column C.....upto 30 columns
1 4 4
2 5
3 5
I have a main workbook that uses a macro to open a workbook provided to me from another department. This workbook contains a sheet that is basically a copy of the sheet in the main document and has the same sheetname and same codename.
The macro should copy the sheet data from the site workbook to the sheet in the main workbook. However, when i tell the workbook to select the sheet in the site file using its codename it errors....
I have 30 and above sheets in a work book and like that I am having 5 such
books. The sheets are named as 201, 202, 203 ....etc as per the contents in
that particular sheets. (201, 202 .....are the P.O nos.). all the work
sheets are of having similar format of datas.
Now what I need is if I want to look the details of one single sheet (say
324) I have go all the sheets one by one and it is hard to find out.
If any body give me a solution so that if I type a particular no. (forms
part of the name of the sheet) that sheet should appear for me.
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
How would i go about selecting a row to the left of any active cell. As far as column B
View 14 Replies View RelatedI'm trying to make a macro that selects the 3 highest values and give these cells a blue backcolor. (The code should skip any blanc cells).
View 10 Replies View Relatedi did write code to search for the data..and if the data has more than one record then a button will be appear to find all data that related to entered data... and user will have option to press on the select button to select the wanted recored and delete it or update it....the problem is that the select button show the selected data in the form but its not selecting the right data in the sheet. So when i click on delete button it delete the first row which has the same data that entered by the user.
for example, user wants to see all data the belong to the user "Tim", the forms will show all data related to the use Tim... but i want to delete the selected row not the first row with the name of Tim!
i learnt the code from this link [url]
here is the codes for find. find all, and select
Private Sub cmbFind_Click()
Dim strFind, FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Set rSearch = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.txt_num.Value 'what to look for
Dim f As Integer
With rSearch
I have a table of adressess and clients with contacts ie
a,b,c,d
client,contact,address,select
in the list there are about 300 entries.
what i want is to put an S in the select column on those clients i am due to visit and then these records to be exported into say a word doc to print off so i have a list of visits i have to do with company contact and addresses
i dont use excel that often, and normally only for really stuff, so apologies, but i couldnt find a solution.
I have a spreadsheet of expenditure, with a column for a category of what was purchased, and then the amounts in the next column, arranged chronologically. eg
01/02/08 food 20.68
06/02/08 petrol 44.65
07/02/08 food 117.65
etc, with various oither columns for week nos (for averages), comments and the like.
Now I would like to do some analysis, for example the mean weekly food spend. I think this should involve checking the category column for "food", and if found, adding the adjacent cell (containing the amount spent) to the sum (and then obviously dividing by number of weeks) - but i cant work out how to do this!
I'm trying to create a formula that searches through a row and selects certain values. I have a list of individuals in rows followed by their 18 scores in columns. What I'm trying to do is only select the best 8 of those scores and add them together. I tried a few things but this is a little over my head.
View 5 Replies View RelatedI need to select the whole column ABOVE the active cell. Ctrl-Shift-UpArrow is no good because it stops at the first blank cell. And selecting the whole column is no good either because when I subsequently paste into the column, it pastes in all the empty cells of the column, meaning my worksheets expands from a few hundred rows to 1 million!
Shift-Home does what I want on rows. Is there an equivalent for columns?
So in my code I have two set ranges and want to select from one set range to another. The code I have now does not work because I cannot select Range("POBRF:PO"). This is the code.
[CODE]
ARow = Range("A" & Rows.Count).End(xlUp).Row
Set POBRF = Range("A1:A" & ARow)
BRow = Range("I" & Rows.Count).End(xlUp).Row
Set PO = Range("I1:I" & BRow)
Range("POBRF:PO").Select
[CODE/]
I have two option buttons on each of five spreadsheets in my Excel workbook. Selecting the first option button enables an x to be written to a cell when that cell is clicked with the mouse. Selecting the second option button disables the writing of the x when the cell is clicked with the mouse. I have these working quite well.
My problem is that I want the second option button selected (disable writing of x) when I enter the sheet. I'm pretty certain that I need the code in Sub Worksheet_Activate() but I haven't been able to find the VBA statement to set the second option button on.
Also, unlike having the option buttons on a form, when I select the option button on the spreadsheet in Design mode I can see no property sheet indicating the name, value, etc of the option button.
I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do:
Select cell C192
Select Shift and R-Arrow so that C192 and D192 are selected
Select Ctrl, Shift and Up Arrow so that C1:D192 are selected
Select Shift and Down Arrow so that C2:D192 are selected
I'm trying to find a way to select all charts in a worksheet and pasting them into a new sheet as a jpeg and can't find a way to select the charts all together as a group like if you shift click them.
I'm looking to copy and paste them as a group because they are grouped in a specific way for a report.
i have this code ...
View 11 Replies View RelatedThe main worksheet is for buttons to select emergency type and what group should be notified. Subsequent sheets will have different sets of contacts - Col A= Name, Col B = email address, Col C = Yes/No indicator.
The basic code works great as long as the names are on the primary worksheet. When I try to point the code to a different worksheet, the result displayed in the MSGBOX is null. I did this by putting "Worksheets(Sheet2)." in front of "Columns("B")." I double checked the sheet name.
I have a question about selecting an entire row, and then inserting a new row. The following code will select, and then insert a new row at row 30:
View 2 Replies View RelatedIf I select checkbox1 I want checkbox3 to be false and vice versa, thats simple enough. Everytime I click on one or the other checkboxes I have to reselect the one I want.
It takes two clicks to get a checkbox checked (True).
My listbox will be populated by both lists in the "Checkboxes" code once I select a checkbox, then reselct to repopulate.
Checkboxes:
I have a table, that while writing a vba code I need to select the first column in the table (A) and the last column with data inj the table ( unknown which column it is). The last column might change (meaning that it sometimes column B, somestimes column Y etc...depends on the data). I need a way to select only the the first AND the last column in that table(actually it's the last column with data on that sheet). I'm sure there is must be a way I just don't know it!
View 2 Replies View RelatedI have 5 sheets in a workbook. After a code has run I want each sheet to be in A1 when they click on that tab (as there could be 50,000 rows on each), but when I try recording it it doesn't do it properly (Sometimes the A1 cell is active but the view is somewhere down the bottom of the sheet).
View 2 Replies View RelatedCells(xrow, 3).Select --
Is there any way I can select more than one column.
I tried Cells(xrow, 3:10).Select but it gives error.
I have a macro that my coworker runs weekly to update info. The macro depends on data in a workbook that comes from another source who arbitrarily decides to rename things. I put into my code a 3 situation scenario.
1. If the sheet name = what it is supposed to then activate it
2. Else If the sheet name contains the word "Pivot" then activate it
3. Else load a userform that lists the worsheets in the workbook and allows the user to select the proper sheet.
The code I have is below. It works until I introduce the 3rd option. Is "Else" not the proper syntax here? Because when I run it, it jumps straight to the "Else" statement and loads the userform even if the sheet is named correctly or contains "Pivot" in its name.
Code:
Sub FindPiv()
Dim strWSName As String
Dim s As Worksheet
For Each s In ActiveWorkbook.Sheets
[Code] .......