Accessing sheets in other workbooks

[Houdinii]

I have created a excel workbook that allows a user to enter information through entry sheets and update to appropriate equipment log sheet. Unfortunately their is over 100 pieces of equipment which I have each piece of equipment on a separate sheet this tends to cause the workbook to be extremely slow when opening, saving or exiting document. Any recommendations on how I should change this structure to better accommodate the many pieces of equipment with several columns of data each.

Ideas I have had would include;

breaking the equipment up into multiple sheets. Which gives me the problem of opening sheets from different workbooks while using all macros and code from main workbook. I need to be able to open individual equipment worksheets for review and possible updates.

Another would be put all on one extremely long spread sheet. with a lot of sorting management. I am not sure I could do the manipulation necessary to accommodate the need. I could use pivot tables to report or review this information, However I need them to be able to review by equipment and also be able to edit as needed.


Please give me some suggestions on which way would be easiest with the maximum results.

 

[patel45]

to open xls file from macro

Sub OpenFile2()
ChDrive "D:"
ChDir "D:\DATAI\test"
filename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If filename <> False Then
     Workbooks.Open (filename)
     MsgBox "File: " & filename & " is open"
Else
    Exit Sub
End If
End Sub

 

[Houdinii]

because I don't want the user to have to answer the question of which file to open I modified this a bit, I am currently using;


ChDrive "D:"ChDir "D:\DATAI\test" Workbooks.Open ("D:\DATAI\test\Support Logs.xlsm")
Sheets("CP101").Visible = True
Sheets("CP101").Select
' Protects current sheet
Application.Run "Control_protect_this_sheet"
' Sorts the data on the current sheet
Application.Run "Sort_date_des_recno_des"
' Locate form to top right
Range("A1").Select
' Locate active cell (cursor) to hide under button
Range("A3").Select


</pre>

At this point I am in the workbook "Support Logs.xlsm" with the original workbook in the background. On this workbook I have a button that is macro enabled I have set this macro to look at the original workbook. The job of this macro is to save this workbook if necessary and close it relinquishing power back to the original work book.

Unfortunately when I use the macro it is giving me an error:
"Cannot run the macro "Support Logs.xlsm'!Main Menu'. The macro may not be available in this workbook or all macros may be disabled."


I have checked in the trust center and made sure all macros are enabled and Trust access to the VBA project object model is set on.

Any ideas on how to solve this?

to open xls file from macro

Sub OpenFile2()
ChDrive "D:"
ChDir "D:\DATAI\test"
filename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If filename <> False Then
     Workbooks.Open (filename)
     MsgBox "File: " & filename & " is open"
Else
    Exit Sub
End If
End Sub