count number of sheets in workbook
Is there a way using VBA to count and report the total number o worksheets in another closed workbook. I don't mind briefly opening and closing the workbook to perform th function. I am attaching the workbook in an email and wanted to includ the total number of sheets in the body of the email. Thanks -- Message posted from http://www.ExcelForum.com
Axcell.12i41v
excel.misc
78881 articles. 
5 followers.
4 Replies
328 Views
Hi
try the following
Option Explicit
sub foo()
Dim wbk As Workbook
Dim count_wks
Dim path As String
Dim wbk_filename As String
'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\" 'change this
log_filename = "test.xls" 'change this
'check if other workbook is open / if not open it
On Error Resume Next
Set wbk = Workbooks(wbk_filename)
On Error GoTo 0
If wbk Is Nothing Then
Workbooks.Open filename:=path & wbk_filename
Set wbk = Workbooks(wbk_filename)
End If
msgbox wbk.worksheets.count
' close
Application.DisplayAlerts = False
wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
--
Regards
Frank Kabel
Frankfurt, Germany
> Is there a way using VBA to count and report the total number of
> worksheets in another closed workbook.
>
> I don't mind briefly opening and closing the workbook to perform the
> function. I am attaching the workbook in an email and wanted to
> include the total number of sheets in the body of the email.
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
frank.kabelAxcell,
Here is a little fuynction that does it without opening the workbook. Just
call it with the filename as the argument, like
Debug.Print NumSheets("D:\Development\hospital db\TBIcontacts.mdb")
It returns -1 if there is an error, such as non-existant file.
Function NumSheets(FileName As String) As Long
Dim oConn As Object
Dim oCat As Object
Dim oTable As Object
Dim sConnString As String
Dim sFileName As String
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";"
Set oConn = CreateObject("ADODB.Connection")
On Error Resume Next
oConn.Open sConnString
If Err.Number <> 0 Then
NumSheets = -1
Exit Function
Else
On Error GoTo 0
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn
NumSheets = oCat.tables.Count
End If
oConn.Close
Set oCat = Nothing
Set oConn = Nothing
End Function
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Axcell >" <<Axcell.12i41v@excelforum-nospam.com> wrote in message
news:Axcell.12i41v@excelforum-nospam.com...
> Is there a way using VBA to count and report the total number of
> worksheets in another closed workbook.
>
> I don't mind briefly opening and closing the workbook to perform the
> function. I am attaching the workbook in an email and wanted to include
> the total number of sheets in the body of the email.
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
bob.phillips1Little faux-pas in there, it used an Access connection string.
Here is a corrected version that access Excel spreadsheets. Otherwise it is
the same.
I have also included an ADO version just to show the difference.
'---------------------------------------------------------------------------
---------
' ADOX Version
'---------------------------------------------------------------------------
---------
Function NumSheetsADOX(FileName As String) As Long
Dim oConn As Object
Dim oCat As Object
Dim oTable As Object
Dim sConnString As String
Dim sFileName As String
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"
Set oConn = CreateObject("ADODB.Connection")
On Error Resume Next
oConn.Open sConnString
If Err.Number <> 0 Then
NumSheetsADOX = -1
Exit Function
Else
On Error GoTo 0
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn
NumSheetsADOX = oCat.tables.Count
End If
oConn.Close
Set oCat = Nothing
Set oConn = Nothing
End Function
'---------------------------------------------------------------------------
---------
' ADO Version
'---------------------------------------------------------------------------
---------
Function NumSheetsADO(FileName As String) As Long
Dim oConn As Object
Dim oRS As Object
Dim sConnString As String
Dim sFileName As String
Dim cTables As Long
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"
Set oConn = CreateObject("ADODB.Connection")
On Error Resume Next
oConn.Open sConnString
If Err.Number <> 0 Then
NumSheetsADO = -1
Exit Function
Else
On Error GoTo 0
Set oRS = oConn.OpenSchema(20, _
Array(Empty, Empty, Empty, "Table"))
Do While Not oRS.EOF
cTables = cTables + 1
oRS.MoveNext
Loop
End If
NumSheetsADO = cTables
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
End Function
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:usboMRIAEHA.808@TK2MSFTNGP12.phx.gbl...
> Axcell,
>
> Here is a little fuynction that does it without opening the workbook.
Just
> call it with the filename as the argument, like
>
> Debug.Print NumSheets("D:\Development\hospital db\TBIcontacts.mdb")
>
> It returns -1 if there is an error, such as non-existant file.
>
>
Thanks guys, I went with Frank's suggestion and it works great. Bob, I appreciate the ADO code. It is still a little intimidating to m right now. Eventually, when I clean up the code when I finish m project, I will be interested in your approach. Thanks for the replies -- Message posted from http://www.ExcelForum.com
Axcell.12ic2z
Similar Artilces:
HELP: Sheet embedded comboboxes
Hi all, I am assisting somebody with their excel project and they have a sheet with comboboxes in it. When a value is selected, it populates cells underneath, however when the data is freshly loaded, the cell underneath is given the value but the combobox does not update to this value. I have tried to click on the box to edit it, however no matter how many times i click i cannot seem to get it to recognise i've clicked. In other words, the resizing box does not come up and i have no right-click menu. All i can do is select from the drop down list without any editing of the proper...
Excel: Combine contents from identical cells on multiple sheets.
How do you combine contents from indentical cells on multiple sheets into one cell on a different sheet? I am working on a work schedule for partime workers who will each have a sheet assigned to them to enter their name in a cell next to the days they can work. I want their names to then appear in a master calendar in list form under the date they can work for that month. In the master sheet, try a formula like: ='Sheet One'!A1 & " " & Sheet2!A1 & " " & 'Last Sheet'!A1 I've shown [Sheet One] and [Last Sheet] as being betw...
Excel
Can anyone tell me how to convert a date to the number of days ago that date actually was? I need to find the number of days ago. Thanks in advance! Michael the function Today() returns you a number when the cell is formatted 'General' or 'Number' -today sept 29 you get 37893-. Apply the same to your date and deduct one from the other Generally speaking, why don't you read 'About dates and date systems' in Excel Help Emm >-----Original Message----- >Can anyone tell me how to convert a date to the number of >days ago that date actually was? I need to...
How I keep original dates for check numbers?
I'm using Money 2003. When I enter a check number on a particular date, it remains that way in Money until it clears my bank; then when I download the history file from the bank it changes the date to when the check came in. I would rather keep the original date, but can't find anything in Money's help or other sections to tell me how to do this. In microsoft.public.money, Neil Harrington wrote: >I'm using Money 2003. When I enter a check number on a particular date, it >remains that way in Money until it clears my bank; then when I download the >history fil...
Still having a prob counting the blocks of 1's
I am still having problems counting blocks of 1's. A few are returning the correct values, but others are seem to be adding 1 from the previous column. The formula I'm using is.. =SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) =SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1)) =SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1)) =SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1)) =SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on. The first formula is entered in D1 =SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on. Col A B C D E F G H I J K L ...
Counting items based on multiple conditions
Attached spreadsheet explains the problem What I need to do would be possible if COUNTIF could be based on mor than 1 conditio Attachment filename: query 1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=51221 -- Message posted from http://www.ExcelForum.com Hi try something like =SUMPRODUCT((A1:A100="cond_1")*(B1:B1000="cond_2")) or as alternative syntax =SUMPRODUCT(--(A1:A100="cond_1"),--(B1:B1000="cond_2")) -- Regards Frank Kabel Frankfurt, Germany > Attached spreadsheet explains the...
Limiting Number of Characters barcode scanner can read
I am working w/ a client to get the Microsoft RMS software going at his store. My problem when we exported the product data from his old system to new it cut off the last number of the barcode for each product. Can I make my scanner only read 11 characters in the Store Operations Manager? You should be able to program your scanner to ignore the check digit, which means it only reads the first 11 digits. You will have to look at your scanner documentation for the specifics The manual (usually on CD) should have some bar codes you can print out and scan to make the programming change to ...
Can't get Bar chart to use my numbers
I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 1...
data fill sheet 2 from sheet 1 based on criteria
I am trying to Data fill sheet 2 based on criteria match from sheet 1. I have no knowledge of VBA and/or macros and very limited knowledg working with arrays. see sample data below sheet 1 note sheet 1 contains approx 2500 rows column A column B 2295.60 sales.sam 17496 jobs.sue 14.45 trade.john 1775 trade.sam Sheet 2 should look in column B for .sam or .john and when foun return results to sheet 2 columns A and B sheet 2 contain variable number of rows depending on occurrences o sheet 1 Column A column B...
sotring worksheets in a workbook
I have a workbook with 4 worksheets. The “name” column is linked from SHEET1 to the “name” column in SHEET2, SHEET3 and SHEET4. I need to be able to change a name in SHEET1 and sort all of the data colunms in all 4 sheets by “name.” Any ideas on how to do this? here are a couple to try Sub sortworksheets() Dim Cnt As Integer Dim n As Integer Dim m As Integer Dim WS As Worksheet Set WS = ActiveSheet Cnt = ActiveWorkbook.Worksheets.COUNT For m = 1 To Cnt For n = m To Cnt If UCase(Worksheets(n).Name) < UCase(Worksheets(m).Name) Then Worksheets(n).Move Before:=Wor...
How to ensure workbook is saved before exiting Excel?
Need to find "fool-proof" method of ensuring Users save Excel workbooks before exiting program. This has been a huge problem in the past. Can this be done with a macro? Kathy This macro, placed in the ThisWorkbook module, will fire whenever the user issues a close command (whenever the user clicks on "Close"). The macro checks if the workbook has been saved, and will save it if not. The workbook will then close. Come back if you need more. Otto Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Saved = False Then ThisWor...
primary key collisions due to auto reseeding of auto number field.
has anyone come across this problem in Access2003 Can you be more specific, Simon? Is the AutoNumber being seeded intentionally? Or is it one of the causes listed at the top of this article: Fixing AutoNumbers when Access assigns negatives or duplicates at: http://allenbrowne.com/ser-40.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "simcon" <simcon@discussions.microsoft.com> wrote in message news:0AFF03CF-F2B6-4312-ABA3-9DF036466FBD@m...
Backing Up Workbooks
Currently I have a macro button to backup my workbooks. I also have a save button. I would like to use only the save button but on a specific day of the week say Tuesday I would the macro also to do a backup. Can someone tell me the code to do this. I use the following to save the file. ActiveWorkbook.Save ActiveWorkbook.Close I use the following to backup the workbook. ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Splash").Select ActiveWorkbook.SaveAs Filename:="C:\Posto Pote\Backup\Posto Pote.xls", _ FileFormat:=xlExcel8, Password:="&...
Counting Highlighted Cells- Fastest way?
Currently I am working on web reports in which I have highlighted cells in several different worksheets. I then have a summary page which counts the Sum of Column J, COUNTA(J2:J1000) which counts the number of data entries in the range, and then i have to manually filter column J by highlighted color(yellow). Not all rows have a highlighted column and so I currently just Filter by Cell Color and is the Count function at the bottom of the screen; however this is time consuming and I am trying to automate this report as much as I can, but I am stuck on this. I am using Excel 2007 and have so...
number in cell changes
When I enter the Number 1 in a cell it changes to an 8. All other numbers are ok. Just started having this problem. Is this a hardware or software problem or do I have a setting wrong. I re-installed office and it didn't help. Doesn't occur in any other application. Most likely you have autocorrect (it might be a prank) tools>autocorrect options set to do the change open autocorrect, type 1 in the replace box and if it is there it will come up, then select delete Another option (not likely) would be an event macro -- Regards, Peo Sjoblom (No private emails please) "w...
Formating a decimal number
Hello I have a 2003 EXCEL. I want to average two decimal number number like 0.234 and 0.225. It is impossible with the formating protocol. Can any body help me to do this? With a formula =AVERAGE(A1,A2) what happens when you custom format the cell to 0.000 OR Select 'Number' format and allow 3 decimals.. -- Jacob (MVP - Excel) "JohnJJ" wrote: > Hello > I have a 2003 EXCEL. > I want to average two decimal number number like 0.234 and 0.225. It is > impossible with the formating protocol. Can any body help me to do this? I think you&...
Sheet Protection / Groupings
Using Excel 2003 Is there any way to protect a sheet and allow Outling to work? Paul Smith If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Ea...
Run-time error '1004' Method 'Sheets' of object'_Global' failed
I'm new to excel and don't really know what I'm doing. When the below macro is executed (through changing a combo box) the macro appears to run fine but when I close the workbook the above Run-time error message dialog box comes up, can anyone help? Sheets("dec").Range("A1:CO111").Cop Sheets("ROTA VIEW").Selec Range("B4").Selec Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= False, Transpose:=Fals Range("B3").Selec Thanks Richard, Add the line Application.CutCopyMode = false...
Incrementing packing slip numbers
We have a customer who partially fulfills, ships and invoices orders. They would like the packing slip to increment with each item shipped/invoiced. Is there a way to do this? Standard GP seems to reuse the same packing slip number for each item from the order regardless of when shipped. Thanks.. So basically you want all pay codes for an employee regardless of whether they are going to be paid or not in the next pay period. If this is the case, the only way to build this is by using SmartList Builder. 1) Open SmartList Builder 2) Enter an ID for your SmartList, say EMPPAYCODE 3) E...
How to close active Workbook without closing other open workbooks?
Not sure how this developed. I have Excel 10(?) from Office XP. I have more than one Workbook opened and then want to close one Workbook and continue working with the other opened workbooks. But I close one Workbook and all the Workbooks close at once. Does anyone know how to separate them from all closing at once? Or, have I unknowingly changed something to my "Book1.xls"/original at some point? If so, how do I correct that? Many thanks! framingham1@hotmail.com If you have tools|options|view tab|windows in taskbar checked, then maybe you assumed that Excel and Word worked simi...
Workbook Windows
How can make multiple workbooks show up on my task bar instead of having to go to "Windows" and putting a check by the workbook I want active? Hi goto 'Tools - Options - General' and check 'Windows in Taskbar' -- Regards Frank Kabel Frankfurt, Germany "Shanta" <Shanta@discussions.microsoft.com> schrieb im Newsbeitrag news:C0C2F817-16CF-4761-880D-53A81A751061@microsoft.com... > How can make multiple workbooks show up on my task bar instead of having to > go to "Windows" and putting a check by the workbook I want active? ...
Wildcard Usage: I absolutely need this to count?/sum? two or more criteri
_*Number*_ _*Status*_ _*Date*_ N01-02 Draft 4-May-05 N01-02 Draft 4-May-05 N01-92 In Process 15-Oct-05 N01-88 Draft 15-May-05 N01-29 In Process 9-Jul-05 N01-32 In Process 9-Jul-05 N02-22 Draft 24-Aug-05 N02-27 Draft 24-Aug-05 N20-12 Assigned 3-Dec-05 N24-19 Draft 24-Aug-05 N33-13 Doc Written 4-May-07 N44-04 Cancelled 15-Oct-05 My header are Number, Status, and Date (columns A,B and C). The dcuments represent my rows (A2-A13). Since I ...
How to see full path name of open workbook?
Is there a simple way to see the full path name (folder and file name) of an open workbook? Hi Joe, =CELL("filename",A1) Ed Ferrero www.edferrero.com Where do you want to see this? If you display the Web Toolbar you will see path and name if the bar. On the Title Bar? Add this code to Thisworkbook module. Private Sub Workbook_Open() ActiveWindow.Caption = ActiveWorkbook.FullName End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.Caption = "" End Sub Gord Dibben MS Excel MVP On Wed, 17 Mar 2010 21:22:04 -0...
Moving rows to other sheets
I was wondering if anyone could be of assistance with my dilemma. I want to run a macro that does the following: Sheet 1 has records with column headers such as name, address, and date. If the date field is populated with a date, I want to take the entire row and move it to Sheet 2, then delete the source row in Sheet 1. However, if the date field is populated by a word such as "Dead Deal" I want to take the entire row and move it to Sheet 3, then delete the source row in Sheet 1. Thanks in advance. Edwin Assuming you are only looking for a way to distingush between the two re...
create a link between workbooks
I’d like to create a link between two different workbooks. please help Hi one way: - open both workbooks - in the target cell enter the equation sign '*=3D' - now select with your mose the other workbook and the=20 source cell - hit ENTER >-----Original Message----- >I=E2?Td like to create a link between two different=20 workbooks. please help >. > Hi What kind of link? Link to cell in another workbook - several ways: 1. Enter the formula directly into cell, like ='C:\My Documents\My Excel Files\[SourceFile.xls]SourceSheet'!A1 2. Open both files. Into target s...
Hi all, I am assisting somebody with their excel project and they have a sheet with comboboxes in it. When a value is selected, it populates cells underneath, however when the data is freshly loaded, the cell underneath is given the value but the combobox does not update to this value. I have tried to click on the box to edit it, however no matter how many times i click i cannot seem to get it to recognise i've clicked. In other words, the resizing box does not come up and i have no right-click menu. All i can do is select from the drop down list without any editing of the proper...
Excel: Combine contents from identical cells on multiple sheets.
How do you combine contents from indentical cells on multiple sheets into one cell on a different sheet? I am working on a work schedule for partime workers who will each have a sheet assigned to them to enter their name in a cell next to the days they can work. I want their names to then appear in a master calendar in list form under the date they can work for that month. In the master sheet, try a formula like: ='Sheet One'!A1 & " " & Sheet2!A1 & " " & 'Last Sheet'!A1 I've shown [Sheet One] and [Last Sheet] as being betw...
Excel
Can anyone tell me how to convert a date to the number of days ago that date actually was? I need to find the number of days ago. Thanks in advance! Michael the function Today() returns you a number when the cell is formatted 'General' or 'Number' -today sept 29 you get 37893-. Apply the same to your date and deduct one from the other Generally speaking, why don't you read 'About dates and date systems' in Excel Help Emm >-----Original Message----- >Can anyone tell me how to convert a date to the number of >days ago that date actually was? I need to...
How I keep original dates for check numbers?
I'm using Money 2003. When I enter a check number on a particular date, it remains that way in Money until it clears my bank; then when I download the history file from the bank it changes the date to when the check came in. I would rather keep the original date, but can't find anything in Money's help or other sections to tell me how to do this. In microsoft.public.money, Neil Harrington wrote: >I'm using Money 2003. When I enter a check number on a particular date, it >remains that way in Money until it clears my bank; then when I download the >history fil...
Still having a prob counting the blocks of 1's
I am still having problems counting blocks of 1's. A few are returning the correct values, but others are seem to be adding 1 from the previous column. The formula I'm using is.. =SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) =SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1)) =SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1)) =SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1)) =SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on. The first formula is entered in D1 =SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on. Col A B C D E F G H I J K L ...
Counting items based on multiple conditions
Attached spreadsheet explains the problem What I need to do would be possible if COUNTIF could be based on mor than 1 conditio Attachment filename: query 1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=51221 -- Message posted from http://www.ExcelForum.com Hi try something like =SUMPRODUCT((A1:A100="cond_1")*(B1:B1000="cond_2")) or as alternative syntax =SUMPRODUCT(--(A1:A100="cond_1"),--(B1:B1000="cond_2")) -- Regards Frank Kabel Frankfurt, Germany > Attached spreadsheet explains the...
Limiting Number of Characters barcode scanner can read
I am working w/ a client to get the Microsoft RMS software going at his store. My problem when we exported the product data from his old system to new it cut off the last number of the barcode for each product. Can I make my scanner only read 11 characters in the Store Operations Manager? You should be able to program your scanner to ignore the check digit, which means it only reads the first 11 digits. You will have to look at your scanner documentation for the specifics The manual (usually on CD) should have some bar codes you can print out and scan to make the programming change to ...
Can't get Bar chart to use my numbers
I have a set of numbers: Level Response $250,000 1 $187,500 1 $75,000 1 $50,000 3 $46,857 1 $30,000 3 $21,000 1 $20,000 1 $18,750 1 $18,000 1 $15,000 1 $10,000 2 In this case, I have 1 response at 250,000 and 3 reponses at 50,000 etc. What I am trying to do is chart this so that the Y axis is 0 - 250,000 (or 300,000) and the X axis is at 0, 1, 2 , 3. What I get is the Y axis at 0 - 250,000 and the X axis at 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (which are the cell numbers where 250000 is 1 and 1...
data fill sheet 2 from sheet 1 based on criteria
I am trying to Data fill sheet 2 based on criteria match from sheet 1. I have no knowledge of VBA and/or macros and very limited knowledg working with arrays. see sample data below sheet 1 note sheet 1 contains approx 2500 rows column A column B 2295.60 sales.sam 17496 jobs.sue 14.45 trade.john 1775 trade.sam Sheet 2 should look in column B for .sam or .john and when foun return results to sheet 2 columns A and B sheet 2 contain variable number of rows depending on occurrences o sheet 1 Column A column B...
sotring worksheets in a workbook
I have a workbook with 4 worksheets. The “name” column is linked from SHEET1 to the “name” column in SHEET2, SHEET3 and SHEET4. I need to be able to change a name in SHEET1 and sort all of the data colunms in all 4 sheets by “name.” Any ideas on how to do this? here are a couple to try Sub sortworksheets() Dim Cnt As Integer Dim n As Integer Dim m As Integer Dim WS As Worksheet Set WS = ActiveSheet Cnt = ActiveWorkbook.Worksheets.COUNT For m = 1 To Cnt For n = m To Cnt If UCase(Worksheets(n).Name) < UCase(Worksheets(m).Name) Then Worksheets(n).Move Before:=Wor...
How to ensure workbook is saved before exiting Excel?
Need to find "fool-proof" method of ensuring Users save Excel workbooks before exiting program. This has been a huge problem in the past. Can this be done with a macro? Kathy This macro, placed in the ThisWorkbook module, will fire whenever the user issues a close command (whenever the user clicks on "Close"). The macro checks if the workbook has been saved, and will save it if not. The workbook will then close. Come back if you need more. Otto Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Saved = False Then ThisWor...
primary key collisions due to auto reseeding of auto number field.
has anyone come across this problem in Access2003 Can you be more specific, Simon? Is the AutoNumber being seeded intentionally? Or is it one of the causes listed at the top of this article: Fixing AutoNumbers when Access assigns negatives or duplicates at: http://allenbrowne.com/ser-40.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "simcon" <simcon@discussions.microsoft.com> wrote in message news:0AFF03CF-F2B6-4312-ABA3-9DF036466FBD@m...
Backing Up Workbooks
Currently I have a macro button to backup my workbooks. I also have a save button. I would like to use only the save button but on a specific day of the week say Tuesday I would the macro also to do a backup. Can someone tell me the code to do this. I use the following to save the file. ActiveWorkbook.Save ActiveWorkbook.Close I use the following to backup the workbook. ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Splash").Select ActiveWorkbook.SaveAs Filename:="C:\Posto Pote\Backup\Posto Pote.xls", _ FileFormat:=xlExcel8, Password:="&...
Counting Highlighted Cells- Fastest way?
Currently I am working on web reports in which I have highlighted cells in several different worksheets. I then have a summary page which counts the Sum of Column J, COUNTA(J2:J1000) which counts the number of data entries in the range, and then i have to manually filter column J by highlighted color(yellow). Not all rows have a highlighted column and so I currently just Filter by Cell Color and is the Count function at the bottom of the screen; however this is time consuming and I am trying to automate this report as much as I can, but I am stuck on this. I am using Excel 2007 and have so...
number in cell changes
When I enter the Number 1 in a cell it changes to an 8. All other numbers are ok. Just started having this problem. Is this a hardware or software problem or do I have a setting wrong. I re-installed office and it didn't help. Doesn't occur in any other application. Most likely you have autocorrect (it might be a prank) tools>autocorrect options set to do the change open autocorrect, type 1 in the replace box and if it is there it will come up, then select delete Another option (not likely) would be an event macro -- Regards, Peo Sjoblom (No private emails please) "w...
Formating a decimal number
Hello I have a 2003 EXCEL. I want to average two decimal number number like 0.234 and 0.225. It is impossible with the formating protocol. Can any body help me to do this? With a formula =AVERAGE(A1,A2) what happens when you custom format the cell to 0.000 OR Select 'Number' format and allow 3 decimals.. -- Jacob (MVP - Excel) "JohnJJ" wrote: > Hello > I have a 2003 EXCEL. > I want to average two decimal number number like 0.234 and 0.225. It is > impossible with the formating protocol. Can any body help me to do this? I think you&...
Sheet Protection / Groupings
Using Excel 2003 Is there any way to protect a sheet and allow Outling to work? Paul Smith If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Ea...
Run-time error '1004' Method 'Sheets' of object'_Global' failed
I'm new to excel and don't really know what I'm doing. When the below macro is executed (through changing a combo box) the macro appears to run fine but when I close the workbook the above Run-time error message dialog box comes up, can anyone help? Sheets("dec").Range("A1:CO111").Cop Sheets("ROTA VIEW").Selec Range("B4").Selec Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= False, Transpose:=Fals Range("B3").Selec Thanks Richard, Add the line Application.CutCopyMode = false...
Incrementing packing slip numbers
We have a customer who partially fulfills, ships and invoices orders. They would like the packing slip to increment with each item shipped/invoiced. Is there a way to do this? Standard GP seems to reuse the same packing slip number for each item from the order regardless of when shipped. Thanks.. So basically you want all pay codes for an employee regardless of whether they are going to be paid or not in the next pay period. If this is the case, the only way to build this is by using SmartList Builder. 1) Open SmartList Builder 2) Enter an ID for your SmartList, say EMPPAYCODE 3) E...
How to close active Workbook without closing other open workbooks?
Not sure how this developed. I have Excel 10(?) from Office XP. I have more than one Workbook opened and then want to close one Workbook and continue working with the other opened workbooks. But I close one Workbook and all the Workbooks close at once. Does anyone know how to separate them from all closing at once? Or, have I unknowingly changed something to my "Book1.xls"/original at some point? If so, how do I correct that? Many thanks! framingham1@hotmail.com If you have tools|options|view tab|windows in taskbar checked, then maybe you assumed that Excel and Word worked simi...
Workbook Windows
How can make multiple workbooks show up on my task bar instead of having to go to "Windows" and putting a check by the workbook I want active? Hi goto 'Tools - Options - General' and check 'Windows in Taskbar' -- Regards Frank Kabel Frankfurt, Germany "Shanta" <Shanta@discussions.microsoft.com> schrieb im Newsbeitrag news:C0C2F817-16CF-4761-880D-53A81A751061@microsoft.com... > How can make multiple workbooks show up on my task bar instead of having to > go to "Windows" and putting a check by the workbook I want active? ...
Wildcard Usage: I absolutely need this to count?/sum? two or more criteri
_*Number*_ _*Status*_ _*Date*_ N01-02 Draft 4-May-05 N01-02 Draft 4-May-05 N01-92 In Process 15-Oct-05 N01-88 Draft 15-May-05 N01-29 In Process 9-Jul-05 N01-32 In Process 9-Jul-05 N02-22 Draft 24-Aug-05 N02-27 Draft 24-Aug-05 N20-12 Assigned 3-Dec-05 N24-19 Draft 24-Aug-05 N33-13 Doc Written 4-May-07 N44-04 Cancelled 15-Oct-05 My header are Number, Status, and Date (columns A,B and C). The dcuments represent my rows (A2-A13). Since I ...
How to see full path name of open workbook?
Is there a simple way to see the full path name (folder and file name) of an open workbook? Hi Joe, =CELL("filename",A1) Ed Ferrero www.edferrero.com Where do you want to see this? If you display the Web Toolbar you will see path and name if the bar. On the Title Bar? Add this code to Thisworkbook module. Private Sub Workbook_Open() ActiveWindow.Caption = ActiveWorkbook.FullName End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.Caption = "" End Sub Gord Dibben MS Excel MVP On Wed, 17 Mar 2010 21:22:04 -0...
Moving rows to other sheets
I was wondering if anyone could be of assistance with my dilemma. I want to run a macro that does the following: Sheet 1 has records with column headers such as name, address, and date. If the date field is populated with a date, I want to take the entire row and move it to Sheet 2, then delete the source row in Sheet 1. However, if the date field is populated by a word such as "Dead Deal" I want to take the entire row and move it to Sheet 3, then delete the source row in Sheet 1. Thanks in advance. Edwin Assuming you are only looking for a way to distingush between the two re...
create a link between workbooks
I’d like to create a link between two different workbooks. please help Hi one way: - open both workbooks - in the target cell enter the equation sign '*=3D' - now select with your mose the other workbook and the=20 source cell - hit ENTER >-----Original Message----- >I=E2?Td like to create a link between two different=20 workbooks. please help >. > Hi What kind of link? Link to cell in another workbook - several ways: 1. Enter the formula directly into cell, like ='C:\My Documents\My Excel Files\[SourceFile.xls]SourceSheet'!A1 2. Open both files. Into target s...