Solved Vba to Copy Sheet, Rename using Cell Value & Increment 1

Microsoft Excel 2010 - complete product...
October 9, 2014 at 09:05:52
Specs: Windows 7 Service Pack 1
Hi,

I have code that will copy a template sheet, rename it using a cell value and adds -1 if sheet name already exists. How can I modify to change the last number instead of adding a -1?

Number is T-12345-X001 would like the next sheet if number is duplicated to be T-12345-X002

Sub NewSheet()
    Dim ws As Worksheet
    SheetName = Sheets("Letter-Template").Range("E3")
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = SheetName Then GoTo exists1:
    Next
    ActiveSheet.Copy _
    after:=Sheets(Sheets.Count)
    ActiveSheet.Name = SheetName
    Exit Sub
exists1:
    For Each ws In ActiveWorkbook.Worksheets
        If Left(ws.Name, Len(SheetName) + 1) = SheetName & "-" Then GoTo exists2:
    Next
    ActiveSheet.Copy _
    after:=Sheets(Sheets.Count)
    ActiveSheet.Name = SheetName & "-1"
    Exit Sub
exists2:
    n = 0
    For Each ws In ActiveWorkbook.Worksheets
    If Left(ws.Name, Len(SheetName)) = SheetName And Len(ws.Name) > Len(SheetName) + 1 Then
        If Left(ws.Name, Len(SheetName)) = SheetName And Right(ws.Name, Len(ws.Name) - (Len(SheetName) + 1)) * 1 > n Then
            n = Right(ws.Name, Len(ws.Name) - (Len(SheetName) + 1)) * 1
        End If
    End If
Next
    ActiveSheet.Copy _
    after:=Sheets(Sheets.Count) 'ActiveWorkbook.Sheets("Letter-Template")
    ActiveSheet.Name = SheetName & "-" & n + 1

End Sub

Thank you,
Sandi


See More: Vba to Copy Sheet, Rename using Cell Value & Increment 1

Report •

✔ Best Answer
October 21, 2014 at 12:47:59
re: Yes (it is also duplicated in cell #3)

I'm not sure what you mean by cell # 3, but it doesn't really matter in this case.

Just as an FYI...internal to Excel, cell #3 is C1 as can be seen by running this code:

Sub CellByNum()
 MsgBox Cells(3).Address
End Sub

Of course, referencing a cell by a single number is not commonly done, although the use of Cells(RowIndex, ColumnIndex) is very common and very powerful.

Note: I have some other suggestions for making your code more idiot-proof...err...I mean "user friendly" but I'll save that for later. Let's get through this "update log" issue first.

OK, I am going to use the ActiveSheet.Name as the search string. The code below should replace the section of code where you are looping through all of the open sheets. It should update the log with just the values from the ActiveSheet.

Let me know what you think...

Sub FindSheetName()
'Set Transmittal Log Column A as search range
   With Sheets("Transmittal Log").Columns("A")
'Search for ActiveSheet.Name
     Set t = .Find(ActiveSheet.Name, lookat:=xlWhole, LookIn:=xlValues)
'If ActiveSheet.Name is found, loop through cells, copying data
      If Not t Is Nothing Then
  'How the following loop works:
      't.row is the Row where ActiveSheet.Name was found
      'nxt_num is just a variable
      'Columns B:G are Columns 2:7 (the Copy-To Columns)
      'Rows 15:20 are 2:7 + 13 (the Copy-From Rows)
      'If we add 13 to each instance of nxt_num, we get the next Copy-From Row number
      'This allows us to use a single variable for both the Copy-From Rows and _
      'the Copy-To Columns (Sneaky, eh?)
        For nxt_num = 2 To 7
         Sheets("Transmittal Log").Cells(t.row, nxt_num) = _
                ActiveSheet.Range("L" & nxt_num + 13)
        Next
'Copy the single value (L21) that doesn't fit the looping scheme
        Sheets("Transmittal Log").Cells(t.row, "L") = ActiveSheet.Range("L21")
      Else:
'Present Message if ActiveSheet.Name not found
           MsgBox (ActiveSheet.Name & " Not Found")
           Exit Sub
      End If
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 9, 2014 at 20:10:36
Before I offer any suggestions I'd like to know a little bit more about your process. If you're using a cell in a template for the SheetName, isn't a given that the sheet name will already exist? If that's the case why not increment the number in the template before creating the new sheet?

A different question: If the new sheet is always the last sheet, why do you need to loop through all the sheets trying to find the matching name? Why not just check the name of the last sheet?

In other words, I'm trying to find out more about your process and how you are using the workbook before I offer suggestions on how to increment the last digit of the new sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 12, 2014 at 10:22:52
Are you still looking for a solution to this issue? If so, please supply the requested details.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
October 13, 2014 at 06:15:55
Sorry I was unable to get to this over the weekend as I was on vacation.

Question 1: Actually, as I think about your question yes that would work if they put the # of the sheet on the template prior to creating.

Question 2: Is there a way to add a message if the # already exists in the workbook or if it is blank?

What I am trying to do is create a workbook for our Letter of Transmittals that we send to the Customer and make it as user friendly as possible. I have a template that multiple departments will be using. The naming process will be "T-12345-X001", where X stands for the department code capturing each transmittal sent. The next step will be to create a log that will pull certain cells from those sheets to prefill so we can track (I have not started that process yet as I am unsure where to start).

Hope this helps,
Sandi


Report •

Related Solutions

#4
October 13, 2014 at 07:41:48
I'm not sure what you are asking for in your Question 2:

Is there a way to add a message if the # already exists in the workbook or if it is blank?

Yes, it is fairly easy to check for the existence of a specific sheet name and present a message, but I'm not sure what you plan to do with that information. From a "user friendly" (sometimes referred to as "idiot proof") perspective what would you expect the user to do when they get a message that the sheet name already exists?

As far as checking to see if a specific sheet is blank, I don't see how that would ever happen unless a user (or macro) deleted all of the data in that sheet. If you are adding sheets by copying the template, the new sheet will never be empty because the template itself contains data.

I spent a little time on this over the weekend and came up with the following code.

The code copies the template and then increments the value in the template so that the next time the code is run the 3 digit number at the end of the sheet name is already "+1". This eliminates to need to check for the existence of a sheet with a specific name based on the contents of E3 unless the user is adding/renaming sheets manually. That could be dealt with, but I since I don't know if that will ever happen, I didn't put any work into it.

As written, the code will work up to T-12345-X1999 after which the "increment" section of code gets a little screwy. If you think you will need more than 1999 sheets for a given department, I'll need to know more about the "department code". Extracting/incrementing the last 3 digits is easy. which is what I did. This works up to 1999 and retains the 001, 002, format. After 1999, we need to extract 4 characters but since I don't know what your "department code" looks like, I can't simply look for values greater than 1999 because the first digit might be part of a department code, not part of the incremented sheet number.

Sub NewSheet1()
Dim ws As Worksheet
Dim SheetNameStart, SheetNameEnd, SheetName As String

'Add new Sheet and change Name based on value in Letter-Template!E3
  SheetName = Sheets("Letter-Template").Range("E3")
   ActiveSheet.Copy after:=Sheets(Sheets.Count)
   ActiveSheet.Name = SheetName

' ***Increment value in Letter-Template!E3 ***
'Extract Left side of Letter-Template!E3 up to the last three characters
   SheetNameStart = Left(Sheets("Letter-Template").Range("E3"), _
                         Len(Sheets("Letter-Template").Range("E3")) - 3)

'Extract Right side of Letter-Template!E3...the last 3 characters
   SheetNameEnd = Right(Sheets("Letter-Template").Range("E3"), 3)

'Increment value in Letter-Template!E3
'*** Note: If "sheet number" > 1999, this section of code
'will produce inconsistant results ***
   Sheets("Letter-Template").Range("E3") = _
          SheetNameStart & Format(SheetNameEnd + 1, "000")
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
October 13, 2014 at 07:55:06
After reading your first reply I thought it would be best to control the worksheet name with the cell value vs incrementing the name of the worksheet.

If the cell value is blank (user forgot to input value) then a message would pop up telling them to input a # or if the # was already used a message would pop up and tell them to check the log for the next available number.

Hope this makes sense?


Report •

#6
October 13, 2014 at 09:57:37
The code I offered does "control the worksheet name with the cell value" and also increments the value after a new sheet is added, but of course, it requires that a value (with the correct format) already exists in Letter-Template!E3.

Your latest request has me concerned...

If the cell value is blank (user forgot to input value) then a message would pop up telling them to input a # or if the # was already used

Popping up a message if Letter-Template!E3 is blank and/or popping up a message if a sheet with the name shown in Letter-Template!E3 already exists is relatively easy.

However, I have my concerns about having the users manually input the name to be used for the new sheet. I guess it all depends on how idiot - errr- I mean, user proof you want to make the process.

Let's say that Letter-Template!E3 contains T-12345-X001 and that Sheet T-12345-X001 already exists. The user runs the macro and a message pops up and tells the user that the name already exists and that he should determine the next available number (e.g. T-12345-X002) and enter it in Letter-Template!E3. That's all well and good, so far.

The user then clicks on Letter-Template!E3 and accidentally enters T-12345-Y002 and runs the macro. Now you've got a sheet with an incorrect Department Code or some other typographical error. Only you can decide how big of an issue this might be.

That said, this code will add a new sheet based on the value in Letter-Template!E3 if:

1 - There actually is a value in Letter-Template!E3, and
2 - A sheet with that name doesn't already exist

If either of those 2 conditions are false, the appropriate message will be presented to the user.

Sub AddSheetMsg()
 Dim ws As Worksheet, chkShtName As String
 
'Set variable to value in Letter-Template!E3
 
 chkShtName = Sheets("Letter-Template").Range("E3")
 
'If chkShtName is empty, inform user, exit sub
  
  If chkShtName = "" Then
     MsgBox "Please enter a name for the new sheet in Letter-Template!E3" _
             & vbCrLf & vbCrLf _
             & "Once the value is entered, please re-run the macro."
     Exit Sub
  End If
  
'If chkShtName is not empty, check to see if Sheet exists.
'The Set instruction will produce an error if the sheet does not exist.
'We can hide the error, let the code continue and then reset the error.
'If the sheet does not exist, ws will be Set to Nothing
   
   On Error Resume Next
     Set ws = Sheets(chkShtName)
   On Error GoTo 0
      
'If ws is Not Nothing, then the worksheet must exist, therefore inform user, exit sub
      
     If Not ws Is Nothing Then

       MsgBox "A sheet with the name found in Letter-Template!E3 already exists." _
               & vbCrLf & vbCrLf _
               & "Please refer to the log for the next available Sheet Name" _
               & " and enter it in Letter-Template!E3." _
               & vbCrLf & vbCrLf _
               & "Once the value is entered, please re-run the macro."
 
       Exit Sub
     End If
     
'If we've made it this far, then Letter-Template!E3 is not blank and a
'sheet with that name does not exist, so we'll add it

      ActiveSheet.Copy after:=Sheets(Sheets.Count)
       ActiveSheet.Name = chkShtName
       
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#7
October 13, 2014 at 11:13:18
Wow that code is exactly what I was looking for, thank you so much for your help.

Your concerns are the same ones I am worried about as well. I will toss that out to the manager I am helping create this for and see how much of an issue he thinks this will be.

Do you think it is possible to add something in that will say if "X" is not one of A, M, P, E or PM then the message would say Department code not valid?

Thanks again for your help!
Sandi


Report •

#8
October 13, 2014 at 11:43:45
In order for the code to check a single letter (or even the "PM" duo) within a string, it would need to know where to look for the letter within the string. There are multiple ways to do that, but I would need to know more about the string before I could offer any suggestions.

For example, if there will only be a single occurrence of A, M, P, E or PM in the string, then searching for any of those letters is easy.

e.g. T-12345-P001

However, things get more difficult if you have something like this:

T-47PK3-P001

Now we can't just look for a P, we have to look for a P following a hyphen.

But wait! What if the string could look like this?

T-P7K83-P001

Now we have to look for a P following the second hyphen.

Just about anything can be done, but the more details we know about the various formats the string might take, the easier it is to account for those situations.

'twere it me, I'd consider some sort of data validation to ensure that the users can't enter invalid strings.

A series of Data Validation Drop Down lists could be used to build the string based on the different segments and even supply the final "digits" to ensure that there will never be a duplicate sheet. That's stuff that you would need to work on...I just make suggestions. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
October 13, 2014 at 11:48:45
There will always be T-"5 numbers"-, so the letter would always fall after the second hyphen.

Not sure how a data validation option would work but I will look into that as well.

Thanks again for your advise and help :)

Sandi


Report •

#10
October 20, 2014 at 07:43:17
Hi,

I am wondering if you can help me with part 2 of this workbook. I have code to add the Transmittal # to the Log. What I would like to do next is to take the data entry section on the Transmittal Form and have a macro update the Transmittal Log in the row that contains the Transmittal #. I have the following code to update the log after the template is created, but I am not sure how I can tweak this to have it find and update.

Sub WBLoop()
Dim wkst As Worksheet
Dim row As Long 'if you ever exceed 32,000 this will fail as integer
row = 4
For Each wkst In ActiveWorkbook.Worksheets
' loop through the Open worksheets
   If wkst.Name Like "T1*" Then
      Worksheets("Transmittal Log").Cells(row, 1) = wkst.Range("E3")
      row = row + 1
   End If
Next
End Sub

I can't do this all at the same time as the log is creating the number so I need to do the code above when the sheet is created. The users will then go back and fill out all the data at a later time. This information will be updated on a sheet to sheet basis.

Thank you,
Sandi

message edited by SandiS


Report •

#11
October 20, 2014 at 09:38:54
I'm confused...

"I have the following code to update the log after the template is created, but I am not sure how I can tweak this to have it find and update."

Find and update what?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
October 20, 2014 at 09:58:54
I would like to find the row that has the Transmittal # on the Transmittal Log sheet and copy the information that is entered on the Letter of Transmittal form. I have the following code to do what I want but it goes through every sheet each time, I just want it to update the ActiveSheet.

