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

Vba And Multiple Worksheets

I am trying to write a macro to pull data from 1 of multiple worksheets and enter it in another sheet. The data is addresses up to 3 possible. I need to use the last entered data (row) from whichever sheet is chosen using a userform. Can anyone help me with a macro for that? Thank you!

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Help With Opening Multiple Txts Files In A Single Excel Files With Multiple Worksheets
I am new to using visual basic code as I usually use it for making macros inside the Excel program.

I would like to to open numerous text/ascii files into a excel file. I would like to write VB code that I can point to a directory (and it's subdirectories) to identify text files in the folder and import the data of each file into one excel file. Each separate file would be opened into a new work sheet. File1 goes to excel file worksheet 1, file2 same excel file worksheet 2 etc. etc. What is the best way to do this? Is there a script out there that already does this?
What I want to avoid is manually open up and copy each of these fields to a new or manually use the import text wizard as we will a large number of separate files with data.

This forum thread has the code which seems to do what I need but I got it to work only once and then i get errors all time.
Does it need to run as VB project or as a macro in excel??

microsoft has a example of what i want to do but again not in enough detail for a novice programmer is another example but i want to opem multiple files in one goal

After this code we have rearrange our using another excel marco and graph the data so we have a lot of work ahead of us
Thanks for your help in advance!


Multiple Worksheets
I am trying to run through all worksheets in my workbook using the for loop. The following code isn't working and I can't figure out why.

Option Explicit

Public Function Merge_Date_Time()
Dim index As Long
Dim worksheet As Object

For Each worksheet In Workbooks(ThisWorkbook.Path).Sheets
Do While Range("a1").Cells(index, 1) <> 0
Range("e1").Cells(index, 1).Formula = "=concatenate(a1, b1)"
index = index + 1
Next worksheet
End Function

Multiple Worksheets
I am putting data from a temporary file onto an excel worksheet. Is there any way to know if I have reached the maximum number of rows on a worksheet, tell my datafile to hold on, create a new worksheet, and tell my file to resume output. (I mainly need help with telling my datafile to pause and telling it to resume when I create a new worksheet). Any Suggestions would be greatly appreciated.

Heres the code that puts the data from the file to a worksheet (I created the "m_excel" object earlier in my code):

m_Excel.Workbooks.OpenText (filename:=DataFile,Origin:=xlWindows, DataType:=xlDelimited, Other:=True, OtherChar:=Delimiter, TextQualifier:=xlDoubleQuote)

Searching Across Multiple Worksheets Via VB6
I'm trying to figure out how to search for text across multiple worksheets
without knowing there names. I've tried kludge'ing it together from different
threads in the archives but they are all for VBA not VB6.

My attempt starts at the "Dim wb&" line and goes to the
"Set c = General.xlWkbS.End(xlDown)" line.

Can someone point me in the right direction?

Thanks in advance,
- Brad

Public Sub Survey()

Dim a As Range, b As Range, c As Range 'These ranges are for searching the Workbooks
Dim LastRow&, LastRow2&, LastRow3&, LastRow4& 'Var's used for End of Row calculations
Dim aa& 'Var used as a Survey request counter
General.xlSur.Columns("A:A").ColumnWidth = 100 'Resize the A column to Width 100
LastRow2& = General.xlSurv.Range("A65536").End(xlUp).Row 'Find the last used cell in the survey data
LastRow3& = General.xlFD.Range("B65536").End(xlUp).Row 'Find the last used cell in the Players Fleet & Defence worksheet
LastRow4& = General.xlGM.Range("A65536").End(xlUp).Row 'Finds the last A Column row number in the GM worksheet
For Each a In General.xlFD.Range("B1:B" & LastRow3&) 'Selects the used range in the player worksheet
If a.Value Like "Survey ###x###/*" Or a.Value Like "survey ###x###/*" Then 'Looks for the Survey request
' Set b = General.xlSurv.Range("A1:A" & LastRow2&).Find(Right(a.Value, 10)) 'Sets the range to the used A column and searches for the bb string var
Dim wb& '
For wb& = 1 To General.xlWkbS.Worksheets.Count '
Sheets(wb&).Select '
Set b = General.xlWkbS.UsedRange.Find(Right(a.Value, 10)) '
Set c = General.xlWkbS.End(xlDown) 'Lets do the search
General.xlSurv.Range(b.Address, c.Address).Copy 'Copy the Survey data from the Survey Worksheet
General.xlSur.Activate 'Switch to the Surveys worksheet
LastRow& = General.xlSur.Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Row 'Find the last used cell in the Players Survey Worksheet
General.xlSur.Range("A" & LastRow&).Select 'Select the ff$ range (the survey request)
General.xlSur.Paste 'Paste the survey into the Players Worksheet
Next wb& '
aa& = aa& + 1 'Count how many Survey's were procesed
End If 'End of range
Next a 'Wash and Repeat
General.xlGM.Range("A" & LastRow4& + 2).Value = "Starting Survey Requests: Processed " & aa& & " Surveys"
General.xlGM.Range("A" & LastRow4& + 2).Font.ColorIndex = 10 'Make it green
End Sub

Concatenate Across Multiple Worksheets

My function doesn't seem to work. I need to concatenate the text strings from a range of cells from all worksheets in the workbook. I can't figure out what I'm doing wrong here.

Function ConcatenateAllWS(InputRange As Range) As String

Dim ws As Worksheet, Temp1 As String, Temp2 As String
Application.Volatile True
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
Temp1 = Application.WorksheetFunction.Concatenate(ws.Range(InputRange.Address))
End If
Temp2 = Application.WorksheetFunction.Concatenate(Temp2, Temp1)
Next ws
Set ws = Nothing
ConcatenateAllWS = Temp2
End Function
Also is there any way to add a comma in between each cell's text?


Help - Creating Multiple Worksheets
I am writing a macro to create multiple worksheets based on a column of values. While I can get the macro to create and name a single worksheet based on a specified cell I can't figure out how to do it iteratively down the complete list. I'm not sure if I'm suppose to use a Do...Loop with Offset ranges or what.
Any suggestions would be much appreciated. Thanks, Eric.

On Working With Multiple Worksheets.
I'm currently having issues with working with multiple spreadsheets. We've got a situation where our body of data has thousands of columns, so it has to be split across multiple worksheets; however, for many operations, it's necessary that it all be treated *as if* it were the same worksheet. For example, if a particular row on Sheet1 is hidden or deleted, the corresponding rows on Sheet2, Sheet 3, ... , Sheet N have to be hidden or deleted as well. Other functionality that's required includes performing sorts that affect multiple worksheets based upon sort keys that appear on only one of the worksheets, or even sorting multiple worksheets based upon multiple keys that each appear on a *different* worksheet. It's... getting pretty complicated, to say the least.

I'm afraid I don't have a specific question here, as I don't really know where to begin; I'm more looking for recommended reading on the subject. Does anyone have any resources they'd recommend? Books? Websites? Anything?

- David Prokopetz.

Search Through Multiple Worksheets
Hello people,

i'm not a programmer and i know only a few things about VB. My problem is this:

i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this:

Private Sub FindCB_Click() 'This is the Find Next button in my form

Dim sh As Worksheet
Dim rng As Range, firstAddress As String

For Each sh In ThisWorkbook.Worksheets

Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not rng Is Nothing Then

firstAddress = rng.Address


If Not rng Is Nothing Then

Application.Goto rng, True

End If

Set rng = sh.Cells.FindNext(rng)

Loop Until (rng.Address = firstAddress)

End If


So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel?

Thank you very much in advance!

Sum Same Cell In Multiple Worksheets
I need to sum the same cell in multiple worksheets into a total cell. The number of worksheets changes and I need to start the sum at the third sheet. So, sum cellXX in worksheet 3 through last worksheet. Ideas?

Vb, Excel And Multiple Worksheets
the workbook i've been using in my vb app has just been changed from having 1 worksheet to having many worksheets. the problem i have encountered is when i do my application.calculate not all worksheets get updated. is there a solution to this?

Arrays Made From Multiple Worksheets

I have been given significant assistance on this site before and am hoping for some more.

My problem is this I need to search 1 column in several different workbooks. I can navigate all the workbooks programatically just fine. I was hoping to use an array to speed things up. There is an enormous amount of inforamtion to sift through. I can build an array by going cell by cell though the column I need on each workbook, but this isn't much faster that how i am currently doing the process. Is there a way I can add to an array in big chunks. I can do the following on one worksheet, but can i add to it?

dim INFO() as Variant, rng as range

set rng = range(cells(1,9), cells(Activeworksheet.usedrange.rows.count,9)
Info = rng

Can I continue the process on all my desired worksheets to make one huge array? If so how.

One Million thankyous

Deleting Comments On Multiple Worksheets
Hi Everybody

I have 3 worksheets in a workbook. Some cells in one or more worksheets may have comments attached to them

Is there a way to delete the comments from all the cells in all the worksheets in the workbook using a single command rather than scanning all the used cells on all the worksheets and then delete them as we find them.

I am looking for something like so :-

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).select

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).select

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).select
selection.cells.interior.colorindex = xlNone
but don't seem to work.

