Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Select Sheets Using Vba Whose Name Contain Specific Word (word From Variable)

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Select sheets using VBA that contain specific word (word from variable).

I want to select all sheets in active workbook whose name contains a specific word. I getting such specific word from variable.

Thanks
Amir
View Answers     

Similar Excel Tutorials

Excel Prank - Automatically Change User-Entered Text
Use AutoCorrect to change the text a user enters into something funny. This is a great little prank to use on co-wo ...
Determine if Cells Contain a Specific Value in Excel
Find if a cell or range of cells contains a specific value in Excel. This method can be used on individual cells wh ...
Get the First Word from a Cell in Excel
How to use a formula to get the first word from a cell in Excel. This works for a single cell and an entire list or ...
Change Specific Text within a Cell in Excel
Change or replace text in a cell with other text - you can replace a single character, numbers, letters, etc. This ...
Simple Excel Function to Extract a Word or Text from a Cell
Excel function that makes it easy to extract a word or text from a cell in Excel. This is a single function! It doe ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...

Helpful Excel Macros

Open Microsoft Word from Excel
- This free macro will open the Microsoft Word program on your computer. You do need to have this program first. This wi
Open a Word Document from Excel
- This Microsoft Excel macro will open a Microsoft Word document from excel. With this macro, you can open any word docume
Extract a Word from a Sentence / Cell in Excel with this UDF - Allows for a User-Defined Delimiter
- Extract whole words from a cell or sentence in Excel with this UDF. This allows you to specify which word from a cell y
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Capitalize the First Letter of Every Word in a Cell
- This macro will make the text of any selected cell in excel proper case. This means that the first letter of any word in

Similar Topics







Excel Gurus,

I have a dropdown list in cell A1 with four possible choices:
Statement 1: "50 gallons - White"
Statement 2: "50 gallons - Black"
Statement 3: "25 gallons - Green"
Statement 4: "25 gallons - Blue"

In cell B1 I need to evaluate what was choosen in cell A1 to determine what action to take.

So, how do I tell cell B1 "if A1 does contain the word/number "25" AND does NOT contain the word "Blue" then make B1 equal to cell C1?"

I can get this to work to verify if the cell contains a specific word, but I can't figure out how to combine the AND statement with the ISNUMBER, SEARCH, and NOT statements to also verify if the same cell does NOT contain a certain word. Any help would be greatly appreciated.

Thanks, jomilo


Okay, here goes:
I have converted 1092 PDF pages (purchase orders) into an Excel database. Because of the layout of the PDF it does not quite translate into a proper table for the various column needs.
In an effort to pull just the specific information I need from each purchase order I will need to:


1. Search the entire worksheet for anytime a specific word is found; for example "Total"
2. Return the vlaue from an Offset cell with that specific word "Total"
3. Have each of these values in their own column so I can obtain a Grand Total.

Sounds like it should be easy in theory but, I am fast learning how much of a novice I really am. Any suggestions or solutions would be greatly appreciated.


Hi All,

I have found a bit of code ( thanks to whoever orginally wrote it) that will copy a chart from Excel and place it in Word, however I for the life of me cannot work out how to re-size the picture to say 88%. I'll admit I am no expert when it comes to VBA, so I was hoping someone could tweak this code for me:
****************
Sub Customers()
'You will need to select Microsoft Word 12.0 under Tools and then References
Dim wdApp As Word.Application

'Copy Chart
Sheets("Area Data").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Copy

On Error Resume Next
'Try to establish link to open instance of Word
Set wdApp = GetObject(, "Word.Application")

'If this fails, open Word
If wdApp Is Nothing Then
Set wdApp = GetObject("", "Word.Application")
End If
On Error GoTo 0

With wdApp
'Add new document
.Documents.Add
'Make Word visible
.Visible = True
End With
With wdApp.Selection

'Go to end of document and insert paragraph
.PageSetup.Orientation = wdOrientLandscape
.EndKey Unit:=wdStory
.Font.Bold = wdToggle
.Font.Size = 18
.TypeText Text:="Title of Page to go here"
.Font.Size = 10
.TypeParagraph
'Paste Chart
.PasteSpecial , Link:=False, DataType:=14, _
DisplayAsIcon:=False
End With
'Release object variable
Set wdApp = Nothing
End Sub
**********************************

Hopefully someone can help me.

Cheers
Haydn


Hello Everyone!

I'm having trouble and was wondering if you could help.

Using Excel 2007.

I have 10 .xls Files, each with 4 FULL spreadsheets, with over 2GB of data.
Was wondering if there is way to copy certain rows that contain specific keywords.

For example: all rows that have the word "green" in them and all rows that have the word "blue" and so on.

I tries using search function (CTRL+F) and used "Find All" option. It did find all the occurrences. When I selected all the findings it limited selections to specific CELLs now the ROWs.

I was wondering if there is a way to select ROWs with specific KEYWORDS in them.

Thank you!


I have a user form that enters information into a data sheet but also need to enter some of the inputs into a Word document. The word document is about three pages long, but I only need to add certain inputs into certain parts of the Word document. Is it possible to open a specific word document from my desktop and add userform inputs into certain locations in the document? What would the code look like?

Any help is greatly appreciated. Thanks in advance.


I have a large mailing of about 65,000 pieces. I need to print variable
addresses and other variable data onto the forms. So, I basically need to
create a template in Microsoft word and import variable data (such as unique
address and account numbers) onto the form. I will then need to store the
data in a standard file type to send to the printers.



I've seen many examples of finding and replacing a specific text string. However, I want to find a word within a cell, then replace the entire cell with another word.

The searched cells may contain more than one word beyond the one I'm looking for.

I have this:

Sub FindData1ReplaceData2()

ActiveSheet.Activate
Cells.Replace What:="A", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False


End Sub

My cell may contain 'abcd'. This routine just replaces the 'a' with 'b' and the cell becomes 'bbcd'. I only need 'b' in the cell.

Seems pretty easy, but can't find the solution...


I have an existing Word document with three charts in it.
I have an Excel spreadsheet with a VBA Macro that does some calculations and assigns the results to variables.
I want to update the chart data in Word with the values of the variable in excel.
This code correctly locates the second and third charts but puts the same variables into the cells for both charts:
VB:

Dim objShape As InlineShape 
For Each objShape In ActiveDocument.InlineShapes 
    If objShape.HasChart Then 
        objShape.Chart.ChartData.Activate 
        Range("b2").Value = variable4 
        Range("b3").Value = variable5 
    End If 
Next 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



First problem is, it completely skips over the first chart in the Word document so I assume the argument objShape.HasChart = False for some reason.
Second problem is, it assigns values for variable4 and variable5 to the second and third charts in the word document. I want to assign variable1,2,3 to the first chart, variable4 and variable5 to the second chart and variable6 and variable 7 to the third chart.

Another line of thinking for me was to forget about updating the existing charts and just insert new charts based on the various variable data. I can not seem to figure out how to place a chart at a very specific location in the Word document. I tried selecting a pre-positioned bookmark, then adding the chart but it just put the chart at the beginning of the Word document.
VB:

WD.ActiveDocument.Bookmarks("Chart").Select 
Set RFactorChart = WD.ActiveDocument.Shapes.AddChart.Chart 
Set chartWorkSheet = RFactorChart.ChartData.Workbook.Worksheets(1) 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



For the love of God, please help me. I'm already bald or I would be pulling my hair out!

Hello:

My Column D contains many words in many rows. I would like to add in either VB or Conditional Formatting that would change the specific word "Update" to Bold and Red when it is typed anywhere in that column.

Thanks!
Nikki


Hi,

Is there an easy way to automatically select all the cells in a worksheet that contain a specific word?

