Altering Macro To Read On Another Sheet?
Mar 26, 2013
I have come across another stumbling block with VBA (new on VBA and slowly taking baby steps). I have a macro that reads and deletes all duplicates on the files i work on. However I need this to read and do the job on 2 sheets at the sametime (sheet 2 and sheet 3). Below is the macro which does work fine on a different workbook. How would I integrate this into my new sheet to do 2 jobs at once?
VB:
Sub delduprows()
Dim m As Integer, j As Integer
Dim n As Long, i As Long
[Code]....
View 1 Replies
ADVERTISEMENT
Sep 4, 2007
In cell AD17, I have a formula that returns the number of days or draws(Skips), since the number in cell AD2 has been drawn. The following cells AD18.......... returns the next skip and continues untill all the hits( AD3), are accounted for. My question, Can the formulas be altered to read a "
View 9 Replies
View Related
Apr 17, 2007
I have a workbook with four worksheets. Sheet1 is the master. On sheet one there is information in a 10x10 table. The first column of the table contains a status of "No, Maybe, Yes." A record will start with a status of No and move to Maybe and then yes. What I want to do is be able to dynamically fill sheet2-sheet4 with information from the master. If the status is "Yes" move that row to sheet2. If the status is "no" move that row to sheet 3. Etc. Further complication that is depending on status, I want specific columns from the master, not necessarily all of them.
View 2 Replies
View Related
Jan 22, 2010
I'm working with a workbook that has about 75 sheets. I need a macro that will copy the info in a certain couple of ranges from one sheet to the same ranges in another. This would not be a problem if it was always the same two sheets. But I need the user to be able to specify which sheet he wants to copy from and which sheet he wants to copy to.
The ranges on the sheets will always remain the same, just the sheet name needs to change. I created drop down boxes for the user to choose the copy from and copy to sheets, but I don't know how to insert that into the VBA code. The way I've designed it, the macro needs to read the result from the user that is on sheet "Index" in cell H5 to copy from and N5 to copy to. The result in those cells is the actual sheet name.
Here's the simple part of the code so far:
Sheets("MM Ades 64z").Select
Range("B8:B59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("B8:B59").Select
ActiveSheet.Paste
Sheets("MM Ades 64z").Select
Range("D8:D59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("D8:D59").Select
ActiveSheet.Paste
Sheets("MM Ades 64z").Select
Range("J8:O59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("J8:O59").Select
ActiveSheet.Paste
Sheets("TMMC").Select
Range("M1").Select
End Sub
View 9 Replies
View Related
May 31, 2007
Is/how possible to read entire ss into a variant "off-scree" without showing the user? I can open the file, I just dont know how to not show the file, select the entire sheet and read the values. I have seen the below but dont know how modify it as needed. Also is there a way to do this dynamically with variable length rows/columns?
Dim vaData As Variant
Dim lRow Long
Dim lCol As Long
vaData=ActiveSheet. Range("A1:B10").Value
from Professional Excel Development S.Bullen et.al. pp614
View 7 Replies
View Related
Feb 11, 2013
I am trying to read a value in a cell to post in another sheet.
Once the value has been copied, end. However, if the cell to the right of the copied has a value, then that value should be copied instead.
If the cell is blank, then the information should not be copied.
Basically I have a string of data from left to right being populated, with the most current value being copied to a differnt sheet.
View 7 Replies
View Related
Dec 8, 2009
I have two sheets, the first is "Form" & the second is "Tracking". The "Form" sheet contains one button that, when clicked, brings up a userform. The userform has 11 checkboxes & two buttons ("View Next" & "View Previous"). The "Tracking" sheet has 11 columns (A - K), which will contain either "True" or "False".
I need the checkboxes in the userform to pull the values from the "Tracking" sheet. For example, the "Tracking" sheet has the following information listed: ....
View 8 Replies
View Related
Jan 25, 2007
I am trying to limit the amount of stuff I write to a sheet, and I figured I can just pass variables around inside of a routine with out having to write each step to a sheet like I am doing now.
Below is the formula showing Z2 as where parsed data is
CVersionFormula = Evaluate("Z2 * 10 ^ (4 - Int(Log(Z2)))")
and here it is, with me trying to plug in a variable rather than read it from the data sheet. The variables for ease are read from the data sheet. In this example I would read in Z1 and assign it "CVersion", then the formula then takes CVersion (a 4 digit number) and makes it a 5 digit number..
CurrVersionE = Evaluate("CVersion * 10 ^ (4 - Int(Log(CVersion)))")
But I don't think I have the variables in there right.
View 9 Replies
View Related
Jan 4, 2007
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
View 2 Replies
View Related
May 21, 2013
I have a very large workbook, too big to cut down and upload . In it I have some code which selects a graph and sets the data on the graph to an arbitary number, Unfortunately, I get a "Method 'Xvalues' of object 'Series' failed" error when I run it (although not every time). The relevant part of the code is below. Prior to the code below, different sheets are selected and variables moved around, then this part of the code hides the other sheets leaving the calculation page visible.
The graph is on a log-log scale, but I don't think that is particularly important. The data series are named as various things (e.g. baseline, reference, measurement) though but there are only ever two on the graph (I just rename them later on and change the data).
VB:
'open calculation page and hide survey results and archive pages
With Worksheets("Calculation Page")
.Visible = True: .Select
[Code]....
View 9 Replies
View Related
Mar 31, 2014
I need to modify the following formula,
=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)
[Code].....
View 10 Replies
View Related
Jun 11, 2009
I was wondering if there is a way to set a macro to run on an event only if the workbook is read-only. The macro I have set up right now reformats the toolbars to a custom user friendly one, but I want to be able to open the document with a password and not have the toolbars rearange on me.
View 2 Replies
View Related
May 30, 2007
I have been using various bits of information from all sorts of threads and made a somewhat nifty little Macro program for the company i work for.
The following threads may have my answer in them, but I can't see it or figure out how it could be restructured to my needs.
Disable Read-only Message Box
looks as if the file is set as readonly then to close it without saving
[url]
[url]
contains a msgbox I would like to add in, specifying that the file is read only therefore to abort current macro for particular file
concerns very much the problem I am having.
Basically what it boils down to is,
My Program Manager runs a daily report, then uses my program to update up to 7 different workbooks. Each having their own Sub program.
Some of the Supervisors or leads gets in to early and thus makes the file as a Read-Only, which of course stops my program and can not continue.
What I would like to learn is.. (from the countless threads I have looked through, I can't seem to find the answer)
Bring up a msgbox, informing the PM that a specified file is already open and therefore this particular process has been aborted.
Then it will move on to the next Sub, and so forth...
I want to thank everyone for their help directly or indirectly, (been reading threads for about 9 months now) and any future help as well.
And if it's not to difficult, could it be close to layman terms? I try to understand alot of these codes and I get lost with some of the wording. I am just pratically piecing pieces together with my "eyes closed" and somehow come out with a working VB codes.
View 9 Replies
View Related
Jun 12, 2014
I have a spinbutton on a userform. Here is the section of code for SpinUp:
[Code]....
Normally, I wouldn't hard code an address in like this, but I was given a last minute project to present on Monday, so it's down,dirty, and quick. Anyways, the adjacent cell "O7" has a formula referencing another page. When I spin the spinner it activates the correct cell (and continues to do so as long as I press the button), but here's the wierdness: cell "07" formula disappears, leaving the existing value in it. It's like it copy pastespecial values.
I've stepped through the code and it's not referencing any other procedure. It happens on this line:
[Code] ....
View 1 Replies
View Related
Jul 18, 2006
how does one alter a variable without referring to it directly? Eg. One has a combo box (CboPNum) with values from 1 to 20 and textboxes (TxtN1 to TxtN20). Upon selecting a value from the CboPNum dropdown, the background of the corresponding TextBox should be changed. I’ve tried to create something that joins the string “TxtN” and the CboPNum value:
ThisTextBox = “TxtN” & CboPNum.Value
ThisTextBox.Background = Black
But this quite correctly gives the error “Object required”… What do you guys recommend?
View 3 Replies
View Related
Apr 5, 2013
I want to design a macro to check if the cell has a comment in it. If so, the macro should read the comment and store it in a variable.
View 2 Replies
View Related
Jan 21, 2009
below code
Sub Collect_OS_Data()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook, wbCur As Workbook
fldPath = "Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESSOngoing - Suspense BUSINESS - Activity Reports"
Set wbCur = ActiveWorkbook
ActiveSheet.Name = "Raw Data"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath)
On Error GoTo ErrHandler
For Each fil In fld.Files
This opens all spreadsheets within a folder and takes the data i require out of each one and pastes to a new workbook, however the files are password protected. Can you add into the code to open each spreadsheet as a readonly copy and paste data and then close without a save prompt or denying the save prompt ?
View 9 Replies
View Related
Jul 14, 2009
how do i set the macro to read a range without limitation?
currently i have a recording macro that only reads to P126 - if i end there data in C127:N127 I would like the macro to recognized that there is new data and update to P127 with the new calculation....
View 9 Replies
View Related
Sep 30, 2009
I have the following code which reads line 54 (row 54) in a spreadsheet and calculates the following using data in subsequent columns. Sometimes the data I need isn't always in row 54, I would rather have the macro read the data in Column A[AB] that is on line 54...sometimes on 53.
The date is Sep-09. How can I make it read this rather than AB54. I think I just need changes @ the top (ie Dim rng as Range, Set rng = [AB54]).
Sub Profile_info2()
Dim rng As Range
Set rng = [AB54]
rng.FormulaR1C1 = "=RC[-12]+R[-1]C[-12]+R[-2]C[-12]"
With rng.Offset(0, 2)
.FormulaR1C1 = "=((RC[-28]+R[-1]C[-28]+R[-2]C[-28])*4)/((RC[-25]+R[-1]C[-25]+R[-2]C[-25])/3)"
.NumberFormat = "0.0"
End With
With rng.Offset(0, 3)
.FormulaR1C1 = _
"=((RC[-15]-RC[-29])+(R[-1]C[-15]-R[-1]C[-29])+(R[-2]C[-15]-R[-2]C[-29]))/(RC[-15]+R[-1]C[-15]+R[-2]C[-15])"
.Style = "Percent"
.NumberFormat = "0.0%"
End With
rng.Offset(0, 4).FormulaR1C1 = "=RC[-8]"
With rng.Offset(0, 5)
.FormulaR1C1 = "=(((RC[-17]-RC[-31])+(R[-1]C[-17]-R[-1]C[-31])+(R[-2]C[-17]-R[-2]C[-31]))*4)/((RC[-28]+R[-1]C[-28]+R[-2]C[-28])/3)"
.Style = "Percent"
End With
End Sub
View 9 Replies
View Related
Apr 3, 2008
i have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.
what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed
i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet
View 12 Replies
View Related
Dec 28, 2012
I have a column ful with numbers and would like to add an h (hour) after every number. I found that it is possible for valuta (dollar sign). Is there a way for random symbols that are not in the valuta list?
View 3 Replies
View Related
Nov 12, 2008
I have two tables of information. The first is a matrix with some distances. The second is a table of distribution. Ive been trying this for a long time now with limited success using a truth table but i've realised the only way to do this is in code. Ive got limited experience with this so please point me in the right direction.
Ive written some steps explaining what each table does here -
Step 1Check for lowest value B4:F4 in Table 1 (in example is 10)
Step 2Check corresponding column destination available capacity in table 2 (Example 500)
Step 3Distribute as much as possible from source in table 2 (500)
Step 4Reduce value in capacity line by value taken from source
Step 5If some source remains move back to table 1 and find next nearest column Dest
Step 6Repeat step 2 until all source is gone in row
Step 7 Check for lowest value B5:F5 in Table 1
if run out of capacity at all sites stop code
etc
until Table 1 column B is empty
Ive posted a spreadsheet with some before and after tables in it aswell. Its very small and formatted o its easy to see whats happening.
View 14 Replies
View Related
Jul 6, 2007
I have a master spreadsheet containing 4 relevant fields, 'Manufacturer', 'Product', 'Version' and 'Type'. This contains all possible variations of 'manufacturer', 'product' and 'version' that can occur in the organisation.
The type field shows how the particular item is going to be processed, either 'automated', 'manual' or 'non'
This is a computer generated spreadsheet, apart from the 'Type' field which we've entered for each one individually, as are the user spreadsheets which dont yet have the 'type' field.
This is a large list of around 7000 items
I then have a multiple spreadsheets (one for each user, totaling around 1200) that has a list of a couple of hundred items that are relevant to the user which contains 'manufacturer', 'product' and 'version'.
i need to sort these out quickly by comparing the sheet to the master spreadsheet so where 'manufacturer', 'product' and 'version' match up with the masters record it automatically removes the the 'non' type of item from the user sheet and placing either 'automated' or 'manual' in a new 'type' filed where appropriate
View 3 Replies
View Related
Feb 16, 2013
Does Excel can be used to read data from HDF files? Specifically, what I would like to do is this: I have an Excel worksheet with some latitude data on Column A and longitude data on Column B. I would like to open a HDF file, which contains many sets of data, out of which 2 are the latitude/longitude data, and a third set contains the data that I want to extract. I will use the latitude data in Column A to search through the first set in the HDF, which will return me the row number. I will then use the longitude data in Column B to search through the second set to return the column number. With these row and column numbers, I will then extract the corresponding data in the third set, and write it to Column C in the worksheet.
how to write a simple VBA code for it? Or point me to some relevant information?
View 1 Replies
View Related
Jul 21, 2009
I have a pair of macros that together close and reopen a workbook when the user clicks on a button labelled 'Refresh'. The reason for the macros is that any one of multiple users may have the workbook open for editing while the others have it open read-only, and those who have it open read-only will want to refresh it periodically to make sure they're viewing the most up-to-date version.
Now, here's the hitch. If I have the workbook open read-only and refresh it while no-one else has it open for editing, it doesn't reopen read-only (it reopens for editing). But I don't want it to reopen for editing (I want it to reopen read-only). Is there anything I can add to the macros to make the workbook reopen read-only, or is there a different pair of macros I can use? Here's what I have at the moment:
Macro 1
Sub CloseMe()
'
Application.OnTime Now, "OpenMe"
ThisWorkbook.Close SaveChanges:=False
'
End Sub
Macro 2
Sub OpenMe()
'
End Sub
View 9 Replies
View Related
Mar 14, 2009
I've written a script to read all the values in an excel worksheet in to an array. The cell types differ from General or Text to Custom - HH:MM When I look through the array the script has returned the correct value for most but for the cells that are of a custom type an "incorrect" value has been returned. For example a cell of type Custom HH:MM which is 10:23:00 in the worksheet appears as 0.432638888888889 in the array. Is there a way to get the script to return the correct value?
Below is an extract of the code I've used
If objWorksheet.Cells(i, "D") <> "" Then
If objWorksheet.Cells(i, "F") <> "" Then
arrCULDEV = Split(objWorksheet.Cells(i, "F"), ":")
Redim Preserve arrSheetInfo(x)
arrSheetInfo(x) = arrCULDEV(0) & "," & arrCULDEV(1)
x = x + 1
i = i + 1
Else
i = i + 1
End If
End If
View 2 Replies
View Related
Mar 31, 2009
--------------------------------------------------------------------------------
Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula:
=LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32)
This allows me to type 101-16 in and excel recognizes this as 101.5
My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4).
I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?
View 11 Replies
View Related
Feb 8, 2012
At work, I have a workbook with multiple tabs that contain lists. Each tab has a corresponding Pivot Table.
There is a business requirement that a user can specify a name, which filters the data. For the sake of simplicity use this for an example
Column Headers: First_Name ; Last_Name; Age
Row 1: Johnny; Bravo; 29
Row 2: Shane; Falco; 34
Row 3; Bobby; Shane; 15
The user specifies "Shane" as the filter in another pre-determined Cell (D1). Using advanced criteria, I need to find all rows that have Shane in either First_Name or Last_Name. The only way I know how to do that is inserting two rows and adding criteria:
First_Name ; Last_Name; Age
=D1; ;
;=D1;
Johnny; Bravo; 29
Shane; Falco; 34
Bobby; Shane; 15
With Criteria Range = "A1:B3"
This is problematic because my Pivot tables now include 5 rows of data.
View 3 Replies
View Related
Nov 17, 2009
Convert the first letter of a string to a capital without altering the rest of the strings format.
example: the activecell contains the string a1st (lowercase a, superscript 1st)
how can i achieve this in VBA?
Additionally, is it also possible to convert all of a string to capitals except the superscript characters
View 9 Replies
View Related
Apr 10, 2014
I have a macro that copies time cells from another sheet and puts them in cells in another sheet, I also have another macro that takes 30 mins away from that time if a certain condition is reached but it wont work unless i retype over the copied cell.
View 14 Replies
View Related
