Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to copy sheets (excel 2003)

    Hello Everyone,
    I have a coding that someone in this forum was kind enough to write for me. It does everything i need it to do, but now I have to design the code to process for each file. I need to take each sheet and copy it to the file where the macro resides (in this case the file is name is "summary"). I don't care in what arrangement they go. This command will go below the" 'process each" command line item Any help would be great.
    Option Explicit
    Sub FileProcessingExample()
    'Variable Definition
    Dim FilesToOpen
    Dim iFileCount As Integer
    Dim x As Integer

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    'Get files to work with
    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
    MultiSelect:=True)

    'Quit if NO files are selected
    If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
    End If

    'Act on each file
    iFileCount = UBound(FilesToOpen)
    x = 1
    While x <= iFileCount
    Workbooks.Open FileName:=FilesToOpen(x)

    'Process each
    With ActiveWorkbook
    'add your code here to work with each workbook
    This is where the coding would go. Basically I need to copy each sheet in the files it process into a Workbook call "Summary" and this is where the macro will reside too
    End With

    'Close workbook
    ActiveWorkbook.Close SaveChanges:=False

    'Get next file
    x = x + 1
    Wend

    'Give a message saying you are done
    If iFileCount = 1 Then
    MsgBox "1 File was processed"
    Else
    MsgBox iFileCount & " Files were processed"
    End If

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to copy sheets (excel 2003)

    Try this:

    ...
    With ActiveWorkbook
    .Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
    End With
    ...

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to copy sheets (excel 2003)

    Thanks Hans,
    It looks like it might work, but as I process it I get the following error (see attached). Since the Summary Sheet is a blank sheet, I don't know why it would say that error. In addition, the file does not save the changes and I think this happens because of the command after "process each where it says puts the command to save=false. I am assuming that the "summary" file becomes the activeworkbook. Any help would be great.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to copy sheets (excel 2003)

    You can avoid the problem of what exactly the active workbook is by defining a variable of type Workbook and setting it to the workbook being opened. That way, you can refer to the variable instead of to the active workbook.

    Sub FileProcessingExample()
    'Variable Definition
    Dim FilesToOpen
    Dim iFileCount As Integer
    Dim x As Integer
    Dim wbk As Workbook

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    'Get files to work with
    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
    MultiSelect:=True)

    'Quit if NO files are selected
    If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
    End If

    'Act on each file
    iFileCount = UBound(FilesToOpen)
    x = 1
    Do While x <= iFileCount
    Set wbk = Workbooks.Open(Filename:=FilesToOpen(x))

    'Process each
    wbk.Sheets("1-15 1st Half").Copy Before:=Workbooks("book1").Sheets(1)

    'Close workbook
    wbk.Close SaveChanges:=False

    'Get next file
    x = x + 1
    Loop

    'Give a message saying you are done
    If iFileCount = 1 Then
    MsgBox "1 File was processed"
    Else
    MsgBox iFileCount & " Files were processed"
    End If

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub

    By the way, I don't see "Summary" mentioned anywhere in this version of the code.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to copy sheets (excel 2003)

    Thank you Hans. The coding above work like charmed. I just change the workbooks("book1") to workbooks('"summary"). Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •