Results 1 to 2 of 2

Create worksheet in masterfile and add content below existing content

Threaded View

  1. #1
    Registered User
    Join Date
    06-01-2004
    Posts
    11

    Create worksheet in masterfile and add content below existing content

    Context: each employee uses the same worksheet stored on a network drive to fill in their daily activities. I want to capture these activities into one masterfile.
    I have found some very neat code to copy the content of a worksheet into a seperate masterfile.
    Now I want to:
    1. create a new sheet in the masterfile (if it doesn't exist) based on the value of cell B7 in the worksheet (a date)
    2. in the new sheet of step 1, find the last row that is filled in, leave a row blank and then copy the content of the worksheet into that new sheet (e.g. in the workbook Tom presses CommandButton9 first and copies his activities to A1:M11 in the new sheet of the masterfile, then Frank presses CommandButton9 and copies his activities to A13:M23 etcetera)
    How can I accomplish that?
    See here for my workbook

    Private Sub CommandButton9_Click()
        Dim IntSht As Worksheet
        Dim IntBk As Workbook
        Dim ExtBk As Workbook
        Dim ExtFile As String
         
        Set IntBk = ActiveWorkbook
        Set IntSht = IntBk.ActiveSheet
        ExtFile = "N:\Uren\Master Workbook.xls"
        'Example of dynamic filename: wbName = "NAV SHEET " & Format(Date, "ddmmyy") & ".xls"
        If Dir(ExtFile) <> "" Then
        Else
            ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Please Select A File")
        End If
        On Error Resume Next
        Set ExtBk = Workbooks(Dir(ExtFile))
        On Error GoTo 0
        If ExtBk Is Nothing Then
            Application.Workbooks.Open ExtFile
            Set ExtBk = Workbooks(Dir(ExtFile))
        End If
    
    'Copy Sheet content to new workbook
    'Original line:    IntBk.IntSht.Range("DataDump").Copy ExtBk.Worksheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
        IntSht.Range("A11:M21").Copy ExtBk.Worksheets("Sheet1").Range("A1:M11")
        Application.DisplayAlerts = False
        ExtBk.Save
        ExtBk.Close
        Application.DisplayAlerts = True
        Call CreateEmail
        CommandButton9.Enabled = False
    End Sub
    This code should be integrated above to create a sheet if it doesn't exist
    'Create sheetname
    Dim strSheetName As String
    Dim wsTest As Worksheet
     
    Set wsTest = Nothing
    On Error Resume Next
    Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
    On Error GoTo 0
     
    If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
    MsgBox "Sheet " & strSheetName & " created."
    End If
     
    'Create worksheet with "Bob" if it doesn't exist.
    CreateSheet "Bob"
    Last edited by Hond70; 10-19-2011 at 06:36 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1