0

I am newbie at VBA and have a problem to solve, which for you will be more than simple, I am sure. Here is the problem:

I have table with company names in column A. What I want is to select cell with company name, click the button which will run a macro creating new sheet based on template, and rename the newly created sheet with name of the selected cell in Sheet1 (company name).

The macro generally works, I have only problem with renaming it. Your help will be greatly appriciated. Also any comments on my code in general will be very useful. Here is the code:

Sub NewSheet()

Dim wb As Workbook
Dim ws As Worksheet
Dim activeWB As Workbook
Dim FilePath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
Set wb = Application.Workbooks.Open(FilePath)
wb.Worksheets(1).Copy After:=activeWB.Sheets(activeWB.Sheets.Count)
activeWB.Activate

ActiveSheet.Name = Worksheets("Arkusz1").ActiveCell.Value

wb.Close False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Community
  • 1
  • 1
Thomas
  • 13
  • 4
  • 1
    Your code opens a workbook. Do you want to create a new sheet in the workbook that has the selected cell or do you want to open a workbook and create a new sheet in the other workbook?? – teylyn Jul 25 '17 at 11:33
  • Side Note: Avoid using `On Error Resume Next` which is bad practice because it just mutes errors but does not handle them. Instead use a proper [Error Handling](https://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/5411/error-handling). – Pᴇʜ Jul 25 '17 at 11:36
  • Thanks for the quick reaction. Well, in my workbook the template opens as a new sheet, so I thought that it is a worksheet. I want to have it the same workbook where selected cell is, opening as a new sheet. – Thomas Jul 25 '17 at 12:16

2 Answers2

1

If you need to use the ActiveCell, you can use something like the code below:

Dim ShtName As String
ShtName = ActiveCell.Value2 ' <-- save the value of the ActiveCell

Set wb = Application.Workbooks.Open(FilePath)
wb.Worksheets(1).Copy After:=activeWB.Sheets(activeWB.Sheets.Count)

' rename the sheet
activeWB.Sheets(activeWB.Sheets.Count).Name = ShtName
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
0

You've copied the new worksheet to the end, so you can get hold of the last worksheet like so:

Set activeWB = ActiveWorkbook
' You copied sheet 1
wb.Worksheets(1).Copy After:=activeWB.Sheets(activeWB.Sheets.Count)
' New sheet is last sheet
Dim sh As WorkSheet
Set sh = activeWB.Sheets(activeWB.Sheets.Count)
' Rename sheet
sh.Name = activeWB.Sheets("Arkusz1").ActiveCell.Value

Please note, relying on selections and active cells/sheets/workbooks is risky, and can create hard-to-diagnose errors. Try and use cell addresses instead of active cells.

Wolfie
  • 27,562
  • 7
  • 28
  • 55
  • Thank you for the comment, I will keep it in mind as I will be learning VBA. As to your answer I can't get it working, not sure why. Probably I did something wrong. Fortunately answer from Shai Rado worked perfectly. – Thomas Jul 25 '17 at 12:30
  • Ah glad your problem was resolved, for reference this question's answer has some great pointers for avoiding `Select` https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Wolfie Jul 25 '17 at 13:05
  • It really has. Thanks a lot! – Thomas Jul 25 '17 at 13:35