I want to be able to select all of them and then move all of them in one shot.

Conditional formatting highlights the cells but I can't figure out how to select only those highlighted cells.

Thanks.


Hi,
I have a column of text describing various products. The word 'type' is always included in the description, e.g. "electronic gizmo type 323". I want to split the column to isolate everything including and after the word 'type'. If possible, non-code please.

Thanks,
Doug.


Hello Everyone,

I've been working on this one for the past few days. I've found some useful threads here but I'm still stuck.

I'm copying a range of cells from excel and pasting into the active word document as an enhanced metafile. I would like to be able to resize the pasted picture in Word with the excel macro.

I've tried to use .selection.InLineShapes(1).height = 577 but keep getting an error message "Run-time error '5941':
The requested member of the collection does not exist."

I have the Word Object Library referenced. My code is as follows:

Sub PasteToWord()

Dim WDapp As Word.Application
Dim WDdoc As Word.Document

Set WDapp = GetObject(, "word.application")
Set WDdoc = WDapp.ActiveDocument

Sheets("TDS 2000").Select
ActiveSheet.Range("a1:h65").Copy
With WDapp
.Selection.PasteSpecial link:=False, DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False
.Selection.InlineShapes(1).Height = 577 '

I have an Excel VBA project which needs to extract some info from a table in a Word document. I can open the Word doc ok, but how can I access the table? It's a one-row table with ten cells across, and I want to 'tab' along it extracting the value of each cell.

Problem is the Excel VBA doesn't seem to have the instructions for it. It doesn't understand the Word VBA instructions for doing that (eg Selection.MoveRight Unit:=wdCell) In fact anything I try to select from the table using the references to the Word doc which are set up when it is opened actually give me data from the currently active Excel sheet .

An alternative might be to copy the whole table and paste it into a spare place in my Excel workbook somewhere and analyse it there.

Is there any way of doing either of these things? I realise this is an "Excel" forum, but I'm sure there are experts on here who know about these things.




Hi everyone,

I am using MS Excel 2007 and MS Word 2007. I have a workbook with a number of named cells. I have a word document named with a number of bookmarks. The names in excel match the bookmarks in word. I need to export the values in the excel named cells to populate the bookmarks in the word document and then save the word document as a new file with a name based on the values of one of the bookmarks.

The name/location of the excel workbook will always be different whereas the name/location of the word document will not change.

Some of the names/bookmarks need to be formatted in a specific manner e.g. the date need to be in the "dd mmmm yyyy" format, while dollar values need to be "$#,##0".

I'm very new to VBA so dont really know where to go from here, so any help would be most appreciated!

Hello,

I am trying to insert a Word Object into Excel 2007. I go to the Insert tab, then select Object under the Text grouping. From the pop-up window I select Microsoft Office Word Document, click OK and then I get a small Word browser inserted into the Excel document with a new menu bar at the top. I click File at the top, then Open to open the word document I want to Insert. I Select the file, Click OK, and then get the error: " "FileName".docx : file format is not valid ". I have tried other Word files, as well as the older .doc type files, nothing seems to work. What am I doing wrong? Thanks.


Hi All

Wonder if you can help. I know how to open a word document in through Excel VBA. Could you advise on the could that would allow me to select the fine I want to open from a specific directory?

Regards,


Am trying to open, print and close embedded objects as part of a macro to print all of workbook including embedded files. I have the following which works ok if the embedded documents are only word files but I have been unable to adapt it to print PDF files. Is anybody able to offer any ideas??

Sub testprint()
Dim ChartList As Integer
Dim X As Integer
' Variable chartlist stores a count of all embedded charts.
ChartList = ActiveSheet.OLEObjects.Count

' Increments the counter variable 'X' in a loop.
For X = 1 To ChartList
' Selects the shape.
ActiveSheet.OLEObjects(X).Select
Selection.Verb Verb:=xlPrimary
' Makes chart active.
ActiveSheet.OLEObjects(X).Activate
' Prints one copy of active chart.
Set WordApp = GetObject(, "Word.Application")
WordApp.Activedocument.PrintOut
Next
WordApp.Quit
End Sub


