Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Perform Operation On Multiple Sheets

I would like to delete several sheets but the exact number is unknown. My code creates several worksheets after receiving input from the user. I would like to delete these sheets without deleting a few hidden worksheets that always exist. For example, my code creates several sheets named differently, like "AMFR", "AMCS" which I would like to delete, but I would not like to delete a hidden sheet called "Configuration." At the momen I'm using code something like:
For each ws in ActiveWorkbook.Worksheets
If<>"Configuration" then
End if

I had the idea that it's faster to refer directly to all the sheets (without using a loop) and then delete them. (I'm not sure if this assumption is true, that it's faster.) For example I'd like to have code like


This would avoid deleting the last "Configuration" sheet because I know it's always the last one in the workbook. However, this will only delete for example Sheets 1 AND Sheets 3, not Sheets 1-3 as I would like. Is it possible to delete an array of sheets using a variable? I would like to avoid using a loop but maybe it's not possible. Thank you!

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Perform Operation At A Particular Time...
I want to have my app write a report once/day at a certain time. How to do? Too many formats for time and can't get it to work.


Connection Cannot Be Used To Perform This Operation
I am trying to add a record to an Access database and i keep getting the following error:

Description: The Connection Cannot be used to perform this operation. It is Either closed or invalid in this context
Source: ADODB.recordset

VB Code:
Private Sub AddRecord() Dim ar As ADODB.Recordset  Set ar = New ADODB.Recordsetar.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:MIPER DEMIPRDEatch.mdb;Uid=;Pwd=;"ar.AddNewar!TransactionType = batchHeader.strTransactionTypear.Update End Sub

How To Perform NOT Operation With Binary Number??
I have binary number, example 11010010, how do I perform NOT operation and get result 00101101? And how do I then add 00000001 to the result?

MESSAGE: Can't Perform Requested Operation.
I have a download from SAP upon which I run a macro in Excel that smartens it up into my required report format. I wrote the code last month and it worked fine, I have run it today and I get the message, Can't perform requested operation. I am stumped as to why, the download from SAP hasn't changed, I haven't changed the code but it won't run. Why? I am well stumped. Any help would be much appreciated, I have read the piece on but it's not applicable to my case.



Code To Ungroup Sheets, Perform Task, Then Regroup?
I need to perform tasks that require ungrouping sheets. The code would be structured as follows:

-call to sub to ungroup sheets

-perform some task

-call to another sub to regroup sheets

The macro recorder gave me what I thought I needed as syntax for selected sheets, but when I run the following code I get an "subscript out of range error".

Status1 and Status2 are Public string variables; ctr is a public integer variable...

Sub test01()

