Thread: Using Personal.xls as a worksheet template holder.

Hi all!

Newbie here and love learning new vba tricks and grasping new concepts.

I have a problem and it's the one thing that is preventing me from crossing the finish line. Have coworkers who are not pc savy and I am trying to minimize the amount of un needed data that they will have to send over their wireless mobile network cards. They work with customers on remote sites and we use excel to process all of our paperwork/forms. I know this is not the best solution, but I am making the best of a mediocre situation.

I have my personal.xls workbook with all the required macros and scripting to create a custom toolbar for them (idiot proof..i mean user friendly) to handle things that I do not want to have to repeat over and over. The personal .xls also has worksheets preformated and coded to mesh well with the workbooks that they are currently working with. I need to be able to copy sheets from the personal.xls workbook to a unknown named workbook (the workbook exists, but the filenames are never going to be the same and I want these to work with older versions of our paperwork). My code is as follows, but I need where it references "2012 Workbooks.xls" to be the current workbook. The code works perfectly with my workbooks named Personal.xls and 2012 Workbooks.xls. Just when I throw the variable workbook name it will fail...of course.

Sub AddWarranty()

' Add Warranty Check List Macro
Application.ScreenUpdating = False

Windows("PERSONAL.XLS").Visible = True
Sheets("Warranty Check List").Select
Sheets("Warranty Check List").Copy After:=Workbooks("2012 Workbook.xls").Sheets("Repair Order")

'Cleans up lost references
Cells.Replace What:="[PERSONAL.XLS]", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Windows("PERSONAL.XLS").Visible = False
Application.ScreenUpdating = True
End Sub

So in the end, they can add forms as needed therefore only sending in necessary forms.
Thank you in advance. I am sure the solution is easy, it is just escaping me at the moment.

Sub snb()
  Application.ScreenUpdating = False

  with windows("PERSONAL.XLS")
    .Visible = True
    .Sheets("Warranty Check List").Copy ,thisworkbook.Sheets("Repair Order")
    .Visible = False
  end with
  Thisworkbook.sheets("Warranty Check List").Cells.Replace "[PERSONAL.XLS]", ""

  Application.ScreenUpdating = True
End Sub

You'd better reference the workbook and the sheet for the replacement to prevent unpredictable results.

Sub snb()
  Application.ScreenUpdating = False

  thisworkbook.Sheets("Warranty Check List").Copy ,activeworkbook.Sheets("Repair Order")
  activeworkbook.sheets("Warranty Check List").Cells.Replace "[PERSONAL.XLS]", "", xlpart

  Application.ScreenUpdating = True
End Sub

Ok, pardon my lack of comprehension here.
First let me make sure that I have my goal stated correctly. My initial workbook has only three tabs/worksheets (necessary items for all jobs: Repair Order, Daily Story Sheet and Scanned Documents). The rest of the optional tabs/worksheets all reside in the hidden personal.xls file which is on the local machine(reduces file send size). When the user clicks a custom button on a custom toolbar, the script copys the hidden tab into a specific location in the active workbook; i.e I just went from 3 worksheets to 4 worksheets in my workbook. Any repeat adds will be subsequently labelled incrementally; i.e. "Daily Story Sheet (2)".

So this copies from the personal.xls to the active workbook at the end of the worksheet tabs?

thisworkbook.Sheets("Warranty Check List").Copy ,activeworkbook.Sheets("Repair Order")

And this is reformatting an existing worksheet with values in the personalxls file?

activeworkbook.sheets("Warranty Check List").Cells.Replace "[PERSONAL.XLS]", "", xlpart

Copy from personal.xls to activeworkbook:

Workbooks(1).Sheets("Warranty Check List").Copy ,activeworkbook.Sheets("Repair Order")