Hi Everyone,

I need a formula to return any word containing a dash "-" and only that word. The space before and after defines the beginning and end of the word. The word can be either letters or numbers or any combination.

For example,

Text: Violinist Anna-Sophie Mutter performed the Mendelssohn Concerto.

Result: Anna-Sophie

Thanks so much!!!


Is it possible to delete all columns that contain a specific word?

I am trying to create a macro for it, but I'm not having any luck at all.


I am trying to put together a calendar events list for my organization that
will be publicized in the local press. I have set up a web page that allows
folks to input specific information about their event which I can then
download to an Excel spreadsheet. From there I want to be able to import the
data (what, when, where, etc.) into a Word document. Copy and paste takes way
too much time and I don't know a thing about macros. How can I create one
Word template that includes data fields that links to specific cells in
Excel? Mail Merge would seem to be the obvious answer however I am not trying
to create several form letters, I am trying to create one document that lists
several different events. Any help in layman's terms is greatly appreciated!



Hi All,
I am trying to select a text box in a macro so I can select the text in it to copy to a different location.
My question is when recording a macro I cannot select the text box as I cannot click on it so how do I do this please?

Thanks for any help

sorry forgot to say my word is MS Word 2000


Hi everyone, im try to build a macro that will find a word within a sentence that is in a cell. i.e.

A1="store number 566" I need the macro to select that cell (and stop). After that I will insert the rest of the code to copy and paste a larger area from that point, into another worksheet.

Thanks everyone in advance for any help.

sd


I'm developing an application that pastes a large number of Excel 2007 charts into a Microsoft Word 2007 document (each chart replaces a specific Word bookmark). After pasting each Excel chart into Word, I execute a command to re-size the chart.

I'm having a frustrating time as the macro seems to re-size the chart correctly most of the time. However, there are numerous occasions where the chart does not get resized at all--not even a default re-sizing to the document margins that usually happens when you paste a large chart into a Word by hand.

And here's the kicker--the offending charts seem to size perfectly fine when I manually step through the code.

Has anyone experienced anything like this? Any ideas what I might try? I tried inserting a Excel VBA delay before and after pasting, but that didn't help.

For whatever it's worth, here's a rough cut of my code (dWidth is a variable that represents the desired width of the chart, about 6 inches on the page):

Code:

 
Sub InsertChart(ByVal sBookMark As String, ByVal sChartType As String, ByVal dWidth As Double)
  Sheets(sChartType).ChartArea.Copy
  wrdDoc.Bookmarks(sBookMark).Range.Select
  wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
     Placement:=wdInLine, DisplayAsIcon:=False
  wrdApp.Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
  wrdApp.Selection.InlineShapes(1).LockAspectRatio = msoTrue
  wrdApp.Selection.InlineShapes(1).Width = Int(dWidth * iPointsPerInch)
End Sub


Thanks for any help you can provide me.


I'm using the following code to copy Range("A1:H59") and place it into a word document for forwarding by email.

The code works well, except that the default margins in the word Doc mean that eveything comes out too small. (I think that my work is being "Scaled To Fit")

How do I manually set the margins in Word from Excel VBA code?

How for example do I set all the margins to be 0.7mm?

Many thanks

Mike


Sub WordEmail_Click()
Range("A7").Select
Dim wdApp As Object
Dim wd As Object

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wd = wdApp.Documents.Add

wdApp.Visible = True

Range("A1:H59").CopyPicture xlScreen, xlPicture
wd.Range.Paste

Application.DisplayAlerts = False


End Sub


I have a macro that I am using to generate word documents. Now I need to send a back space to word due to a spacing issue. I have tried vbback but all I got was a square character in my word doc. Does anyone know of a way around this.