0

To perform the following actions:

  1. Copy a particular "template"-worksheets as many times as the number of cells selected in the master database
  2. Create worksheets (with names) based on the selected range of cells
  3. Based on the name of the newly created worksheet - update one of the cells
  4. Use VLOOKUP functionality to pull other values based on the cell updated in Step 3.

I have the following VBA code:

Sub CreateWorkSheetByRange()
'variable declaration
Dim WorkRng As Range
Dim Ws As Worksheet
Dim arr As Variant
Dim tws As Worksheet
'Start of Program
On Error Resume Next
'Specify the title of the dialog that requests for range
xTitleId = "Select Range"
' Assign template worksheet to a variable
Set tws = Worksheets("template")
' Assign the application.selection function to the variable WorkRng
Set WorkRng = Application.Selection
' Accept input from the user
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
' Create an array of the input values
arr = WorkRng.Value
' The following line is optional
' Application.ScreenUpdating = False
' Create the Worksheet names based on range selected
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            tws.Copy after:=Worksheets(Sheets.Count)
            Set Ws = Application.ActiveSheet
            Ws.Name = arr(i, j)
        Next
    Next
' Application.ScreenUpdating = True
End Sub

I would like to improve the following things in my code - but do not know how

  1. Want to know if the code is efficient - or bug free
  2. I want to distribute this to a group of engineers - how can I do it. I should not assume that they know how to utilize this code in VBA - is it possible to give this like a executable excel?
heavyd
  • 62,847
  • 18
  • 155
  • 177
Prasanna
  • 4,036
  • 5
  • 34
  • 51
  • @heavyd How did you make syntax highlighting using markdown? Looks excellent now... Thanks! – Prasanna Apr 27 '15 at 05:15
  • Apparently using the `` tags triggers different formatting from the built in markdown formatting (ie prefix the line with 4 spaces). – heavyd Apr 27 '15 at 05:20
  • 2
    You should always prefer markdown rather than html code for formatting. For one thing it allows [syntax highlighting](http://meta.stackexchange.com/questions/184108/what-is-syntax-highlighting-and-how-does-it-work). The highlighting language is worked out from the tags in the question but can be overridden. – Mokubai Apr 27 '15 at 05:42
  • `Want to know if the code is efficient - or bug free ` - so does everyone who writes code! You need to find an efficient way to catch bugs and log them, we can't tell if the above will ever/never produce issues! And to answer number 2, you point them to http://superuser.com/questions/801609/how-do-i-add-vba-in-ms-office! They're engineers and running VBa is a fairly trivial task! – Dave Apr 28 '15 at 11:52
  • @Dave Thanks for your advice on Question-1. I'm new to VBA - my intent of posting this code was to find out if there are effective ways of doing the same thing the code does. I agree with you on the `bug free` portion as you pointed out correctly. For Question-2 - I'm planning to send out the link you provided along with the code on `how to use the VBA code`. Many Thanks! – Prasanna Apr 28 '15 at 12:08
  • @Prasanna, I suggest you look at http://www.cpearson.com/excel/errorhandling.htm - you should be able to expand things like this into logging as well (to file) to get details about logs, what the values of your variables were etc to help debug – Dave Apr 28 '15 at 12:15

0 Answers0