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
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
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