Where am I going wrong?

Best regards

Deepak Agarwal

Hinding Rows In Multiple Worksheets
In order to hide a selection of rows in multiple (3) worksheets I used the following macro. It does not seem to function properly, and I can not figure out why. The only sheet in which the rows are properly hidden is the "MT" sheet, the hinding does not function in the two remaining sheets.

The rows which are selected for hiding, contain the word "obsolete". I know it ancient but it is for excel97.

Hopefully somebody has a suggestion.



Sub Obsolete()
' Obsolete Macro
' Hiding obsolete studies in all sheets

Application.ScreenUpdating = False

Sheets("General Information").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("General Information").Select
Dim r As Range: Set r = Range("z2:z200")

r.Offset(0, -1).FormulaR1C1 = "=RC[1]=""Obsolete"""
r.Rows().Hidden = r.Offset(0, -1).Value
r.Offset(0, -1).Delete

Sheets("Study Information").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("Study Information").Select
Dim s As Range: Set s = Range("z2:z200")

s.Offset(0, -1).FormulaR1C1 = "=RC[1]=""Obsolete"""
s.Rows().Hidden = s.Offset(0, -1).Value
s.Offset(0, -1).Delete

Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Dim t As Range: Set t = Range("z2:z200")

t.Offset(0, -1).FormulaR1C1 = "=RC[1]=""Obsolete"""
t.Rows().Hidden = t.Offset(0, -1).Value
t.Offset(0, -1).Delete

Application.ScreenUpdating = True

End Sub

Take One Worksheet && Create Multiple Worksheets
Hello Everyone,

I have a worksheet that contains customer information (i.e. sales, returns, rank, etc.), where each customer will appear on four separate rows, one for each sales quarter. This format comes off of our mainframe, so adapting the original file is out of the question.

I want to take each of these customers (and all four rows of data) and create a new worksheet for EACH customer, with their information, along with naming the worksheet with the customer code (a 6-digit number).

Here's an example layout of the original:

A1 B1 C1 D1
Code Qtr Sales Returns
123456 1 50 -10
123456 2 60 -11
123456 3 30 -3
123456 4 20 -5
555111 1 55 -1
555111 2 23 -12


Can someone offer some guidance as to how to handle the VBA to search through Column A until a change in the Customer Code, create a new worksheet with that Customer Code, along with the four rows of data, then progress on to create a new worksheet for the next Customer Code in Column A?

I know some VBA, so just some pointers on where/how to start would be greatly appreciated.



Working With Multiple Excel Worksheets
i have seven excel files in a folder.
i want to create an eight one, with first column from the first sheet, second from the second, and so on.....

how can i write a .vbs file to automatically do that.

Imran Sajwani
Illinois Institute of Technology

Opening Multiple .txt Files On Different Worksheets

I'm Using Excel 97.

I wonder if someone can help me or point me in the right direction.

I have a number of tab delimited .txt files in different directories. I need to open each one within the same workbook but with each on a different worksheet.

I found a thread that does the same as i need to do but i am not sure what should go instead of the Data_From_File part ? Also very annoyingly my help files do not seem to work so i cannot even check the correct syntax in there.

any help appreciated


Searching Through Multiple Worksheets Within A Workbook
Hello all and Happy Memorial day to you all

I'm trying to figure out how I can search through multiple woksheets within my workbook.

I have the user updating their input through a userform(2) which then searchs column b and c for certain criteria.

That part works great.

I just cant do the multiple worksheet search, can anybody assist me

here is my code so far


Option Explicit

Private Sub cmdRJFH_Click()
Dim iMonth As Integer
Dim jobname As String
Dim temp As Range
Dim temp2 As Range
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Set ws = Worksheets("MAY2008")
'check for a job name!
If Trim(Me.txtRLJname.Value) = "" Then
  MsgBox "Please enter the Job name that has been requested to be taken off hold!"
  Exit Sub
End If


Dim strVal1 As String
Dim strVal2 As String
Dim irow As Long
Dim m As Long

 'Get values of text boxes
strVal1 = Me.txtRLJname
strVal2 = Me.TextBox1

' Last used row
m = ws.Range("B" & Rows.Count).End(xlUp).Row
' Loop through the rows

For irow = 1 To m
 'Do we have a match?
    If ws.Range("B" & irow) = strVal1 And ws.Range("C" & irow) = strVal2 Then
   'If so, get out of loop
        Exit For
        End If
Next irow
  If ws.Range("B" & irow) = strVal1 And ws.Range("C" & irow) = strVal2 Then
       If Me.txtNop2.Value = "" Then
    MsgBox "Please enter the Requestor's Name!"
    End If
   ws.Cells(irow, 8).Value = Me.txtNop2.Value
  ws.Cells(irow, 9).Value = Date
 ws.Cells(irow, 10).Value = Time
ws.Cells(irow, 11).Value = Me.txtCOMments.Value
'clear the data
Me.txtRLJname.Value = ""
Me.txtNop2.Value = ""
Me.txtCOMments.Value = ""
Me.TextBox1.Value = ""
End If
If ws.Range("B" & irow) <> strVal1 And ws.Range("C" & irow) <> strVal2 Then GoTo ERRHNDLR
    MsgBox "Hobname and or Service desk ticket are not within the month of april"
'clear the data
Me.txtRLJname.Value = ""
Me.txtNop2.Value = ""
Me.txtCOMments.Value = ""
Me.TextBox1.Value = ""

End Sub

thank you

Importing Excel Into Access (multiple Worksheets)
i have written code that will import multiple excel worksheets from multiple excel files. but i have to name the specific worksheet im wanting to import from.

is there a way to import multiple excel worksheets without having to name the specific worksheet?

i have excel files that may have 1 or more worksheets and they will have different names.

i tried searching and did not find anything that would help.


Printing Multiple Worksheets With Same Printer Settings
Hi everybody! I am banging my head against the wall with some printing issues. The scenario is that I have a collection of worksheets within one workbook. Each of these worksheets is unique in that they are different lengths and have different information in the header and footer.

My goal is to give the user the print dialog box so they can specify printer settings (duplex, black and white, etc) and then be able to print all the worksheets with the same settings. If you choose "Entire Workbook" in the print dialog box it only prints the first worksheet with the settings the user chose (because Excel dispatches each as a separate print job).

I tried two possible workarounds, neither of which I got fully working. The first was to make a custom box that allowed the user to tell me their print settings, capture those and then loop through and automate the printing of each worksheet individually. The problem is that I can't figure out how to change the printer settings with VBA (they don't get recorded in a macro, I've tried that). Second scenario is to copy and paste all the worksheets into one big worksheet and insert HPageBreaks between them. This worked wonderfully for formatting; all the printer settings get applied to everything. But here the header (worksheet name) and footer (page numbers) information is not maintained.
If anyone has any ideas on how I can fix my two scenarios, or a new one that I haven't thought of I would LOVE to hear about it. My head hurts from thinking about this Thanks in advance!

Reading Excel File W/multiple Worksheets
Is VB capable of reading in an excel file that contains multiple worksheets? I need to externalize alot of data, and I don't want to have 10 or more text files, so I would rather have 1 excel file with 10 worksheets.


Plotting Chart With Data From Multiple Worksheets
I'm trying to plot a chart using data from more than one worksheet in Visual Basics using Excel language. When plotting, it ignores everything except the first worksheet, though, it will plot manually.

Can anyone solve this?

Running An Excel Macro On Multiple Worksheets
I have the following macro:

Code:Sub PSAPerfBreak2Macro()
' PSAPerfBreak2Macro Macro
' Macro recorded 9/11/2003 by VJ Occhino
Dim rval As String
Dim nval As String
Dim rng1 As String
Dim rng2 As String
Dim finr As String

   nval = ActiveCell.Value
   rval = InputBox("Enter text to replace:")
   finr = "g1:g500,n1:n500"
   If rval = "" Then
    MsgBox ("Canceled")
   Selection.Replace What:=rval, Replacement:=nval, LookAt:=xlPart, _
   SearchOrder:=xlByColumns, MatchCase:=False
   End If
End Sub

Currently, it updates the two ranges on the ACTIVE worksheet that I define in my variable "Finr". However, I would like the same "find & replace" macro to work in another range in another worksheet in the same workbook. Is there a way to define my finr as not just"g1:g500,n1:n500" in the current worksheet but a range in another worksheet as well?

Thanks in advance!!!

Apply Vb Macro To Ranges In Multiple Worksheets
I have the following macro:

Code:Sub PSAPerfBreak2Macro()
' PSAPerfBreak2Macro Macro
' Macro recorded 9/11/2003 by VJ Occhino
Dim rval As String
Dim nval As String
Dim rng1 As String
Dim rng2 As String
Dim finr As String

   nval = ActiveCell.Value
   rval = InputBox("Enter text to replace:")
   finr = "g1:g500,n1:n500"
   If rval = "" Then
    MsgBox ("Canceled")
   Selection.Replace What:=rval, Replacement:=nval, LookAt:=xlPart, _
   SearchOrder:=xlByColumns, MatchCase:=False
   End If
End Sub

Currently, it updates the two ranges on the ACTIVE worksheet that I define in my variable "Finr". However, I would like the same "find & replace" macro to work in another range in another worksheet in the same workbook. Is there a way to define my finr as not just"g1:g500,n1:n500" in the current worksheet but a range in another worksheet as well?

Thanks in advance!!!

Excel Macro - Formatting Multiple Worksheets

I need help with a macro I am trying to write. I have a macro that takes data dumped into Excel from a server database, and then splits the data into multiple worksheets based on the number of categories in the table and formats the cells. At the end, I then need to format the page margins and header & footer text. Right now the macro does this individually to each worksheet as it creates it, but it is bogging down the program. I figured it may, possibly, work faster if it does all formatting at the end. However, I can't find a way to do this. Manually, you can select all the worksheets and change them all at once using the Page Setup dialog box. I've tried doing this:

With ActiveSheet.PageSetup
     (Insert Formatting Here)
End With

The "Worksheets.Select" line does indeed select all of the worksheets. However, when the macro is run, it only formats the first worksheet.

Any ideas as to why I can do this manually, but not via macro?

Edited by - trumpet326 on 7/17/2003 7:51:52 PM

Updating Multiple Workbook/worksheets/cells W/new Formulas
I would like to update update multiple workbooks/worksheets/cells with new formulas to replace existing formulas. I will have a spreadsheet with a path, workbook/file name, worksheet name, cell name/address, new formula and old formula. I would like to loop through all of the files/workbooks/worksheets and change those cell formulas that have new formulas. Any help would be appreciated. I've done quite a bit of VBA work in Excel and could probably figure this out, but if someone has done this already I would be very grateful.



Cross-referencing Data In Multiple Excel Worksheets
I am very new to this, so please bear with me. I have two worksheets with an overlap of data between them. I want the second worksheet to update every time the first worksheet is changed, as well as have the updates appear in red bold. The problem is, the sheets aren't set up the same way, so I am trying to find a way to cross-reference columns and rows in each sheet until it finds matching column/row labels from sheet to sheet. Once found, I want it to replace the cell on sheet two with the information from the cell on sheet one. I hope I explained everything clearly. Please help! Thanks very much in advance.


I Need To Create A Macro / Button That Would SEARCH Multiple Worksheets And REPLACE

This is day one with trying the VB / Macro side of Excel, so please be patient.

I have a spreadsheet which contains approximately 7 Worksheets. I would like to be able to create a button (if possible) or macro that has a search built in(the search is predefined in the button) . Once clicked on, it would ask me for the replacement Data. It would search for the predefined information and change it to what is typed in when clicking the button or executing the macro.

So, my spread sheet is actually a template. Every time I open it, I am creating collection of data on a specific computer type, (say.. IBM ThinkPad). So throughout my spread sheet at the title blocks and graphs I placed text that says #MODELHERE (So I know this is where I want the model name to go). now I open the spreadsheet I want to press a button that will prompt me for the "MODEL NAME", I type in IBM THINKPAD. The code then goes out and searches for #MODELHERE and replaces it with IBM THINKPAD. I would like to do this not only for Model, but also for #NIC and #VER (for Network interface card and the other for NIC driver version).

So... now that I have bored you all.. it this possible?
Thanks so much!

Edited by - RBorgia on 11/7/2003 10:18:43 AM

Cant Run Thru All Worksheets
i have a workbook with around 5 worksheets.

1 is sheetA, which holds all the data extracted from the database
1 is sheetB, which help forms the other worksheets with its data.

the other worksheets are holding data according to their departments (which is one of the field on sheetB). ie. 1 dept takes up 1 worksheet.

Private Sub Highlight()
Dim sheetA As Worksheet, sheetB As Worksheet
Dim i As Long, k As Long
Dim isheet As Long
Dim name As String
Set sheetA = ActiveWorkbook.Sheets("Sheet1")

Application.ScreenUpdating = False
For isheet = 1 To ActiveWorkbook.Worksheets.Count
i = 5
k = 2
With Worksheets(isheet)
While Not IsEmpty(Worksheets(isheet).Cells(i, 2))
If sheetA.Cells(k, 3) = Worksheets(isheet).Cells(i, 2) Then
Worksheets(isheet).Cells(i, 5).NumberFormat = "[Red]-$#,##0.00"
k = k + 1
i = i + 1
End If
End With
Next isheet
Application.ScreenUpdating = True

End Sub
i wan it to be able to compare column B of all other worksheets wif column C of sheetA. if the values match, i wan the function to be able to change the numberformat of that particular row of data into "[Red]-$#,##0.00". but the problem is that if sheet 2 does not have any matching values, it will move onto sheet 3, which has matching values. After that when it moves onto sheet 4, which also has matching values, it is not able to change the numberformat. pls advise how to solve the problem anyone?

Run Macro On 100 Worksheets
I have an excelfile with 100 worksheets.
How can I run a macro on all these 100 worksheets automatically.

Copying Worksheets
Is it possible to copy an entire sheet, controls formula and all, to another worksheet without losing the same format as the original?

Targetting ALL Worksheets
I want to be able to target all worksheets and assign them all with the same property. Reading a previous post, I tried:

Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.DisplayGridlines = false

But that seems to give me an error Can someone correct this for me, as well as explaining the line:

For Each mySheet In Worksheets


Renaming Worksheets

I'm trying to rename my worksheets.
If you look at the sheet's properties in Visual Basic then you see a (Name) and another Name. I want to rename both in VB code to the same name. How do I do that, because the following code renames only one
sheetExample.Name = "NewName"

Can anyone help?


Worksheets In Excel
For some reason my insert worksheet option is not working in excel. Even I can't unhide worksheets that I have previously hidden. Some how anthying to do with worksheets is turned off. I can't figure out what's the reason. I can't even go to file/properties anymore. This is just in one of my excel files. All my other excel files and any new ones I'm creating are not causing this problem. Does anyone know why this is happening? Thank you,


Changing Worksheets
Is there a way to switch between different worksheets when using VB with excel? I have a workbook with 12 worksheets in it, each one has a different month on it, and I would like use a combobox to pick which worksheet to write to. Thanks for your help.

Pasting In Worksheets With VBA
I've been writing a fairly complex VBA / Excel 2000 application for a while and getting along fine, but suddenly I'm out of my depth!

Because Activating is a Bad Thing and seems to be getting me into other trouble I won't worry you with, I'm trying to cut and paste from one worksheet to another (working round the 256 char per cell limit issue) without changing the Active workbook. So I'm trying to change this code:

TempBook.Sheets("LL Rules Examples").Activate ' this works!
ThisWorkbook.Sheets("LL Rules Examples").Activate

to this (which fails!)

TempBook.Sheets("LL Rules Examples").Columns("AY:AY").Copy
Sheets("LL Rules Examples").Range("AY1:AY1").Paste

The error message is:

Run time error '438'
Object does not support this property or method

What's the correct version? TIA

Excel Worksheets
I have an application that will scan a table and sort the contents into an excel workbook. I need to be able to add sheets as I go along, but if the sheet does not exist then I need to create it, I have looked at scanning all the sheets but that can take time, is there a way of checking quickly if the sheet exists or not, maybe trying to activate it and if it does not activate use the error to try and create it. Any help would be appreciated.

Hiding Worksheets
Hi everyone. I am writing a VBA application and want to hide the underlying worksheet, or even the entire workbook. Unfortunately my code relies on getting index numbers of sheets within the workbook, and the index numbers seem to not take into account hidden sheets, which causes me problems. If a sheet has a particular index number, it will have a different number if one or more sheets are hidden. Is there any way that I can hide sheets but still search and manipulate sheets based on their index number?

Excel Worksheets
Somehow im posting a lot round these days...
So the question: How can you put values of textboxes into excel worksheets?? And print the sheet afterwards??


Using Two Separate Worksheets
I would like to save data to two separate worksheets. In my present program I am saving to only one with:
Set objWsheet = mobjExcel.ActiveWorkbook.ActiveSheet

What do I change to save data to two different worksheets of the same workbook or do I need to use two different workbooks? I so how is that handled?

Macro Across All Worksheets
Hi all.

I'm trying to get a macro I've written to run across all the worksheets in a workbook. Everything I have tried however results in the macro being run on the same worksheet and I am now in need of some advice as to how to get this to run.

If anyone knows of a way to do this I would be most greatful.

Many Worksheets To One Workbook
Hello there,

I need to copy many unique worksheets into one workbook. These worksheets are one to each file (only one sheet in the unique workbook file). I would like to be able to select a number of files (workbooks with only one sheet) and merge them into one file (workbook).

Any help would be immensely appreciated!


Combobox In Worksheets..
Can anyone please advise me on how to prevent user from moving or deleting comboboxes or textboxes placed in worksheets?


Iterate Through The Worksheets
Thank you for reading this. This is my first time using VBA in Excel.
Below is the code that I am using to change 2 cells on 9 worksheets in a workbook. This works for now, but if they add a worksheet to the template later this won't work for them.

Private Sub Workbook_Open()
Dim i As Integer
Dim mDate As String
Dim yDate As String
mDate = Format(Date, "mmmm")
yDate = Format(Date, "yyyy")

