PDA

View Full Version : Solved: Copy worksheet to new workbook and more



tx7399
04-26-2012, 10:06 PM
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.

Teeroy
04-27-2012, 06:22 AM
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.

sassora
04-28-2012, 04:22 AM
Could you post the Excel file?

thair younis
04-30-2012, 12:20 PM
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 ?

Teeroy
05-01-2012, 01:32 AM
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.


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


Good luck.

tx7399
05-02-2012, 05:07 PM
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.:beerchug: