Consulting

Results 1 to 6 of 6

Thread: Solved: Copy worksheet to new workbook and more

  1. #1
    VBAX Regular
    Joined
    Apr 2012
    Posts
    32
    Location

    Solved: Copy worksheet to new workbook and more

    Hi Everyone,

    Newbie with his first post!

    I have a workbook (Duty_Logs.xlsm) which contains eleven worksheets with four columns (DATE, TIME, OPERATOR, and COMMENTS). From a command button placed on
    each worksheet, I need to call a procedure that does the following:
    -merges the eleven worksheets into a new worksheet named "Passdown"
    -sorts the new worksheet named "Passdown" on the first two columns (Date and Time), both in ascending order
    -filters "Passwdown" for a user input date
    -the new worksheet "Passdown" should now display four columns (DATE, TIME, OPERATOR, and COMMENTS) for just the date as input by the user.
    Something like this:
    DATE TIME OPERATOR COMMENTS
    4/25/12 0100 Paul variable comments entered here
    4/25/12 1130 Ted variable comments entered here
    4/25/12 1800 Bob variable comments entered here
    4/25/12 2130 Jim variable comments entered here
    -copy this worksheet "Passdown" into a new workbook saved as "Passdown.xlxs" saved in the same folder as Duty_Logs.xlsm
    -open Passdown.xlsx in a new instance of Excel
    -delete the merge sheet ""Passdown" from Duty_Logs.xlsm
    -return the user to the worksheet from which the procedure was called via one of the eleven command buttons
    I have a hodge podge of code that gets me through merging the eleven sheets into a new worksheet in the original workbook, but I am really confused
    after that.
    Any help would be greatly appreciated.

  2. #2
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    Hi tx7399,

    I've done something similar but can't get the code at the moment (it's at work). To simplify your steps you should re-order the operations.

    1. Use workbooks.add method to create new workbook.
    2. Rename "sheet1" of new workbook to "Passdown"
    3. Copy data from eleven sheets directly to "Passdown" in the new workbook.
    4. Either filter or delete irrelevant data based upon the user input date.
    5. Save the file.
    6. Return the user to the original location.

    If this doesn't help I'll find some code examples in a couple of days when I get back.

  3. #3
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    262
    Location
    Could you post the Excel file?

  4. #4
    Quote Originally Posted by Teeroy
    Hi tx7399,

    I've done something similar but can't get the code at the moment (it's at work). To simplify your steps you should re-order the operations.

    1. Use workbooks.add method to create new workbook.
    2. Rename "sheet1" of new workbook to "Passdown"
    3. Copy data from eleven sheets directly to "Passdown" in the new workbook.
    4. Either filter or delete irrelevant data based upon the user input date.
    5. Save the file.
    6. Return the user to the original location.

    If this doesn't help I'll find some code examples in a couple of days when I get back.
    We waiting your code example ?

  5. #5
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    Hi tx7399,

    I've cobbled something together quickly so it may not be too efficient and it doesn't have any data validation but it works.

    [vba]
    Sub Save_DutyLog()

    Dim numberOpenWorkbooks As Integer
    Dim CurrentBook As Workbook
    Dim CurrentSheet As Worksheet
    Dim NewBook As Workbook
    Dim rw As Range
    Dim dest As Range
    Dim testrng As Range

    Dim get_date_text As String
    Dim get_date As Date

    'Set the location of the starting point

    Set CurrentBook = ActiveWorkbook
    Set CurrentSheet = ActiveSheet

    'count original number of open workbooks
    numberOpenWorkbooks = Workbooks.Count

    'Create new workbook
    Application.Workbooks.Add

    'set the newBook as the latest member of the Workbooks collection
    Set NewBook = Application.Workbooks(numberOpenWorkbooks + 1)

    'rename the 1st sheet of NewBook to Passdown
    NewBook.Sheets(1).Name = "Passdown"

    'get date to filter for
    get_date_text = InputBox("Input the date to filter for")
    'convert string to date
    get_date = CDate(get_date_text)


    'set data destination
    Set dest = NewBook.Sheets("Passdown").Range("a2")

    'return to source workbook
    CurrentBook.Activate

    For Each Sheet In Sheets
    'set a data range in each sheet to test for the input date
    Set testrng = Sheet.Range("A2", Sheet.Range("A" & Sheet.Rows.Count).End(xlUp))
    For Each rw In testrng
    'test for date and copy to ne workbook
    If rw.Value = get_date Then
    Range(rw, rw.Offset(0, 3)).Copy Destination:=dest
    Set dest = dest.Offset(1, 0)
    End If
    Next
    Next
    CurrentSheet.Activate
    End Sub
    [/vba]

    Good luck.
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

  6. #6
    VBAX Regular
    Joined
    Apr 2012
    Posts
    32
    Location

    SOLVED: Copy worksheets to new workbook

    Thanks Teeroy,

    Your code works great! I placed it in a module as a function which can be called from a button on any of the eleven sheets. The Sheet Name gets passed to the function so that user can return to his worksheet after viewing "Passdown" which your code produced in a new workbook.

    I really appreciate your help.

Posting Permissions

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