Skip To Next Macro, If Read Only Is Available
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
ADVERTISEMENT
Feb 13, 2014
I am calling sub B from sub A and want to skip some lines in sub B (only when it is called from sub A).
View 3 Replies
View Related
Jun 27, 2007
Sub BlankGcolumn()
Dim cl As Range
For Each cl In Range("B8", Range("B15").End(xlUp))
If cl.Value = "" Then
cl.Offset(0, 0) = cl.Offset(0, -1) & " " & cl.Offset(1, -1)
End If
Next cl
End Sub
in the "Next cl" part, i want it to skip the next cell and do the one after it.
Ive tired: ....
View 9 Replies
View Related
May 19, 2014
I Have the following macro conducting web queries. As the list of websites is spread out I need to get the macro to skip over blanks?
VB:
Sub Top5Investors()
'
' Top5investors Macro
'
'
Dim Erw, Frw, Lrw
Frw = 1
Lrw = Range("A" & Rows.Count).End(xlUp).Row
For Erw = Frw To Lrw
[Code]...
View 1 Replies
View Related
Apr 13, 2009
macro to skip delete sheet question. i have this as part of my code
View 2 Replies
View Related
May 16, 2012
I have create excel VBA to put data from excel sheet to Server database through network. I create the VBA on the "Workbook_beforeprint" so every time the user print out the sheet, the code will run.
Problem occur when there is no network and excel application become not responding.
I want to make VBA code so that every time excel application become not responding because no network, this code will bypass the existing VBA and resume to print.
View 4 Replies
View Related
Apr 24, 2013
I have a spreadsheet with a lot of account numbers. I want my Macro to highlight the row A:C if certain accounts are found in the spreadsheet.
For Example:
Account numbers "10998-0000" and "18999-0000" are allowed to be in the spreadsheet so don't want them highlighted. But Account "10110-0000" should be highlighted.
I know the Macro will be big but I was hopping something like:
If ActiveCell is not accounts .... I would then list all my account numbers... Then
and at the end a loop until ActiveCell is Empty.
I have this so far.
Range("A12").Select
MyColumn = 1
i = 12
Range("A" & i).Select
Do While ActiveCell.Value ""
[Code] ........
View 8 Replies
View Related
Apr 18, 2013
I want to a macro to start of with a Find method Eg Account..something like this
Cells.Find(What:="Account").Activate
Account could be in any row or may not be in the document.
My problem is when it is not in the workbook my macro will come up error. how to recode my macro to fix this.
Dim Finder as string
Set Finder = Cells.Find(What:="Account",...............
'Problem here
'
If Finder is not found then skip till next part of macro.
If it is then just select the Cell and I can work of that.
Also Can I do a Find formula to Find Either 'Account' Or 'Debt' Or Asset'?? Just want to know save me copy and pasting it down to change a name.
View 1 Replies
View Related
Jul 23, 2013
I need to add something to my macro to delete a sheet if it doesn't have more than 40 rows of data. If it has more than that, it needs to do a whole bunch of stuff to it. I've already got the whole bunch of stuff written, I'm just not sure how to do the if function. I took a stab at it, but it wants the count to be an object. I am not very good at writing code.
Code:
Dim count As Integer
Set count = "COUNT('33M & 33RUL'!A:A)"
If waffle Is "
View 1 Replies
View Related
Jul 11, 2007
I have been using this macro, which works perfect:
Sub Signflip()
Dim cell As Range
For Each cell In Selection
If IsNumeric(cell.Value) Then cell.Value = -cell.Value
Next cell
End Sub
It allows me to select cells and flip from neg to pos, pos to neg, and it won't error out on text cells. Great little macro.
However, I don't like how it puts a zero in blank cells. It slows down the macro when I select large areas. Is there a way to make this macro work the same way but skip over empty or blank cells and not enter a zero.
View 9 Replies
View Related
Sep 19, 2007
I need my users to always enable macros when they open my workbook. Is there a way I can tell excel to enable macros and skip this initial question for users?
View 4 Replies
View Related
Jan 10, 2012
Can a macro make a workbook everytime you copy, it will paste special formulas only and skip blank rows? And can I still let me select the range manually? I would like to use this to link workbooks.
View 2 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
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
Mar 7, 2013
I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A
2. Skip the next cell/row (this is the company)
3. Select all the cells beneath the company cell, until it reaches the blank cell underneath
4. Copy, transpose these cells next to the Company cell (transpose in column B)
5. Delete the contents in row A that were just transposed
6. Find the next cell with data (company)
7. Repeat Steps 2-6
My recorded Macro:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select
[code]....
View 5 Replies
View Related
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
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
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
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
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
Feb 5, 2012
I have three workbooks that contain various types of information. I have an Overtime workbook that lists employees and calculates the straight time and overtime worked for the day. This is the main log that information will be pulled from.
I need writing a macro that will copy over information to an Absent Log workbook and a Production Model workbook.
The Absent Log workbook:
This workbook contains worksheets from each month. I need excel to do a lookup of the name in the Overtime workbook and copy the information from the "OT" column but if that column is empty I need it to pull the information from the "Personal DT" column.
The Production Model workbook:
I need this workbook to automatically pull the total straight hours and overtime hours to the production model for the correct day. This will also pull from multiple sheets.
I am going to be writing this in Excel 2010 but it will be primarily used in Excel 2003.
I uploaded the workbooks to filefactory.
OT Workbook: [URL] ......
Absent "Log" workbook: [URL] .....
Production Model: [URL] .....
View 1 Replies
View Related
Apr 10, 2014
I need to get a code that will just read the text in a cell that contains text and numbers example abc123 I want it to only read the abc as the numbers can change and cant write them all into my macro all the time.
View 9 Replies
View Related
May 17, 2006
I have this in my Macro:............
My Macro runs fine when there is something in Cell S2 but if there is not then I get an error on "Cells(sumrownum, 19).Value = "Total" 'Column S". What I want to do is put a if then type of statement that if S2 is empty the it will skip down to "Columns("W:X").Select" and keep going with the rest of my macro.
View 6 Replies
View Related
May 1, 2014
Because I have a formula that updates the bottom of a table I have to put Total row at the top, the first row.
The propblem is that the filter obviously includes this row. Is there a way to tell the filter to skip over the first row?
View 1 Replies
View Related
Jul 15, 2013
I have two sheets open. On the first sheet i have a drop down list of dates from which I select. That list of dates is on another sheet. I want to be able to select a date from the drop down list, then when I go to the second sheet, excel skips down to the row that contains that date. I can also make it values if it is easier.
View 2 Replies
View Related