Status1 = """" & ActiveSheet.Name & """"
For Each Worksheet In ActiveWindow.SelectedSheets
If ctr = 0 Then
Status2 = """" & Worksheet.Name & """"
Status2 = Status2 & ", """ & Worksheet.Name & """"
End If
ctr = ctr + 1
Next Worksheet

MsgBox "Grouped sheets are: " & Status2 & Chr(13) & "The active sheet is: " & Status1

Sheets(Status1).Select '#####ERROR
MsgBox "Sheets are now ungrouped. " & Chr(13) & "The active sheet remains: " & Status1

Sheets(Array(Status2)).Select '#############ERROR

End Sub
Anyone have any bright ideas? TIA

How To Perform Adding Operation On Datagrid's Row Or Column
Im working on an accounting system. On the net income form i have two rows and three columns i.e Sum of Credit , Sum of Debit, Net Income respectively. All i need to do is to Add Row 1 of Sum of Credit column's element with 2nd Row of Sum of Debit column. In simple words , I want to add 1 row of 1 column into 2nd row of 2nd column and calculate the net Income. Please tell me how to do it. If this question is already answered please send me the link.

Help! Need To Open Acrobat Reader Then Perform Find Operation
Thanks in advance for any and all help. I've got an application that loops through a trip itinerary and picks out each destination. What I would like it to do then is to open Acrobat Reader and automatically either perform a FIND operation looking for that destination as the key word or go to that destination's bookmark in a .pdf document containing a description of each destination. The .pdf document already exists. I don't know how to get my application to open Acrobat Reader and then go find the destination. Essentially, this is like having my application automatically look up a word in a dictionary. I would very much appreciate any help esp sample code. I'm not a beginner at VB but certainly not advanced. Have spent several days now trying to figure this out. Getting to the point of pulling hair out here.

Runtime Error 3709. THe Connection Can Not Be Used To Perform This Operation. It Is E
Hi there,

I'm getting the error on .open cmdCommand.

Function returnRS(cmdCommand As ADODB.Command) As ADODB.Recordset
Dim rstReturnRS As New ADODB.Recordset
Dim str As String
With rstReturnRS
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With
Set returnRS = rstReturnRS
End Function

Any help you can provide would be highly appreciated.


Importing Multiple Sheets From A Excel Spreadsheet Into Multiple Tables
hello all,

I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with.

In a workbook at the start of the year (january) i will have 4 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named xyz). then sheet 4 through the next sheets till sheet 12 (named balance1, balance2, on till balance12) should all go into one table 'table4' (named balance) and all these sheets should keep appending starting from 1 then 2 all the way to 12.

I am able to put one sheet into one table and below is the code....i need help with the other part of my requirement

Code: ( text )
Dim cn As ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim rsAccess As New ADODB.Recordset

' Open Excel Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Test.xls;" & _
"Extended Properties=Excel 8.0;"
End With

' Open Access Connection
Set cnAccess = New ADODB.Connection
With cnAccess
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Documents and SettingskrishnamDesktopIntercompany Consolidation.mdb;"
End With

' Load ADO Recordset with Excel Sheet1Data

oRs.Open "Select * from [abc$]", cn, adOpenStatic
MsgBox oRs.RecordCount

' Load ADO Recordset with Access Data
rsAccess.Open "select * from tbl_abc", cnAccess, adOpenStatic, adLockOptimistic
MsgBox rsAccess.RecordCount

'Synchronize Recordsets and Batch Update
Do While Not (oRs.EOF)
For i = 0 To 11 -----11 columns in table 1
rsAccess.Fields(i).Value = oRs.Fields(i).Value


End Sub

please help me so that i can move forward...

Countif() With Multiple Criteria && Multiple Sheets
I have 4 sheets in an excel workbook
The data starting from the sheet1 column B row 3 (B3) (the column A is empty only in the sheet1 not to the others three) and i dont know where the columns ending.
What i know is that the columns never be more than 1024 (256 columns x 4 sheets)

The data that there are in the cells, are numbers from 0 to 5.

The row 2 (B2, C2,..., IV2 etc) is using to make a group, so there are text numbers (01,02,03 etc ) for the purpose. So i will have 10 columns with 01, 50 columns with '02', 45 columns with '03' on the row2 and goes on.

What i want is:
I want to count how many 0,1,2,3,4 and 5 are for each cell grouping by the row group each time i choose.

So i want to count the the 0,1,2,3,4 and 5 for the group '01' or
count the the 0,1,2,3,4 and 5 for the group '03'.

In a single sheet if i want to find the 0 and if i know where the columns of the group '01' starts and ends it would be like this


I hope you understand
Thanks in advance

Countif() With Multiple Criteria And Multiple Sheets
I have 4 sheets in an excel workbook
The data starting from the sheet1 column B row 3 (B3) (the column A is empty only in the sheet1 not to the others three) and i dont know where the columns ending.
What i know is that the columns never be more than 1024 (256 columns x 4 sheets)

The data that there are in the cells, are numbers from 0 to 5.

The row 2 (B2, C2,..., IV2 etc) is using to make a group, so there are text numbers (01,02,03 etc ) for the purpose. So i will have 10 columns with 01, 50 columns with '02', 45 columns with '03' on the row2 and goes on.

What i want is:
I want to count how many 0,1,2,3,4 and 5 are for each cell grouping by the row group each time i choose.

So i want to count the the 0,1,2,3,4 and 5 for the group '01' or
count the the 0,1,2,3,4 and 5 for the group '03'.

In a single sheet if i want to find the 0 and if i know where the columns of the group '01' starts and ends it would be like this


I hope you understand
Thanks in advance

Creating A Macro To Perform Multiple Tasks In Access

Does anyone know the proper code that would be used to import a text file to a new table, print a report of all data in the file, and combine the new table with an existing table? The text file I'm using is called APMAIN. The new table I created is called APMAIN. The report I created is called APMAIN. The existing table that I need to combine the new table with is called Accounts Payable Transaction File. The data also has to refresh everytime the file is opened. I've looked in my old Access textbooks and on the Internet and have found nothing. I need this information soon. This may seem like a lot, but I was told it wouldn't take a lot of code. Is this true? If it does, than I apologize. I've never used VB before. I use Access 2000 & 2002. Thank you. Please hurry!!

Lisa Girod

Multiple Sheets In CSV

I am using the following code in VB6 to generate a CSV file that opens in MS EXCEL :


Dim printLINE as String
Dim ctr as Integer
Open "c:File1.CSV" For Output As #1
For ctr = 0 To 20
printLINE = printLINE & ctr & ","
next ctr
Print #1, printLINE

This generates a csv file by the name File1.csv with only one sheet by the name File1.

My question is that how can i have more that one sheet e.g. File1 & File2 within the same csv file File1.CSV?

Does any body have any idea ?


Range On Multiple Sheets
Hi, I have to make a range that is on two worksheets, is that possible? I have a sub that creates the range on one sheet. I need the same group of cells on a different worksheet added to that range. Here is the sub:

Function month_rng_finder() As Range

Dim wks As Worksheet, cell As Range, rng As Range, rc As Long, S As Date

Set wks = Worksheets("sheet 1")
rc = wks.Cells(Rows.Count, 1).End(xlUp).Row
Set cell = wks.Cells(rc, 1)
If cell.Row < 30 Then
Set month_rng_finder = Range(Cells(3, 1), Cells(rc, 1))
Set month_rng_finder = Range(Cells(cell.Row - 30, 1), Cells(rc, 1))
End If

End Function

if the range on sheet 1 is for example b2:h30 then i need month_rng_finder to be sheet1 b2:h30 and sheet 2 b2:h30.

Thank you

Selecting Multiple Sheets
Hi, I have a listbox that displays all the sheets in an excel document. Is there a way to select multiple sheets and do different calculations with the cells? Example: I have data that I want to add up on 4 different sheets, how would I do that? Thanks for your help.

PDFing Multiple Sheets

Can someone point me to where I can get VBA code to pdf multiple sheets in Excel into one pdf file??

If i do it manually i have to pdf each sheet individually and then put it together in acrobat which is very time consuming.


Copying Multiple Sheets
I have several opened excel files, each file has only one sheet. I need to copy each sheet of each file to a new book. I wanted to create a loop to do this.
Any idea?

Searching Multiple Sheets
I have come across another problem whilst making my spreadsheet. I have used this forum before where I was able to create the following code from what I learnt. This allows me to search a sheet for a value that I would be able to enter into an input box. I have now run into another problem. I now cannot work out the code that is needed so that it will search more than one sheet. I will need it to search 5 different sheets. I am guessing that it is something to do with a “loop” but I cannot find any information on this to make it work. If anyone could give me the pointers to make this code search multiple sheets I would be very grateful. I am sure that it is something very simple to do, but as I am new to using VB Code it is causing me a few problems. The code that I currently have to search one sheet is shown below. Thank you in advance.

Sub CopyQuote()
Application.ScreenUpdating = False
Dim rngOrder As Range
Dim lngReferenceNumber As Double
lngReferenceNumber = Application.InputBox( _
Prompt:="Enter reference number", _
Title:="Enter reference number", _
Set rngOrder = Worksheets("Quote History").Range("A1:AI1000").Find( _
what:=lngReferenceNumber, _
If rngOrder Is Nothing Then
MsgBox "Quote not found"
With rngOrder
If MsgBox("Do you want to copy this quote?", vbYesNo, "Copying Quote...") = vbYes Then
Sheets("Data for Retrieve Quote").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Retrieve Quote").Select
MsgBox "Quote not copied"
Sheets("Main Page ").Select
Application.ScreenUpdating = True
End If
End With
End If
End Sub

Help Please {Combine Multiple Sheets Into One}
Hi, this is my first time posting on this board and I am relatively new to programming with visual basic. I am an undergaduate who is using Visual Basic 6 to build a program that will compile 72 individual excell sheets into a single excel document. A graduate student who was part of my research group has already made a program that works for 61 files; however, every attempt I have made to alter the program has failed to produce a working program. Does anyone know how I can solve my problem?

Scaling For Multiple Sheets
My excel doc has about 200 sheets. I’m trying to change the Scaling for all sheets at once (Adjust to_ _ % of normal size).
I have selected all sheets. However, only the fist sheet is changed.

How can I fix this?

Excel 2002

Multiple Sheets In An If Statement...
Hi everyone, I'm using this line of code:

If mySht.Name <> "NAGSHEET" Then
to exclude the sheet "NAGSHEET", is it possible to exclude 2 sheets? I've tried:

If mySht.Name <> "NAGSHEET" And "xSetup" Then

If mySht.Name <> "NAGSHEET" Or "xSetup" Then
and neither of those work....
Any ideas??


Dave M.

Accessing Multiple Sheets
iam working on vb front end and access bE project
the access db has 4 tables named week1,week2,week3,wek4. All the tables have similar fields .When i want to update table i want to use one set of cods to update the selected table
the table selection is done thro optionbuttons. Though i managed to make it separate coding i am interested in using a common function/sub and passing the parameters
The code i have written is as follows

Dim db As Database
'Public comrs As Recordsets
Public rsweek1 As Recordset
Public rsweek2 As Recordset
Public rsweek3 As Recordset
Public rsweek4 As Recordset
Dim ws As Workspace

Private Sub Form_Load()
Set db = OpenDatabase("C:Man hourMan hour.mdb")
Set rsweek1 = db.OpenRecordset("week1", dbOpenDynaset)
Set rsweek2 = db.OpenRecordset("week2", dbOpenDynaset)
Set rsweek3 = db.OpenRecordset("week3", dbOpenDynaset)
Set rsweek4 = db.OpenRecordset("week4", dbOpenDynaset)
End Sub

Private Sub CmdAdd_Click()
If OptWK1 = True Then
ElseIf OptWK2 = True Then
ElseIf OptWK3 = True Then
End If

End Sub

Private Sub CmdUpdte_Click() ---- similar codes for week 3 and 4
If OptWK1 = True Then
write_data_wk1 ---------------[1]
ElseIf OptWK2 = True Then
write_data_wk2----------------------[2] takes to anothr sub same
as write_data_wk1()

End Sub

Public Sub write_data_wk1()
hrstotal = Val(TxtNorhr.Text) + Val(TxtOthr.Text)
If rsweek1.EditMode = 1 Or rsweek1.EditMode = 2 Then
rsweek1.Fields("WK NO") = "Week 1"
rsweek1(2) = TxtDate.Text
rsweek1(3) = TxtWon.Text
rsweek1.Fields("WO Type") = wotype
rsweek1.Fields("C C") = sCC
rsweek1.Fields("HRS-N") = CDec(Val(TxtNorhr.Text))
rsweek1.Fields("HRS-OT") = CDec(Val(TxtOthr.Text))
rsweek1.Fields("HRS-TOT") = hrstotal
rsweek1.Fields("Report text") = TxtReport.Text
End If
End Sub

----- written same code for week 2,3&4 is there any other method to write one procedure / function so that [1] and [2] are not required


Excel Multiple Sheets
Is it possible to create a report in Crystal report from Excel that has multiple sheets. All sheets should be displayed in the report. Can this be accomplished?

Creating Multiple Sheets
I need some help creating and accessing multiple Sheets within one excel file.

I have a variable number that indicates how many Sheets I need to Create.

This is my code:

Set XLApp = New Excel.Application 'Create a new instance of Excel
Set XLBook = XLApp4.Workbooks.Add 'Create a new Workbook
XLBook.SaveAs ("c:Book.xls")

For i=1 to number
Set XLSheet = XLBook4.Worksheets.Add
XLSheet.Name = "Sheet" & i
Set XLSheet = XLBook4.Worksheets(i)

....fill in sheet....
Next i

Say my number= 5, then i should have a file called "Book.xls" with 5 Sheets names "Sheet1, Sheet2, Sheet3, Sheet4, Sheet5) How can I do this?

Multiple Sheets Selection~ Help
Hi, sorry to be such a rush cos I found I am in death meat within 2 more days to go before deathline, beside have a last try on here. I have a run time error 1004 while doing this... in Excel (Version 2000 (9.0.4402 SR 1) runing VB.

1) If clicked on ¡®Shift¡¯ and highlight > 1 worksheets then clicked on any cell, the Runtime Error 1004 appeared

The error is from :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'JL 19/08/02: Added for SIR-2226
If Target.Column <= 5 Then
ThisWorkbook.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

However, I need this to protect the first 5 column for read only.

2) Whenever trying to save the repayment schedule onto the local desktop, the following error message is prompted and the excel will be killed off. Not able to save repayment schedule. Tested that this error message only get prompted if file is saved as the default ¡®Ms Excel 97-2000 & 5.0/95 Workbook¡¯.
If changed file type to ¡®Ms Excel Workbook¡¯ then no error is prompted.

Your expertise and lending a helping hand a much much appreciate. Any guidance and advices are welcome too. Thanks again.


Userforms On Multiple Sheets?
I have a userform which gathers inputs from the user and then tries to find them in a spreadsheet. Because Excel cannot display all the days in the year because of row limitaions being 256, i put each month on separate sheets within the workbook. Is it possible to have this code find the data in the sheet which is currently selected? If so, how would I go about it???

Many Thanks


Excel Multiple Sheets
Is it possible to create a report in Crystal report from Excel that has multiple sheets. All sheets should be displayed in the report. Can this be accomplished?

Multiple Excel Sheets To Access
I have 10 excel files with up to 500 worksheets in each file. I am familiar with TransferSpreadsheet...

I can loop thru files in a folder, but once I get to the excel spreadsheet, how do I loop thru each worksheet?

Pick Multiple Sheets In Excel
Does anyone know how I can pick multiple excel sheets that have date names, could be any number of sheets, and copy them to another workbook?
Also how do you pick multiple cells from multiple sheets and do a calculation on one sheet.
I know this sounds easy to some of you but I'm new.

Search Multiple Excel Sheets
Hello. I have an Excel macro that searches all worksheets (expect Sheet3) in the open workbook for the text "new" Upon finding "new" the entire row is copied and then pasted into Sheet3. This is working fine.

However, I am getting an error message (Object variable or With block variable not set) when a worksheet does not contain the text "new"

Mode is as follows:

Dim wSht As Worksheet
Dim allwShts As Sheets
Dim creditAdj As String
Dim startRow As Integer
Dim myRange As Range

Set allwShts = Worksheets

For Each wSht In allwShts
creditAdj = "new"
If wSht.Name <> "Sheet3" Then
startRow = wSht.Columns("B").find(creditAdj, _
LookIn:=xlValues, LookAt:=xlWhole).Row
Range("B" & startRow).Select
Set myRange = ActiveCell
ActiveCell.Offset(1, 0).Select
End If
Next wSht
I tried moving the find function out of the loop but I can't seem to get the syntax correct. I am not even sure if I should move it out of the loop.

Any help would be much appreciated. Thanks

Unprotecting Multiple Sheets In Excel
Does anyone know the answer?

I am trying to unprotect multiple sheets within Excel. Selecting the sheets is easy enough, however I have been unable to remove protection from all the selected sheets.
Any help would be greatfully received.

<P ID="edit"><FONT class="small"><EM>Edited by westbrookrj on 09/19/01 07:47 AM.</EM></FONT></P>

Unhide Multiple Sheets At One Time
I'm trying to "Unhide" sheets using an array... I know this will work for "Hiding" but trying to "unhide" gets a "visible property" error

Sheets(Array("Weekly Report Data", "Total Project Data", "Formula Sheet")). _
Visible = True


Display Same Object On Multiple Sheets
Is it possible to display the same textbox object (or other embedded objects) on multiple sheets, without placing a copy on each sheet.
I just can't find a way to do it.....


Reference Multiple Sheets In An Array, I Think!!
I have a spreasheet with several worksheets. Each sheet has the same layout - a pricing grid - but i have a sheet for each different supplier. This means that =MIN(Sheet1:Sheet7!E6) will find me the lowest price out of all the suppliers for the product referred to in cell E6. However, i need to return the name of that supplier. I have used vlookup and hlookup before but obviously they are not going to work with multiple sheets. I figured that i could do it using an array formula but i haven't got much experience in using these and all my attempts have failed. What can i do?

Copying Multiple Sheets With Links
when I copy 10 sheets from one data base to another I get a constand link error. the error states that I have links to the original database. How do I remove this link. The original database has zero links?


Loading From Multiple Sheets On Another Workbook
I'm trying to extend an existing function I wrote to use different sheets on a different workbook, instead of the active workbook.

The function below did work when using 'local' sheets. But I want to load all my data into a another xls file and load/read from it as and when required. I use multiple sheets a lot and don't want to have to add the same sheets to each of my workbooks each time i want to use the data.

It had now stopped working. It works for a split-second if I manually load the test.xls file, but after a sec it refreshs and returns error values.

Also, as you can see, I'm trying to get it to return "n/a" if it can't find a matching set of values, instead of #VALUE!.

Any ideas anyone?

Public Function lookupref(ref As String, dateref As Date, season As String, reqdcol As String)

'Load and activate correct season sheet
Dim wbTest As Workbook
tmpfile = "c: est.xls"
Set wbTest = Workbooks.Open(Filename:=tmpfile)

'Get size of dataset
lastrow = ActiveWorkbook.Worksheets(season).Cells(2, "A").Value

'Loop data
lookupref = "n/a"
For a = 3 To (Int(lastrow) + 2)
tmpflt1 = UCase(ref)
tmpflt2 = UCase(ActiveWorkbook.Worksheets(season).Cells(a, "B").Value)
tmpdate1 = dateref
tmpdate2 = DateValue(ActiveWorkbook.Worksheets(season).Cells(a, "A").Value)
'MsgBox tmpflt1 & " " & tmpflt2 & " " & tmpdate1 & " " & tmpdate2
If (tmpflt1 = tmpflt2) Then
If (tmpdate1 = tmpdate2) Then
'MsgBox "Match"
lookupref = ActiveWorkbook.Worksheets(season).Cells(a, reqdcol).Value
Exit Function
lookupref = "n/a"
End If
lookupref = "n/a"
End If
Next a

End Function

Using Multiple Excel Sheets Within One Workbook
I am doing a program that gathers information from different cells in Excel, and inputs them as different variables.

Right now, I've got a scarce understanding of how to use excel with, and although I've got my program working properly, I havent been able to figure out how to tell it to get Cell x,y from sheet a, and then get cell x,y from sheet b.

If anyone could help me out here, by either an explanation of how this should work, or even a sample code that makes this work I can probably take it from there.

I'd appreciate any help I can get. Thanks everyone!

Paste Values For Multiple Sheets
I am trying to write a command that will unprotect all worksheets, copy all cells on each worksheet, paste the values on the same sheet (replace any formulas with their values), re-protect all worksheets and then save the file. The code I am using is below. The protect, unprotect, and save all work fine. What I can't get to work is the copy and paste. It will only copy and paste the active sheet and will not switch to the next sheet. I am not an expert in VBA by any means, so this may be an easy fix.

Sub CommandButton1_Click()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="password"
Next ws

Application.ScreenUpdating = True

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next ws

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="password"
Next ws


End Sub

Copy Sheets From Multiple Excels To Another
Hi guys, I hope you can help.

I am attempting to use a VB macro in Excel to import sheets from other open Excel workbooks to this one. I have found the code to move or copy the sheets from one document to another fine. My issue comes when I need to specify the workbook the sheets are to be copied from. Although the workbook to be copied to will have a single constant name, the workbook to be copied from could be anything. To solve this I'm trying to get an user option box so they can selct which book to copy from.

I have managed to do this for the sheets within the book by using:

"Function Go2sheet()

Dim mySht As Single

myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i

mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)

End Function"

When I have tried to adapt this to return the workbook name it trips up with an error. I think this is due to the j valueas adding 1 to the name does not equal the next name.

"Function Go2Workbook()

Dim myWorkbook As Single

myWorkbook = Windows.Count
MsgBox (myWorkbook)
For j = 1 To myWorkbook
myWBList = myWBList & j & " - " & ActiveWorkbook(j).Name & " " & vbCr
Next j

myWorkbook = InputBox("Select workbook to copy from (Enter number only)" & vbCr & vbCr & myWBList)

End Function"

Error is 'Error 438 - Object doesn't support this property method'

Any help with this would be greatly appreciated.


Searching/Copying Sheets From Multiple Workbooks
Hi all,
I have hundreds of workbooks I need to gather information from. Within each workbook there are between one and five sheets. Currently I'm using a macro that searches for a specific sheet, finds it, opens it, copies a column of data, and pastes it (as a row) in a summary workbook. When it encounters a workbook that does not contain the sheet it is looking for it locks up. When this situation occurs I would like the macro to insert a blank row in the summary workbook and continue searching through the other workbooks. Would someone please tell me how this can be accomplished?

Thank you!

Command Buttons Sharing Same Name On Multiple Sheets.
Hey guys,

I'm looking to create a series of command buttons along the top of a standardised layout for an excel app. Each command button links to a different worksheet. I'd like to know if it's possible for each 'Products' command button to share the same name etc. because at the moment I recieve 'ambigous name' errors.

Thanks for any help.


AutoFill On Multiple Sheets With Different Range Sizes
Here is a snippet of the code that I am currently using to AutoFill on multiple sheets, as you can see I am selecting each sheet seperately to perform this function. The reason for this is due to the fact that the AutoFill Ranges are of different sizes on each sheet. On these examples one range is to [N7] and the other is to range [N17]. There are 15 sheets total, all with different AutoFill there anyway to do this without having to select each sheet seperately. Or is there just a better way of doing it the way I am doing it now??...

[N5:N6].AutoFill [N5:N7]

[N5:N6].AutoFill [N5:N17]

As always... Thanks!

Sumif With Source Data On Multiple Sheets
Hi everyone,
Been trying to use a sumif...

My workbook contains the following:

Evaluation range: B28:B38 on sheets 1:31
Criteria: cell value = certain text
Sum range: C7:C17 on sheets 1:31

What I tried to enter was "=sumif(1:31!B28:B38,"=text",1:31!C7:C17)" and what I get is "#value!". My ranges are probably incorrect, but can anyone tell me how to enter this, if it is possible at all to use sumif in this case?


Collecting And Appending Data From Multiple Sheets
I want to create an Excel sheet that will allow the user to look at all the Excel files in the same directory, select which ones he wants to append, and then click a button, which will copy the data out of a sheet from the selected Excel files onto the original spreadsheet.

So I'm picturing a workbook with two sheets: one sheet that contains two listboxes, one that lists all the files in the directory and one that contains the selected files. The user can select a file and click an arrow that will bring it over to the 2nd listbox. Once that's done, he'll click an OK button and it will loop for all selected workbooks the process of opening selected workbooks, copying the data over, then closing it.

My question is: How do I use Excel VBA to find all the .xls files in a directory in order to populate the first listbox with these file names?

Also any other tips on the rest of this would be greatly appreciated.

'Replace' Method Accross Multiple Sheets
Sub Macro2()
' Macro2 Macro
' Macro recorded 23/07/2003 by DANONE

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Cells.Replace What:="y", Replacement:="x", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
End Sub

So that's the macro helpfully recorded by Excel to replace a value on a multiple sheet selection - except when you run in throught VB Editor a second time (remembering to replace the criteria) it only executes on the Active sheet


This is an attempt to prevent Excel raising the 'File not found' dialog when a formula which refers to a sheet not yet added to a new file, is activated. Previously, I've diabled all the possible 'update' and 'calculate' type properties for the Excel Application object, but regardless, Excel faithfully freezes my Access code execution until the dialog is dimissed.

The senario is thusly:
Place a formula such as "|=Account!A1" in a cell, then execute a Find/Replace to remove the virtical bar (which is present to prevent calculation and a resultant link to the original template file). The "Account" worksheet arrives once up to 30 other such sheets have been generated - and no, I can't do it the other way around

Either solution would be satifactory

VB Code Needs To Apply To Multiple Sheets, But Gives Errors
I'm trying to use a control button on one worksheet to hide or otherwise change cells on multiple sheets. For the cells on the same page as the button, everything works. I get errors when I reach the code that is supposed to affect cells on other sheets. I've looked through all examples and everything I can find in HELP, and it doesn't HELP. Is this a PUBLIC vs. PRIVATE (declaration) issue, or something else?

Copying Cell Values From Multiple Sheets XLS

I am having trouble coming up with a way to copy cell values from multiple sheets of a workbook into an empty range in another sheet of the same workbook.

For example, if we have 4 sheets - sheet 1, 2, 3, and 4 of a workbook,
I want to copy the values from the cell B15 in sheets 2, 3, and 4 into some range (say, a1:c1) of sheet 1.
That is, I want the range a1:c1 of sheet1 be filled with values from cell B15 of the three sheets (2, 3, and 4).



Printing Multiple Sheets On One Piece Of Paper
I have a spreadsheet with two sheets. I need to print a selected area from both sheets onto 1 piece of paper. Right now im having problems with getting just one of the sheets to print out correctly.


With Worksheets("TIME SHEET").PageSetup
        .CenterHorizontally = True
        .PrintArea = "$A$1:$N$27"
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
Worksheets("TIME SHEET").PrintOut
With Worksheets("EXPENSES").PageSetup
        .CenterHorizontally = True
        .PrintArea = "$A$1:$O$21"
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With


Im having trouble with the .FitToPagesWide = 1
if I go into excel and goto pagesetup and select "adjust to 100%" like what is defaulted on excel. When it prints out it does not fit it to the page. When I go in and manually set it, it prints fine when i run this.

The two print out on separate sheets of paper, ive looked at a couple of sites and the help in excel and they showed examples of printing different ranges on the same sheet of paper but It didnt work when i did


Worksheets("TIME SHEET").PageSetup.FitToPagesWide = 1
Worksheets("TIME SHEET").Range("A1:N27").PrintOut
Worksheets("EXPENSES").PageSetup.FitToPagesWide = 1

This prints it out the same way as it prints with the code at the top. They both still come out on 2 sheets of paper

one of the sites gave this example for printing multiple ranges on one sheet of paper


Sub PrintRpt2() 'To print several ranges on the same sheet - 1 copy
End Sub


But I need to print ranges from 2 different sheets so i have to have the "Worksheets("")" part in there.

Im pretty new to VBA, so any help or input would be appreciated.

Error While Using .Find On Multiple Excel Sheets
I have searched for some type of solution to this problem, but to no avail. So here we go.

I am trying to search through all of the sheets in an excel workbook.

But for some reason, I cannot get this code to search through every sheet. All I get in return is the first page search results, and it is duplicating
some of the results.

Can anyone please take a look at this code and tell me where my problem lies. Thanks in advance.

James Davis


Dim objExcel As Object ' Excel application
Dim objBook As Object ' Excel workbook
Dim objSheet As Object ' Excel Worksheet

Dim ExcelCutlist As String
Dim rng As Range
Dim firstAddress As String
Dim PcMark As String
'Dim objSheet As Excel.Sheets

ExcelCutlist = App.Path & "" & "TANA EC 271 I.xls"

Set objExcel = CreateObject("excel.application")
Set objBook = objExcel.Workbooks.Open(ExcelCutlist)
'Set objSheet = objBook.Worksheets.Item(1)

objExcel.Application.Visible = True

For Each objSheet In objBook.Worksheets
        Set rng = Nothing
        With objSheet.Cells
        Set rng = objSheet.Range("D:E").Find(What:=txtHeat.Text, lookin:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

                If Not rng Is Nothing Then
                    firstAddress = rng.Address
                            cboPcMark.AddItem Range(rng.Address).Offset(0, -4)
                            Set rng = objSheet.FindNext(rng)
                        Loop While Not rng Is Nothing And rng.Address <> firstAddress
                End If
        End With

Next objSheet

Set objSheet = Nothing
If Not objBook Is Nothing Then objBook.Close
Set objBook = Nothing
Set objExcel = Nothing

Edited by - jholokai02 on 10/4/2006 7:28:27 AM

Copyright © 2005-08, All rights reserved