Sheet1.Range("A2") = mDate
Sheet1.Range("A3") = Sheet1.Name & " for " & mDate & " " & yDate
Sheet2.Range("A2") = mDate
Sheet2.Range("A3") = Sheet2.Name & " for " & mDate & " " & yDate
Sheet3.Range("A2") = mDate
Sheet3.Range("A3") = Sheet3.Name & " for " & mDate & " " & yDate
Sheet4.Range("A2") = mDate
Sheet4.Range("A3") = Sheet4.Name & " for " & mDate & " " & yDate
Sheet5.Range("A2") = mDate
Sheet5.Range("A3") = Sheet5.Name & " for " & mDate & " " & yDate
Sheet6.Range("A2") = mDate
Sheet6.Range("A3") = Sheet6.Name & " for " & mDate & " " & yDate
Sheet7.Range("A2") = mDate
Sheet7.Range("A3") = Sheet7.Name & " for " & mDate & " " & yDate
Sheet8.Range("A2") = mDate
Sheet8.Range("A3") = Sheet8.Name & " for " & mDate & " " & yDate
Sheet9.Range("A2") = mDate
Sheet9.Range("A3") = Sheet9.Name & " for " & mDate & " " & yDate
ThisWorkbook.SaveAs "J:MyDirMyFolder" & mDate & "Report"

End Sub

I'm sure there is a way to iterate(sp?) through the worksheets with a For...Next loop, but I tried:

dim sh as sheets
for each sh in thisworkbook
sh.range("A2")= format(date,"mmmm")
next sh
as well as

dim i as integer
for i=1 to worksheets.count
next i
and neither worked.

Thank you in advance,

Been away for a while, so my memory is a little rusty.

I have this little program where I need to print a couple of worksheets in a workbook.
I thought that I could just declare a new Worksheets collection, and add my sheets to this collection, however it does not work like I thought:

Sub PrintAll()
Dim WS As New Worksheet
Dim SheetsforPrint As Worksheets

SheetsforPrint.Add (ThisWorkbook.Worksheets("Process Power"))

What is the proper syntax for adding a sheet to the worksheets collection?

Deleting Worksheets
I have little knowledge of VB within excel, and my level of skill is tinkering with existing macros and I'm getting out of my depth

I wonder if someone can give me some pointers with a macro I need, but is out of my skill range. I'd like a macro that deletes worksheets except for three named ones ....

Clearing Worksheets
is there any code that can clear a worksheet, without deleting it.

i need something that not only clears text but also chart objects as well.


Why? {Searching All Worksheets}
Hi all,

I'm trying to pinpoint specific data, which could appear on any worksheet in an Excel file... It worked yesterday (I know, famous last words).

For Each wk In Worksheets

intWkSht_COUNT = intWkSht_COUNT + 1
strWkSht_Name = Range("a1").Cells(1, 1).Parent.Name

' Sheets(strWkSht_Name).Select
' With Worksheets(strWkSht_Name)
' strCell_A_HOLD = .Cells(1, 1).Value
' If strCell_A_HOLD = "Admit Date" Then
' strCell_A_HOLD = .Cells(1, 44).Value
' If strCell_A_HOLD = "MRN" Then
' blnWkSht_FOUND = True
' Exit For
' End If
' End If
' End With

Next wk
My problem now is that the "strWkSht_Name" variable always remains as "Sheet1", no matter what the names of subsequent worksheets are within the file...

Any ideas?


Moving Between Different Worksheets
For my excel file, i have different worksheets. Would like to ask if its possible to lock it so that users can only go to the other worksheet only by pressing a button on the excel worksheet.

Picts In Worksheets
I'm adding picts to a worksheet (via wksht.Shapes.AddPicture(...
AddPicture lets you set the destination rectangle. I'd like to preserve the original aspect ratio. Is there a way to go from jpeg to getting the actual bounding rectangle of the picture?

Copyright 2005-08, All rights reserved