Copy Sheet Macro

slickrit

New Member
Joined
Nov 2, 2011
Messages
2
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

bstory84

Active Member
Joined
Oct 31, 2011
Messages
403
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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?
Code:
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

New Member
Joined
Nov 2, 2011
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top