I guess I am not sure if find the existing number on the Transmittal Log is really needed but I wasn't sure how to tell it which row to go to to add the information.

    Dim wkst As Worksheet
    Dim row As Long 'if you ever exceed 32,000 this will fail as integer
    row = 4
    For Each wkst In ActiveWorkbook.Worksheets
    ' loop through the Open worksheets
       If wkst.Name Like "T1*" Then
        Worksheets("Transmittal Log").Cells(row, 2) = wkst.Range("L15")
        Worksheets("Transmittal Log").Cells(row, 3) = wkst.Range("L16")
        Worksheets("Transmittal Log").Cells(row, 4) = wkst.Range("L17")
        Worksheets("Transmittal Log").Cells(row, 5) = wkst.Range("L18")
        Worksheets("Transmittal Log").Cells(row, 6) = wkst.Range("L19")
        Worksheets("Transmittal Log").Cells(row, 7) = wkst.Range("L20")
        Worksheets("Transmittal Log").Cells(row, 12) = wkst.Range("L21")
        row = row + 1
       End If

Hoe this helps.


Report •

#13
October 20, 2014 at 12:02:42
Maybe it's just me, maybe it's just because it's Monday, maybe it's just the way you are explaining what you are trying to do, but I'm just not getting a clear picture.

The best I can offer at this point is an example of the method used to "tell it which row to go to to add the information."

I'll toss out what I think you are looking for, you look it over and tell me what I got right and/or wrong.

My assumptions:

1 - You have sheets with names like T1xxx, T1yyy, T1zzz
2 - You have a sheet named Transmittal Log
3 - You are currently working in the sheet named e.g. T1yyy, making it the ActiveSheet
4 - You have a value in a cell, e.g. L15, on sheet T1yyy that you want to find on the Transmittal Log sheet.
5 - Once you have found that value on the Transmittal Log sheet, you need to know what Row the value was found in.

Based on those assumptions, this code will return the Row number from the Transmittal Log sheet where the value in ActiveSheet!L15 was found.

Sub FindRow()
'Search all cells on Transmittal Log
 With Sheets("Transmittal Log").Cells
'Search for value in ActiveSheet!L15
  Set t = .Find(ActiveSheet.Range("L15"), lookat:=xlWhole, LookIn:=xlValues)
   If Not t Is Nothing Then
'Display Row number (t.Row) if value found
    MsgBox ActiveSheet.Range("L15") & " was found on Row " & t.Row & _
           " of the Transmittal Log sheet."
   Else
'Display "Sorry" msg if value not found
     MsgBox "Sorry, " & ActiveSheet.Range("L15") & _
            " was not found on the Transmittal Log sheet."
   End If
  End With
End Sub

If you need to find multiple occurrences of the value on the Transmittal Log sheet, see this site for examples of the code used for .Find and .FindNext:

http://msdn.microsoft.com/en-us/lib...

If I am way off based with my assumptions, please set me straight.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
October 20, 2014 at 12:48:14
Maybe this will help, I have the following entries on the sheet T1yyyy

Column L Column M
Transmittal #: T12345-M001
File Location: Customer Supplied
Description: M001 Updated Status
For: Information

I want to take this information and copy onto the "Transmittal Log" (see layout below) in the row that coincides with the Transmittal #.

ColumnA Column B Column C
Transmittal # {Value From Sheet T1yyyy} {Value from Sheet T1yyyy} .... etc

Hope this makes sense, I wish I could screen shot but not sure how to do that here?

Thanks
Sandi




Report •

#15
October 20, 2014 at 17:37:26
Please use the pre tags when posting example data so that the columns line up and are easier to read.

After I show you what I assume you have, I'll follow that with my questions.

Input from T1yyy sheet:

                   L                 M
Row x        Transmittal #:     T12345-M001
Row x + 1    File Location:     Customer Supplied
Row x + 2    Description:       M001 Updated Status
Row x + 3    For:               Information

Output on Transmittal Log sheet:

Assumption: T12345-M001 is already in Ay. Data in By, Cy & Dy needs to be copied from T1yyy.


Abbreviations so items fit in post:
CS = Customer Supplied
M001 = M001 Updated Status
Info = Information
            A            B        C       D
Row y  T12345-M001       CS      M001    Info

So, if I'm not mistaken, you want to find T12345-M001 on the Transmittal Log sheet and copy the data from T1yyy into the row where T12345-M001 is found.

If that's right, here are my questions:

How many cells have to be copied...just the 3 shown or more? If it's more, is it always the same number of cells or does it vary?

How will the code know what to copy? In other words, how will it know that you want to copy the data associated with a specific "T" value, e.g. T12345-M001? e.g. will the user have already selected it, will an InputBox be used to get the info from the user, etc?

And then there's the issue that we are dealing with human beings that make mistakes. Have you thought of all the different ways that the user could screw this up so that the code fails?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
October 21, 2014 at 05:16:58
Your assumption is correct, it will always be the following items that will be copied over. I have also included in my code I have for changing this form into a pdf and opening an e-mail that these cells must be filled in before the macro can continue.

T12345-M001 [Sheet Cell]   to  Transmittal Log [Column]
L15                                         B
L16                                         C
L17                                         D
L18                                         E
L19                                         F
L20                                         G
L21                                         L

The person will be on the T12345-M001 Sheet, when this information will be entered so I envisioned the macro come from ActiveSheet.

Hope this helps.

message edited by SandiS


Report •

#17
October 21, 2014 at 07:05:08
You have not answwered this question:

How will the code know what to copy? In other words, how will it know that you want to copy the data associated with a specific "T" value, e.g. T12345-M001? e.g. will the user have already selected it, will an InputBox be used to get the info from the user, etc?

The fact that the user will be on that sheet means that we can use the ActiveSheet method, but the code will still need to know what to search for.

How will the search string be passed to the macro?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#18
October 21, 2014 at 07:14:23
The user will have filled out that section on the T12345-M-001. I then have a button to click with the following macro to run to PDF, E-mail and Update the log. Right now this code works but it is a loop that goes through all sheets that start with T1 and updates the log. I would like to replace that section to just update the information on the current number/sheet they are on.

Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
    Dim FileName As String

    If ActiveWindow.SelectedSheets.Count > 1 Then
        MsgBox "There is more then one sheet selected," & vbNewLine & _
               "be aware that every selected sheet will be published"
    End If

    If Range("L15") = "" Then
    MsgBox "Please fill in File Location (Cell L15)"
    Exit Sub
    End If
  
    If Range("C8") = "" Then
    MsgBox "Please fill in Subject (Cell C8)"
    Exit Sub
    End If

    If Range("L17") = "" Then
    MsgBox "Please fill in For (Cell L17)"
    Exit Sub
    End If

    If Range("D45") = "" Then
    MsgBox "Please fill in Submitted By (Cell D45)"
    Exit Sub
    End If
    
    If Range("C5") = "" Then
    MsgBox "Please fill in Date (Cell C5)"
    Exit Sub
    End If

    If Range("L20") = "" Then
    MsgBox "Please fill in Date Due (Cell L20)"
    Exit Sub
    End If

    If Range("L21") = "" Then
    MsgBox "Please fill in VDR Ref (Cell L21)"
    Exit Sub
    End If

    'Call the function with the correct arguments
    'Tip: You can also use Sheets("Sheet3") instead of ActiveSheet in the code(sheet not have to be active then)
'    FileName = RDB_Create_PDF(ActiveSheet, "", True, False)

    'For a fixed file name and overwrite it each time you run the macro use
    FileName = RDB_Create_PDF(ActiveSheet, "\\abprocesssys\shares\jobs\Blank Templates\Project Management Templates\Letter of Transmittal.pdf", True, False)

    If FileName <> "" Then
        RDB_Mail_PDF_Outlook FileName, "", ActiveSheet.Range("Q3").Value, _
                             "What would you like here????" _
                           & vbNewLine & vbNewLine & "", False
'_
'                           & vbNewLine & ActiveSheet.Range("B2").Value, False
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to Save the file in arg 2 is not correct" & vbNewLine & _
               "You didn't want to overwrite the existing PDF if it exist"
    End If
    
    
    
'Update Log
    Dim wkst As Worksheet
    Dim row As Long 'if you ever exceed 32,000 this will fail as integer
    row = 4
    For Each wkst In ActiveWorkbook.Worksheets
    ' loop through the Open worksheets
       If wkst.Name Like "T1*" Then
        Worksheets("Transmittal Log").Cells(row, 2) = wkst.Range("L15")
        Worksheets("Transmittal Log").Cells(row, 3) = wkst.Range("L16")
        Worksheets("Transmittal Log").Cells(row, 4) = wkst.Range("L17")
        Worksheets("Transmittal Log").Cells(row, 5) = wkst.Range("L18")
        Worksheets("Transmittal Log").Cells(row, 6) = wkst.Range("L19")
        Worksheets("Transmittal Log").Cells(row, 7) = wkst.Range("L20")
        Worksheets("Transmittal Log").Cells(row, 12) = wkst.Range("L21")
        row = row + 1
       End If
Next
Range("M8") = Now() & ", " & Application.UserName
End Sub


Report •

#19
October 21, 2014 at 10:44:05
It seems that we are still not on the same page since you still haven't answered my latest question. Please keep in mind that I cannot see your workbook from where I am sitting, so you need to supply all of the details, even if they seem excruciatingly obvious to you.

Once again I will have to make assumptions and ask that you tell me if I am right or wrong. Let's take this step by step:

Earlier you asked/said:

"I guess I am not sure if find the existing number on the Transmittal Log is really needed but I wasn't sure how to tell it which row to go to to add the information."

Based on that I assumed:

"So, if I'm not mistaken, you want to find T12345-M001 on the Transmittal Log sheet and copy the data from T1yyy into the row where T12345-M001 is found."

To which you replied:

"Your assumption is correct,"

Which made me ask:

"How will (the code) know that you want to copy the data associated with a specific "T" value, e.g. T12345-M001?

In other words, it is my understanding that you want the code to determine which row contains the T1* value (e.g. T12345-M001) on the Transmittal Log sheet and then copy the data from the ActiveSheet to that row.

OK, based on the latest code that you sent me, am I correct in assuming that the sheet name contains the T1* value that we are working with at any given time?

In other words, if the ActiveSheet is named T12345-M001, then the code should attempt to find that string (ActiveSheet.Name) on the Transmittal Log sheet...correct?

Or is that the code should create a new line on the Transmittal Log sheet and place the ActiveSheet.Name in the next empty row in Column A and then copy the data from the cells (L15-L21) on the ActiveSheet to the Transmittal Log sheet?

If the code is supposed to find the ActiveSheet.Name on the Transmittal Log sheet (certainly doable via code) one has to assume that the ActiveSheet.Name is already on the Transmittal Log sheet. If the code is supposed to create a new line for the ActiveSheet.Name and data, then that is even easier to do. I just need to know which situation we are dealing with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#20
October 21, 2014 at 10:52:22
OK, based on the latest code that you sent me, am I correct in assuming that the sheet name contains the T1* value that we are working with at any given time?
Answer = Yes (it is also duplicated in cell #3)

In other words, if the ActiveSheet is named T12345-M001, then the code should attempt to find that string (ActiveSheet.Name) on the Transmittal Log sheet...correct?
Answer = Yes that is correct

And this part is True as well: "If the code is supposed to find the ActiveSheet.Name on the Transmittal Log sheet (certainly doable via code) one has to assume that the ActiveSheet.Name is already on the Transmittal Log sheet."

Sorry for the confusion, it's hard to explain what you are looking at and get the idea across.

Thank you,
Sandi


Report •

#21
October 21, 2014 at 12:47:59
✔ Best Answer
re: Yes (it is also duplicated in cell #3)

I'm not sure what you mean by cell # 3, but it doesn't really matter in this case.

Just as an FYI...internal to Excel, cell #3 is C1 as can be seen by running this code:

Sub CellByNum()
 MsgBox Cells(3).Address
End Sub

Of course, referencing a cell by a single number is not commonly done, although the use of Cells(RowIndex, ColumnIndex) is very common and very powerful.

Note: I have some other suggestions for making your code more idiot-proof...err...I mean "user friendly" but I'll save that for later. Let's get through this "update log" issue first.

OK, I am going to use the ActiveSheet.Name as the search string. The code below should replace the section of code where you are looping through all of the open sheets. It should update the log with just the values from the ActiveSheet.

Let me know what you think...

Sub FindSheetName()
'Set Transmittal Log Column A as search range
   With Sheets("Transmittal Log").Columns("A")
'Search for ActiveSheet.Name
     Set t = .Find(ActiveSheet.Name, lookat:=xlWhole, LookIn:=xlValues)
'If ActiveSheet.Name is found, loop through cells, copying data
      If Not t Is Nothing Then
  'How the following loop works:
      't.row is the Row where ActiveSheet.Name was found
      'nxt_num is just a variable
      'Columns B:G are Columns 2:7 (the Copy-To Columns)
      'Rows 15:20 are 2:7 + 13 (the Copy-From Rows)
      'If we add 13 to each instance of nxt_num, we get the next Copy-From Row number
      'This allows us to use a single variable for both the Copy-From Rows and _
      'the Copy-To Columns (Sneaky, eh?)
        For nxt_num = 2 To 7
         Sheets("Transmittal Log").Cells(t.row, nxt_num) = _
                ActiveSheet.Range("L" & nxt_num + 13)
        Next
'Copy the single value (L21) that doesn't fit the looping scheme
        Sheets("Transmittal Log").Cells(t.row, "L") = ActiveSheet.Range("L21")
      Else:
'Present Message if ActiveSheet.Name not found
           MsgBox (ActiveSheet.Name & " Not Found")
           Exit Sub
      End If
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#22
October 21, 2014 at 13:17:03
The first thing(s) I would change are these sections:

 If Range("L15") = "" Then
      MsgBox "Please fill in File Location (Cell L15)"
      Exit Sub
  End If

Exiting the Sub and leaving the user hanging is not very satisfying.

At a minimum, why not Select the offending cell so the user is all set to update the cell without having to think about what the message said:

 If Range("L15") = "" Then
      MsgBox "Please fill in File Location (Cell L15)"
      Range("L15").Select
      Exit Sub
  End If

Better yet, why not give the user the opportunity to update cell from within the macro and then let it run from there. No need to force the use to keep running the macro from the start:

If Range("L15") = "" Then
'Get input from User
     user_val = _
       Application.InputBox("Please fill in File Location (Cell L15)", _
      "Empty Cell Warning")
'Select Cell and Exit Sub if User Cancels
       If user_val = False Then
         Range("L15").Select
         Exit Sub
       End If
'Put value in cell if entered by User
       Range("L15") = user_val
    End If

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#23
October 21, 2014 at 13:29:02
YEAH......that is perfect THANK YOU SO MUCH!!! And I like your option to update within the macro, I will try that as well :)

Now I have another question, if they realize they need to delete that sheet for some reason is there a way to clear the log as well?

Again thank you so much for all of your help, my head has been spinning! But I am hoping now with your assistance I am seeing a ray of light ahead!


Report •

#24
October 21, 2014 at 16:58:13
By clearing the log, I assume you mean deleting the Row that contains the sheet name of the deleted sheet. If you mean to simply delete the data in the Row (all except the sheet name) the same concept would be used but the code would need to be modified, slightly.

I cannot take full credit for the following solution. I merely modified the concept that can be found here, as well as many other places on the interweb.

http://www.vbaexpress.com/forum/sho...

Since there is no event handler for the deletion of a sheet, we have to use a different event to trigger the code to clear the log, unless you want to run a macro manually. In this case, I'm using the SheetDeactivate event. Deleting a sheet essentially deactivates it, so we can capture that event and run some code.

Open the VBA editor and double click the ThisWorkbook module. Paste this code into the pane that opens:

Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
    Application.OnTime Now + TimeSerial(0, 0, 1), "DeletedSheet"
End Sub

Now paste this code into a Standard module:

 
Sub DeletedSheet()
    Dim oWS As Object
'Allow code to continue when the Set instruction throws 
'up an error because the sheet doesn't exist
    On Error Resume Next
'Determine last row with Sheet Names in Transmittal Log Column A
      lastRw = Sheets("Transmittal Log").Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet Names in reverse order so Rows can be deleted
        For shtNum = lastRw To 2 Step -1
'Fill variable with Sheet Name
         nxtSht = Sheets("Transmittal Log").Range("A" & shtNum)
'Try to access Sheet
          Set oWS = Sheets(nxtSht)
'If Object cannot be set then Sheet doesn't exist, so delete Row
            If oWS Is Nothing Then
              Sheets("Transmittal Log").Range("A" & shtNum).EntireRow.Delete
            End If
'Clear Object if Sheet existed
          Set oWS = Nothing
        Next
End Sub

What will happen is this:

Whenever a sheet is Deactiavted, the event will be captured and the Workbook_SheetDeactivate code will call the SheetDeleted code. The SheetDeleted code will then loop through the Sheets Names in Transmittal Log Column A and try to set an Object variable to each sheet. If it cannot set the variable to a given sheet because the sheet no longer exists, it will delete that row from the Transmittal Log sheet.

You should be aware that this code will run every time any sheet is deactivated for any reason. In other words, it will run every time a sheet tab is clicked to switch sheets. Whether or not this will have a performance impact on the operation of your workbook will be based on the speed of your machine and the number of worksheet in the workbook.

You may have noticed the irony of the fact that we eliminated the need to loop through every sheet in the workbook in order to update the log, only to replace that with the need to loop through every sheet in the workbook to test for deletions. :-)

