Consulting

Results 1 to 2 of 2

Thread: Creating new folders and file names using data from sheets in Excel 2016 Mac

  1. #1
    VBAX Newbie
    Joined
    Oct 2016
    Posts
    2
    Location

    Creating new folders and file names using data from sheets in Excel 2016 Mac

    Hi
    Looking for assistance for the changes in syntax from 2011 to 2016 mac for creating a new folder and then a save as version of a file with data that is firstly entered into a user form that loads into a spreadsheet.

    Thank you in advance.

    Dylan

  2. #2
    VBAX Newbie
    Joined
    Oct 2016
    Posts
    2
    Location
    Current code - works until creating the new file names.

    Private Sub cbCreate_Click()


    Dim tbState As String


    'Inserting the data from the form into the client list sheet


    Dim LastRow1 As String, ws As Worksheet
    Dim LastRow2 As String
    Dim LastRow3 As String
    Dim LastRow4 As String
    Dim LastRow5 As String
    Dim LastRow6 As String
    Dim LastRow7 As String
    Dim LastRow8 As String
    Dim LastRow9 As String
    Dim LastRow10 As String
    Dim LastRow11 As String
    'Dim LastRow12 As String
    'Dim LastRow13 As String


    Set ws = ThisWorkbook.Worksheets("Client List")




    LastRow1 = ws.Range("B" & Rows.Count).End(xlUp).Row + 1
    LastRow2 = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    LastRow3 = ws.Range("C" & Rows.Count).End(xlUp).Row + 1
    LastRow4 = ws.Range("D" & Rows.Count).End(xlUp).Row + 1
    LastRow5 = ws.Range("E" & Rows.Count).End(xlUp).Row + 1
    LastRow6 = ws.Range("F" & Rows.Count).End(xlUp).Row + 1
    LastRow7 = ws.Range("G" & Rows.Count).End(xlUp).Row + 1
    LastRow8 = ws.Range("H" & Rows.Count).End(xlUp).Row + 1
    LastRow9 = ws.Range("I" & Rows.Count).End(xlUp).Row + 1
    LastRow10 = ws.Range("J" & Rows.Count).End(xlUp).Row + 1
    LastRow11 = ws.Range("K" & Rows.Count).End(xlUp).Row + 1
    'LastRow2 = ws.Range("L" & Rows.Count).End(xlUp).Row + 1
    'LastRow3 = ws.Range("M" & Rows.Count).End(xlUp).Row + 1




    ws.Range("B" & LastRow1).Value = tbFirstName
    ws.Range("A" & LastRow2).Value = tbSurname
    ws.Range("C" & LastRow3).Value = tbDOB
    ws.Range("D" & LastRow4).Value = tbSurname & "-" & tbFirstName
    ws.Range("E" & LastRow5).Value = tbDate
    ws.Range("F" & LastRow6).Value = tbPhoneNumber
    ws.Range("G" & LastRow7).Value = tbStreetAddress
    ws.Range("H" & LastRow8).Value = tbSuburbCity
    ws.Range("I" & LastRow9).Value = tbPostCode
    ws.Range("J" & LastRow10).Value = cbState
    ws.Range("K" & LastRow11).Value = tbGender
    'ws.Range("L" & LastRow12).Value =
    'ws.Range("M" & LastRow13).Value =




    'Application.ThisWorkbook.Worksheets("Client List").Range("B2").Value = tbFirstName
    'Application.ThisWorkbook.Worksheets("Client List").Range("A2").Value = tbSurname
    'Application.ThisWorkbook.Worksheets("Client List").Range("C2").Value = tbDOB
    'Application.ThisWorkbook.Worksheets("Client List").Range("D2").Value = tbSurname & tbFirstName
    'Application.ThisWorkbook.Worksheets("Client List").Range("E2").Value = tbDate






    'Opening the master document






    Application.Workbooks.Open "/Users/dylanmaley/Personal Documents/Projects/Dietetics Assessment Tool - Macro.xlsm"






    'Adding the data from the form to the master document


    Application.Workbooks("Dietetics Assessment Tool - Macro.xlsm").Worksheets("Pt Info").Range("B3").Value = tbFirstName
    Application.Workbooks("Dietetics Assessment Tool - Macro.xlsm").Worksheets("Pt Info").Range("B4").Value = tbSurname
    Application.Workbooks("Dietetics Assessment Tool - Macro.xlsm").Worksheets("Pt Info").Range("B5").Value = tbDOB








    'Saving the new file as the name entered into the New CLient Form within a new folder with patients name


    Dim Path As String
    Dim Path2 As String
    Dim FName1 As String
    Dim Fname2 As String


    FName1 = ThisWorkbook.Sheets("Client List").Range("A" & LastRow1).Value
    Fname2 = ThisWorkbook.Sheets("Client List").Range("B" & LastRow2).Value




    Path = "Users/dylanmaley/Personal Documents/Dietetic Reports/"
    Path2 = "Users/dylanmaley/Personal Documents/Dietetic Reports/ FName1 & FName2/"
    'FName1 = Application.ThisWorkbook.Worksheets("Client List").Range("A2")
    'Fname2 = Application.ThisWorkbook.Worksheets("Client List").Range("B2")


    'Marco to vreate new folder


    MkDir "Users/dylanmaley/Personal Documents/Dietetic Reports/" & FName1 & "-" & Fname2 & "/"



    'Marco to create file name


    'FName1 = ThisWorkbook.Sheets("Client List").Range("A" & LastRow1).Value
    'Fname2 = ThisWorkbook.Sheets("Client List").Range("B" & LastRow2).Value

    'Application.Workbooks("Dietetics Assessment Tool - Macro.xlsm").SaveAs FileName:="Path & FName1 & "-" & Fname2 & ".xlsm"
    'Application.Workbooks("Dietetics Assessment Tool - Macro.xlsm").SaveAs FileName:=Path2 & FName1 & "-" & Fname2 & ".xlsm"
    'Application.Workbooks("Dietetics Assessment Tool - Macro.xlsm").SaveAs Filename/= Path & FName1 & "-" & Fname2 & "/" & FName1 & "-" & FName2 & ".xlsm"


    ActiveWorkbook.SaveAs Filename:="/Users/dylanmaley/Personal Documents/Dietetic Reports/FName1 & " - " & FName2 & " / " & FName1 & " - " & FName2 &.xlsm"







    Unload Me

Posting Permissions

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