I am trying to e-mail a portion of a spreadsheet that I have. My approach is the following: I save a copy in a temporary folder, open that copy, remove all the sheets that I don't need to send out, save it (same name as the e-mail subject), and then e-mail as an attachment.
The current approach does not remove the macros and references though. I use the following to remove the macros and references:
I'm trying to insert two rows in a subset of worksheets in a workbook by defining a range in a worksheet and loop through the worksheets in that range, but the code will only run on the active worksheet.
[Code] ..........
How I could get this loop to work?
I'm trying to insert two rows in a subset of worksheets in a workbook by defining a range in a worksheet and loop through the worksheets in that range, but the code will only run on the active worksheet.
Sub StatePIPData()
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("A:A")
If sheet_name.Value = "" Then
[Code] .......
I have this code (got it from here: [url] that saves worksheets as separate workbooks. It works perfectly fine for that but I would like to save each worksheet as a text file (tab delimited). I tried changing FileFormat:=xlNormal to FileFormat:=xlFile but a debug error occurs.
Sub MakeMultipleXLSfromWB()
'Split worksheets in current workbook into
' many separate workbooks D.McRitchie, 2004-06-12
'Close each module AND the VBE before running to save time
' provides a means of seeing how big sheets really are
'Hyperlinks and formulas pointing to other worksheets within
' the original workbook will usually be unuseable in the new workbooks.
Dim CurWkbook As Workbook
Dim wkSheet As Worksheet
Dim newWkbook As Workbook
Dim wkSheetName As String
Dim shtcnt(3) As Long
I have a huge workbook that takes up a lot of memory. The user saves her work every day by saving the entire workbook. Hence, we are using up a lot of hard drive space. I have been trying to find a way to save the relevant worksheets in a format that consumes less disk space. I find that if I save a single worksheet as a web page without the option of interactivity the file is only about 50k. But I need to save three worksheets, not just one. Is there any way to do this? If not, is there any other SAVE AS method that allows one to save three worksheets at a reduced file size? I do not need interactivity, I simply need for the user to be able to go back and view the relevant information in each file.
View 7 Replies View RelatedIn my program I have prevented the user from saving the Workbook with the following code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim p As String
Cancel = True
Do While Cancel = True
p = InputBox("Enter password to save file:", "Password Required To Save", "")
If p = "Enter Password Here" Then
Cancel = False
Else
yn = MsgBox("Wrong password. Try again?", vbYesNo, "Invalid Password")
If yn = vbNo Then
Exit Sub
End If
End If
Loop
End Sub
I also need to prevent the user, when they use the save as, from saving certain worksheets. Ex: There are 6 worksheets in the workbook. I only want them to be able to save the first three. Issues: One of the workbooks that I want to allow them to change has a tab name that changes frequently.
I have another little conumdrum I'm trying to work out.
I'm using one of Ron's scripts as an add-in which I've just amended the output path for.
http://www.rondebruin.nl/tips.htm
This script essentially creates a new workbook from each worksheet in the active workbook.
As I have a workbook of about 500 worksheets, I am trying to group the worksheets opposed to having to split all and rejoin them manually.
Luckily all of my worksheets names are initials with numbers after them (as assigned by excel when merged):
e.g., RH, RH (2), RH (3), RH (4), AG, AG (2), AG (3) and so on.
That said there are some random ones too, but I'm happy for them to be kicked out as individual files.
Anyway I'm pretty sure there is a way this can be added into this existing script by addressing Sheet.Name perhaps but I'm a bit unsure exactly..
Sorted in order (i.e. 2,3,4) would also be a bonus but not essential as they need to manually checked and I have a separate macro for sorting worksheets anyway.
I have a workbook with several worksheets in it. 4 of them have macros written in them, some do not. I protected each sheet with a password and made it so that users couldn't select any "locked" cells, and did this on ALL the worksheets. I noticed now that, just upon opening the workbook (not running any macros yet), on the sheets with macros I'm able to select locked cells (which is wrong), but the sheets without macros still function correctly. I think that this may have to do with Excel's default protecting arrangement where you can select either locked or unlocked cells. I specifically locked all worksheets with the correct arrangement but every time I open the workbook some of the worksheets default back to Excel's default arrangement. What can I do about this problem? I need this configuration to follow the workbook and not the specific computer it's on. Many users are going to use this, and can't be able to select locked cells. My macros do unprotect and re-protect the sheets to run, and I'd wonder if I can edit these to protect the sheets in a different configuration (which I don't think I can do with regards to being able to select locked cells) but these macros don't even run before this happens, and the worksheet open event doesn't involve any unlocking or re-locking (it just disables the cut and drag-and-drop functions.
View 8 Replies View RelatedTo this point I have been able to successfully write code that will save a constant set of worksheets as a pdf. However, I would now like to alter it to be able to dynamically select the desired worksheets from a list box (I have been able to populate my list box) and then save as a pdf. The last step is where I am have issues. This is what I have thus far..
Dim relativePath As String
Dim Selected As Long
For Selected = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Selected) = True Then
Sheets("Summary").Range("Q65536").End(xlUp)(2, 1) = ListBox1.List(Selected)
ListBox1.Selected(Selected) = False
[Code] ..........
I am trying to write this code where I need to save an excel workbook with a number of tabs to 1 text file with a name of users choice.
I am able to do this if there is a separate file for every worksheet within the workbook but not if we have the same text file to append every time.
I am trying to find a easy way to delete multiple worksheets in a workbook and then saving the workbook based on the tab name of the worksheet.
View 9 Replies View RelatedI have been trying to create a Macro that can do the following.
Open a Excel Workbook, Copy the Worksheets that I need (ignoring ones that are not needed.), Save them in the correct location with a new folder of date and time and saving them as csv files.
I have all the elements but can't seem to get them to gel correctly.
Just wondering if anyone has anything like this that I could adapt or edit.
Looking for a "oneliner: or CSE perhaps that sums the maximum values for each subset. See example below. The answer should be "9".....
View 9 Replies View RelatedI have a sub where I pass in a 1200 row by 8 column named range. Inside the sub I want to create a single column, 1200 row range. I also pass in which column I want to use of the 8 in the big range. So I pass in a number between 1 and 8. I create a new range, but i am not sure how to reference a particular column. I am trying to avoid looping.
View 6 Replies View RelatedI have a workbook of data that I created a pivot table with. From there I need to rank each value of the NES, column C, within the corresponding Reporting Structure, column A. The table that I created will be update with data on a monthly basis and I don't want to have to redo the rankings every month and just have it refresh.
View 2 Replies View RelatedI've got a table on a worksheet (daylies) with daily data, dates in A6, A7, A8, ..., various data in columns B, C, D, ... .
Now I would like to make a subset table on a separate worksheet (weeklies) with just weekly snapshots, i.e.,
snapshot from daylies off row 6 on row 2 of weeklies,
snapshot from daylies off row 13 on row 3 of weeklies,
snapshot from daylies off row 20 on row 4 of weeklies etc.
Is this possible or do I have to use the sledge hammer technique of copy and paste?
I'm facing a problem wherein i have two tables A& B.
Table A has two columns and 40834 records(rows).first column is userid and second column is activeyn with respective values (0 or 1) for each userid
Table B userid column is a subset of Table A and has two columns and 411 records(rows).first columns is userid and second column is activeyn with respective values (0 or 1) for each userid.
Now I want to get activeyn value from table A of all the 411 records(userid's) in Table B.
I have a list that kind of looks like this. I sorted it by date, then by location, and then by time. I need to find the earliest item in one location on a given day and display as a list.
datelocationtime
6/1/2013 A730
6/1/2013 A745
6/1/2013 A750
6/1/2013 B800
6/1/2013 B810
6/1/2013 C730
6/1/2013 C745
6/1/2013 C800
6/1/2013 C815
6/2/2013 A730
6/2/2013 A745
6/2/2013 A800
6/2/2013 A815
6/2/2013 B700
6/2/2013 B800
6/2/2013 B815
6/2/2013 C800
6/2/2013 C815
I have a set of data that can be divided into sub-sets. I've attached the sheet of data. There are 5 sub-sets and each of these has 5 sub-sets. The sets are defined by the Portfolio and End columns.
So, the 25 sub-sets look like:
10 INTL
20 INTL
30 INTL
40 INTL
50 INTL
10 LCG
20 LCG
30 LCG
...
I'm wondering if there is a way to write a formula in cell N10 that basically pulls in '10' and 'INTL' and finds the correlation on only that sub-set of data. So I guess I want a formula that returns a range such that =correl(Range1, Range2) will work. Also, the length of the ranges will vary, so trying to write an INDIRECT(ADDRESS... will not work. I tried an array-formula in cell N10, but it didn't work. I also included my expected output. in M18:O24.
I know I can do this in other ways, but I really want to learn if there is a way I can do this with 1 formula that I can just drag down. I guess its more of a learning opportunity than something that needs to get done.
I've got a list of employees with their division and a bunch of other data in each row. I want to copy each and every row to another sheet based on the division.
_______________________________
ID | Name | Division | Other
4 | Joe | South | up
5 | James | South | over
8 | Jim | North | down
9 | John | East | left
14 | Jerry | East | right
Assuming "ID" is A1 and the list is sorted by Division, I need some code to accomplish my goal. I know C/C++ so I understand the principles of programming but I'm just learning the VBA syntax. First thought is to loop through each row moving them individually but then I have to worry about putting it in an open row on the other sheet. Then I thought about finding the first row that contains the target division and, since the data is sorted by division, select each subsequent row and copy the entire selection at once to avoid fumbling around in the destination sheet. Hopefully someone out there can make sense of this and help out. Please let me know if I need to elaborate.
I would get the End of day bhavcopy from the exchange which is a .csv file. The first column contains the column heads like name, open price etc. and the first column. Now I have to extract the names and the corresponding column values from this list by referring to a notepad that contains the ticker names to be extracted, place in another workbook and save that file.
Can I use MATCH to match a subset of characters?
Example....Looking for value in a range that matches cell A1 (which reads 'DOGS')
MATCH(A1,'SEARCH_SHEET'!H4:H2590, FALSE)
This will look for a cell with 'DOGS' in it.
But what if I want a positive match on a cell that has 'DOGS & CATS'
This fails because it doesn't match exactly.
I have a set of 20 numbers that I regularly wish to select 6 numbers from, without duplicates
View 9 Replies View RelatedI have a sheet of data that is imported from an external database. Depending on which report gets imported, the column count and column location will vary. However, the headers for the columns I need to work with are constant, therefore I create a named range for all columns, and reference the ranges as I work with the data and avoid hard-coded cell references. Therefore, the ranges are one cell wide, with up to 2000+ rows.
Here's my problem. I need to reference a sub-set of a named range, to get the min of the subset. I've searched this board and others, but can't find an example of this method. The code is within a loop, and the range subset location is determined by a count on another named range.
The data involves Tasks and Subtasks. Each task can have multiple subtasks, and each subtask has it's own start and end dates. I'm trying to determine the earliest start date and the latest end date for each individual Task so I can determine the total duration for the Task.
Here's the code that determines the cell range. This code is working.
d2 = WorksheetFunction.CountIf(Range("ASR"), Range("ASR").Cells(y, 1).Value) ' count number of tasks in this ASR, store in d2
The code that I can't figure out is below. The compiler can't figure out what I'm trying to do. (Neither can I!!)
d3 = WorksheetFunction.Min("Planned_Start_Date").Cells(y, 1), Cells(y + d2 - 1, 1).Value) ' Get the min Planned Start Date for this ASR
In english: Look in the Planned_Start_Date named range, and more specifically a certain subset of that range, and return the minimum date found.
I've tried several ways, none of which will compile. I realize the above code is way off; I include it to show how lost I really am!
Slightly off-topic: I'm fairly new to VBA, and am expected to create executive dashboards with the data. I've purchased Excel 2003 Power Programming with VBA and Excel Charts (both Walkenbach); Excel Programming 2nd Edition; Excel Edition of Using Visual Basic for Applications; and Dashboard Reporting with Excel, Kyd. These are all useful books, but I think they expect me to have a better understanding than I have.
I need a piece of VBA code to assign to an Excel form that determines the maximum value of a subset of one column whose cognate rows in an adjoining column satisfy a particular value.
View 2 Replies View RelatedI have a huge list of data with numbers in cells. each number also has a corresponding percentage in the next cell over. In some cases, some of the numbers appear twice with differentiating percentages in the cells. What I am trying to do is automatically highlight and delete the duplicate number with the lower of the two percentages. See the below example.
2069091%
2069088%
As per above, I would want to delete the duplicate number with the 88% since it is the lower of the two percentages of that number. I have almost 2000 cells and have been going through manually deleting the lower of the two percentages for the duplicate numbers.
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
So what I have is a large list of items, and each item has the price of the product, and a product type.
What I want to do is find the median price for each specific product type, using a formula, without having to sort or filter my large list of products.
I will do my best to explain but just in case I have attached a worksheet to make things easier. I have a list of a few thousand products with data on each product. When I run through a series of cuts, I get a subset list and want to be able to calculated a weighted average by somehow saying to do a weighted average (and/or count, and/or average, etc.) on the characteristics of only the products in the subset.
View 9 Replies View RelatedI 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.
A B C
1 Fruit Oranges
2 Veg Lettuce
3
Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts
Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Radishes
Almonds
Peanuts