There are other options:

Instead of using the SheetDeactivate event, you could use the BeforeClose and/or BeforeSave event to clear the log just before the workbook is Closed or Saved. That would also be automatic but it probably wouldn't run as often. As I mentioned earlier, you could also just manually run the SheetDeleted macro to clear the log whenever you felt the need.

Let me know if this makes sense.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#25
October 22, 2014 at 06:37:11
I will use this as another button labeled "Delete", that way the macro will not have to run as often. This works but it takes out my header line also (when there is only one). Can this be modified to start at row 4?

Report •

#26
October 22, 2014 at 07:22:49
Figured my question above out changed the following line

 For shtNum = lastRw To 2 Step -1

to

For shtNum = lastRw To 4 Step -1

Thank you again for all of your help you are AMAZING!!!


Report •

#27
October 22, 2014 at 08:01:15
I'm glad I could help.

Pop Quiz:

Do you know why the code runs through the Sheet Names in Transmittal Log Column A in reverse order?

e.g.

For shtNum = lastRw To 4 Step -1

and not

For shtNum = 4 to lastRw

BTW...

I don't know if I have mentioned this before, but you might want to review the material in this tutorial. I use these techniques all the time, whether I'm writing my own code or trying to fix/understand code others have written:

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#28
October 22, 2014 at 08:30:40
Pop quiz: I have been thinking about that and I am not really sure. I thought maybe it had do with having a clean stop point?

Thank you for the link, I will definitely save that to reference back too :-)


Report •

#29
October 22, 2014 at 20:49:54
Whenever you delete rows, you have to do it in reverse order or the loop counter won't match up with the rows you want to check.

Let's say you have 10 rows to check and you set up your loop to count forward from 1 to 10. Let's say the code determines that Row 2 should be deleted.

When the code deletes Row 2, Row 3 will move up and become Row 2. When the code executes the Next instruction, it will increment to 3 and check Row 3, which used to be Row 4, before Row 2 was deleted. That means that the original Row 3, which is now Row 2, will never be checked. Each time a row is deleted and a Row moves up, the row that moved up is ignored by the loop since the counter is already past that number.

However, if you count backwards, every row will get checked. Let's say the code determines that Row 8 needs to be deleted. After Row 8 is deleted, the loop will decrement to 7 and check the original Row 7. Even if Row 7 gets deleted, the loop will Next be at 6 and Row 6 - still the original Row 6 - will be checked.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#30
October 23, 2014 at 05:04:10
Wow that's very interesting and very good to know :)

Thank you,
Sandi


Report •

Ask Question