Copy Sheet Macro

[slickrit]

I have a worksheet with data for hundreds of employes across the columns and they all have a unique identifier in column A. There is a VLOOKUP in cell A6 that all the employee data is tied to, which populates the sheet I would like to make a copy of. Instead of going through, creating a copy, and manually entering the unique identifier in A6, is there a macro that could do that for me and name the new sheet whatever is in column A from the data set?

I'm a newbie to VBA but looking to improve!

Thanks!

 

[bstory84]

Well don't quite understand exactly what you are trying to do. but here is a simple copy worksheet code. and i added a line to name the copied sheet using a cell referance.

Sub shtcopy()
Sheets("Sheet1").Select ' put your sheet name here that you wish to copy
Sheets("Sheet1").Copy After:=Sheets(3)

ActiveSheet.Name = Range("a1").Value 'put the cell ref that you want your sheet named

End Sub

 

[HalfAce]

Hi and welcome to the board. (Both of you.)
Here's an example of bstory84's code that does not select the sheet being copied, (we seldom need to select an object to work with it), does not specify the sheet to be copied by name (just copies the active sheet), does not specify where to put the new sheet (just puts it at the end of the workbook) and names the new sheet with the value in A6.
It also traps for the error generated if a sheet by that name already exists and stops the operation. If that's not what you're wanting to do then can you provide a bit more info?

Sub shtcopy()
ActiveSheet.Copy After:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Range("A6").Value 'put the cell ref that you want your sheet named
If Err.Number = 1004 Then
  MsgBox "A sheet by the name '" & [A6].Value & "' already exists in this workbook." & vbNewLine & _
         "Please change the value in cell A6 and try again."
  On Error GoTo 0
  Application.DisplayAlerts = False
  ActiveSheet.Delete
  Application.DisplayAlerts = True
End If
End Sub

Hope it helps.

 

[slickrit]

Well, I fail for my response time but this all got put on the back burner, big time obviously. Thank you for helping.

HalfAce, what would I need to do to amend your code so the value in cell A6 on the newly copied sheet is populated with a corresponding cell from column 'A' from the main data set? For example, I want to copy sheet 46 to create a new sheet titled 47 and I want sheet 47's information to correspond with line 48 of another sheet in the workbook titled "data."

In other words, I want cell A6 in the newly copied sheet (again, we'll say 47) to be the value for whatever is in cell A48 from the sheet named "data" (i.e. Data!A48). I want the macro to continue copying sheets until it comes across a cell in column A of the Data sheet that is blank. So, if I have 100 lines in the data sheet that I want to make individual sheets for, it will create 100 copies (titled 1-100), and cell A6 for sheet 1 will be 1, for sheet 2 will be 2, for 3 will be 3, and so on....

I hope that makes sense. Thanks again for helping out.