Sub ImportForm() Dim wbLog As Workbook Dim MainSht As Worksheet, LogSht As Worksheet, CopySht As Worksheet Dim rData As Range Dim NewRw As Long Dim sFil As String, sTitle As String, sWb As String Dim iFilterIndex As Integer Set MainSht = ActiveSheet ' Set up list of file filters sFil = "Excel Files (*.xl*),*.xl*" ' Display *.xls by default iFilterIndex = 1 ' Set the dialog box caption sTitle = "Select File to Zip" ' Get the filename With Application sWb = .GetOpenFilename(sFil, iFilterIndex, sTitle) Set wbLog = Workbooks.Open(sWb) ' On Error GoTo err_handler .ScreenUpdating = False .DisplayAlerts = False Set LogSht = Worksheets("Member.Meetings.Log") MainSht.Copy After:=wbLog.Sheets(wbLog.Sheets.Count) ActiveSheet.Name = ActiveSheet.Range("B4").Value Set CopySht = ActiveSheet Set rData = LogSht.Range("A1").CurrentRegion.Offset(1) NewRw = rData.Rows.Count + 1 CopySht.Range("B10").Copy LogSht.Select 'manager NewRw = rData.Rows.Count rData.Cells(NewRw, 1).Select ActiveSheet.Paste Link:=True 'company name CopySht.Range("C2").Copy rData.Cells(NewRw, 2).Select ActiveSheet.Paste Link:=True 'date of meeting CopySht.Range("E6").Copy rData.Cells(NewRw, 3).Select ActiveSheet.Paste Link:=True CopySht.Range("D4").Copy rData.Cells(NewRw, 4).Select ActiveSheet.Paste Link:=True 'add link rData.Cells(NewRw, 5).Select LogSht.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "'" & CopySht.Name & "'!A1", TextToDisplay:=CopySht.Name clean_up: .ScreenUpdating = True .CutCopyMode = False .DisplayAlerts = True End With Exit Sub err_handler: MsgBox "No file selected", vbCritical Resume clean_up End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE