dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: VBA Excel Summary Page

  1. #1
    Join Date
    Dec 2009
    Posts
    3

    VBA Excel Summary Page

    hello,I am new to vba and I would like to know if can search thru the worksheets, copy and then paste in a new worksheet on a column
    Code:
    Public Sub CopyandPaste()
    Dim ws As Worksheet
    
    ' Delete all data from Summary Page
    Worksheets("1a").UsedRange.Delete
    
    ' Copy each worksheet in file and paste to Summary Page
    
    For Each ws In Worksheets
         If ws.Name <> "1a" Then
              ws.Range("a10").Copy
              Worksheets("1a").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
        End If
    Next ws
    
    End Sub
    
    can be inserted?:
    Cells.Find(What:="word", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Selection.Copy
    The "word" I am searching on each page is part of a sentence.
    Thank you,
    Lo
    Last edited by Hack; 12-04-2009 at 01:11 PM. Reason: Added Code Tags And Indenting

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Welcome to DevX

    Will the word you are searching for always be in the same place or will you have to search the entire contents of the sheet (all cells all rows) to find it?

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    Thank you !
    The "word" is not in the same place on the sheets but it is on the same column.
    Thank you for you help!

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Ok, well try something like this (you will need to play around with it a bit)
    Code:
    Dim rngFound As Range
    Dim myFind As String
    
    myFind = "The word you are looking for"
    
    With YourWorkBookName.Sheets("Sheet2")
            Set rngFound = .Columns(1).Find( _
              What:=myFind, _
              After:=.Cells(1, 1), _
              LookIn:=xlValues, _
              LookAt:=xlPart, _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlNext, _
              MatchCase:=False, _
              SearchFormat:=False)
                    If rngFound Is Nothing Then
                        MsgBox "Nothing matching was found.", vbOKOnly + vbInformation, "Provider Not Found"
                        YourWorkBookName.Close
                        Exit Sub
                    Else
                        'if it reaches here, it did find a match so do whatever you need to
                    End If

  5. #5
    Join Date
    Dec 2009
    Posts
    3
    Thank you for all your help.I am really new to this.
    I will give you a link to a part of my database(which has 1000 sheets):
    http://www.mediafire.com/file/djywwy2lmzn/109.xls

    What I need to extract there is :"ISBN" , "publisher" and numbers from the column under"ar2".
    All the words and values we search for changing the rows but not the columns.
    Thank you I really appreciate all the help.

Similar Threads

  1. can't copy from Word document VBA excel form
    By thegeeber in forum VB Classic
    Replies: 11
    Last Post: 04-03-2009, 11:52 PM
  2. Replies: 0
    Last Post: 01-06-2009, 10:44 AM
  3. Excel VBA - Multi Page Control
    By Hack in forum VB Classic
    Replies: 3
    Last Post: 10-29-2008, 08:59 AM
  4. Excel 2000 VBA button question
    By kowalsky in forum VB Classic
    Replies: 3
    Last Post: 04-04-2008, 07:34 AM
  5. Report Designer 6.0 summary on same page
    By Simon Sellick in forum Database
    Replies: 0
    Last Post: 05-17-2002, 12:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center