Worksheet Copy And Rename Not Working As Intended

Oct 11, 2009

I've got a Workbook that contains the following worksheets:

1. conversion(2) - Hidden
2. Old Data - Hidden
3. Blank Form

I want to copy "Blank Form" once for each day of the month.
I'd like to rename each copied worksheet with the Date (i.e. Oct 01 2009)

Here's what I'm working with:

Sub Copysheets ()

Worksheets("Blank Form").select

Dim x As Integer

For x = 1 to 30

Worksheets(1).Copy after: =Worksheets(x)
Worksheets(x+1).Name=Format(DateSerial(2009, 20, x), "MMM-DD-YYYY")

Next x

End Sub

The problem I'm having is that the first sheet to be copied is the "Old Data" worksheet and the copy is renamed Oct-01-2009. After that the correct Worksheet is copied and renamed Oct-02-2009 and so on.

I can't figure out why it's copying the "Old Data" worksheet first.

View 9 Replies


ADVERTISEMENT

VBA Code Not Working As Intended

Nov 6, 2009

Why is the following code not running the macro the number of times I type into the box?


Sub Macro10()
'Sub RunABunch()
Dim ans As Variant, i As Long
ans = Application.InputBox("Enter a Number of loops", Type:=1)
If Not IsNumeric(ans) Then
' user hit cancel, so exit sub
Exit Sub
End If
For i = 1 To CLng(ans)
' do your work here
Next i
' Macro10 Macro
'
'
Cells.Find(What:="Chief", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Cut
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
End Sub

I didnt write the loop and input portion of the code, I just recorded the macro and added that bit of code from the net.

View 9 Replies View Related

Copy And Rename Macro Not Working

Jul 16, 2012

I have a workbook contains 2 sheets, Admin & Master. I have a list (A:A) on Admin sheet having dates based on a drop down menu somewhere on other side on the sheet. I am running the below customized code that i found online to copy the Master sheet and then it should get renamed as per list (A:A).

Public Sub CopyRenameIt()
Sheets("Admin").Select
' Determine how many territories are on Admin sheet
FinalRow = Range("H65000").End(xlUp).Row

[Code]....

View 1 Replies View Related

Copy And Rename Worksheet

Feb 16, 2008

Z = Sheets.Count
I = Range("a65536").End(xlUp).Row
Range("a" & I + 1) = I
Worksheets("MASTER").Copy After:=Sheets(Z)
Worksheets("MASTER (2)").Name = I

the probelm is it asks the user to accept or enter there own name for the copied sheet. how can i do this without it asking the user

View 9 Replies View Related

Copy Worksheet And Rename It Using A Userfom

Feb 9, 2009

When I press "commandbutton1", I need "userform1" to view and insert a name in the text box. When I press OK in "userform1" I need a macro to copy worksheet "s0" into a new worksheet and rename this worksheet with the name typeed in "userform1". Also I need to view the name of all worksheet copied using "userform1" in column H in worksheet final.

View 4 Replies View Related

Create Copy Of Worksheet & Rename

Dec 21, 2006

I have the following code in my vb app. It creates the new worksheet, gives it the corect name, copies all of the existing data from an existing worksheet and pastes it in the new worksheet, but I can not get it to refresh the formulas. When I look at the formulas in the new sheet they reference back to the MasterSheet worksheet(which is my template I copy and paste from when making a new worksheet)

objExcel.Sheets("MasterSheet").Select
objExcel.Sheets.Add
objExcel.Sheets("Sheet1").Select
objExcel.Sheets("Sheet1").Name = MySheetName
objExcel.Sheets("MasterSheet").Select
objExcel.Cells.Select
objExcel.Selection.Copy
objExcel.Sheets(MySheetName).Select
objExcel.Cells.Select
objExcel.Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
objExcel.Sheets(MySheetName).Select
objExcel.Range("B1").Value = Me.DTPicker10.Value
objExcel.Sheets("AccessDataMonday").Select
objExcel.Range("B1").Value = Me.DTPicker10.Value
objExcel.Application.Run "LoadDataFromAccessUsingDates"
objExcel.Visible = True

View 3 Replies View Related

Copy Worksheet That Is Hidden And Rename It Using A Userfom

Feb 16, 2009

I asked for a macro that copy an worksheet and rename it using a userfom!
I received the xls file attached to this mail !
In this xls when I click "Click me to copy s0 sheet and rename it" from final sheet, a userform appear, I type a name there and when I press ok I will have a copy of worksheet "s0" with the name typed in the userform.
If I click on "Click me to copy s0 sheet and rename it" I can create as many copies as I need.

But if I hide worksheet "s0" (using Format /Sheet/Hide) the macro will not work.
I modified the code from module 1 adding the red

View 6 Replies View Related

Copy Worksheet, Rename As Cell Value & Sort All Sheets

Oct 1, 2009

I am working with a workbook that has data automatically entered each time a new child is enrolled to the program. The child's data is automatically entered to the sheet named "Intake". What I need to do now is rename that sheet using the child's name as the sheet name, but also keep the sheet named "Intake" for the next entry. I would then like to sort the sheets alphabetically but leaving the "Intake" sheet either as the first sheet or the last sheet. I have attached an example of the workbook I am working with.

View 6 Replies View Related

Automated Worksheet Copy, Rename Based On Cell Results

Jul 29, 2009

I am building a workbook. The data we are tracking is all in one xml file, which i have mapped to 3 different worksheets(customers, invoices, inventory). On the customers and inventory sheet I have an interface for creating a new customer/product/invoice. I used the macro recorder to make the macro's to do these three things, but could use help on a couple of other functions as I don't actually know VBA. I need to be able to automate editing of existing records by having a button to press on the each sheet that will open an input box that asks the user which invoice number, customer number or product number he would like to edit, then copy that record to the interface for editing, then another macro to replace the existing data with the newly edited data. It would also be wonderful if when creating or editing an invoice it could create a copy of the interface worksheet and rename it as the invoice number. The first row of each datasheet is blank, and each of the interfaces have formula's for importing the information copied to row 1 from the data tables. the second row of each datasheet contains formulas for importing data from the interface. Any help would be terrific...I know you guys are excel gods and I will forever be in your debt if you can help me out....thanks in advance, I'll be studying my butt off until I figure this out

View 11 Replies View Related

Copy Worksheet To An Existing Workbook Not Working

May 30, 2007

I have a user who is trying to use <EDIT><MOVE OR COPY SHEET> to copy a sheet to an existing workbook. She has them in the same directory and has them both open, but when she tries to copy a sheet from either of them to the other, the only workbook listed in the "TO BOOK" drop down menu is the one she is in. NO other workbooks show up as an option for her to copy to.

View 12 Replies View Related

VBA - Insert And Rename Columns Not Working Properly

Feb 21, 2013

The code below is supposed to insert a column and rename it. However, when I debug, the code only renames the column, it does split or run the loop. I press debug again, and then code executes as it is intended.

I can't explain why I have to press debug twice for in order for the code to work properly.

Code:
Sub renameColumns()
With Sheets("byPosition")

Columns("E:E").Select
Selection.Insert Shift:=xlToRight
.Range("E1").Value = "Exemption"
.Cells.EntireColumn.AutoFit

[Code] .......

View 2 Replies View Related

Code Stopped Working - Can No Longer Copy And Save Worksheet

Jul 31, 2012

I have been working on this application for the past couple weeks and I have this module of code that copy's a worksheet into a new workbook and changes everything into values. However I have added buttons onto this sheet and after running the code again it froze the program, in which I had to manually end. It gave an error afterwards however I don't remember what it said. Now everything I try to run or step through it, nothing happens. And I have turned Events and ScreenUpdating on.

I would also like to note that it won't allow me to perform actions like unhide sheets or unprotect them. The code has not been changed either so what has happened to disable all functionality.

[URL] .....

View 1 Replies View Related

VBA / Code Stopped Working - Can No Longer Copy And Save A Worksheet

Jul 30, 2012

I have been working on this application for the past couple weeks and I have this module of code that copy's a worksheet into a new workbook and changes everything into values. However I have added buttons onto this sheet and after running the code again it froze the program, in which I had to manually end. It gave an error afterwards however I don't remember what it said. Now everything I try to run or step through it, nothing happens. I have turned Events and ScreenUpdating on.

View 2 Replies View Related

For Loop Not Running As Intended, Can't Find

Feb 17, 2010

I thought my code was working properly, but then I tried different numbers for myNum and I realize it's not. Here's the

View 5 Replies View Related

Rename Worksheet Name By VBA

Aug 29, 2013

I have came up with the following code , i want all the sheets renamed but except for sheet("PRODUCT")

i tried with if not and somehow it did not work.

Code:

Sub ChangeWorkSheetName()
Dim WS As Worksheet
i = 1
On Error Resume Next
For Each WS In Worksheets
WS.Name = "CUSTOMER" & i
i = i + 1
Next
End Sub

View 4 Replies View Related

Rename Tht Worksheet

May 13, 2007

I renamed a sheet and for some odd reason excel added .xls] before the name of the sheet. So the name was now .xls]Sheet 1. I tried to rename the sheet again to get rig of the .xls], but the program didn't allow it and said that I "entered an invalid name". Now when I'm trying to make links to cells on this sheet, excel says that my "formula contains ans ivalid external reference to a worksheet". The worksheet is now unusable and copying the sheet and updating all the links on other worksheets is a lot of work.

View 6 Replies View Related

Rename Worksheet Cell Value In Another Worksheet

Aug 29, 2011

I have renamed worksheets according to a cell value in the same worksheet, but now I need to rename the worksheet according to a cell value in another worksheet.

Here is the code I tried:

Code:

Private Sub Worksheet_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("Set-Up Page").Range("B2").Value "" Then
'rename the worksheet to the contents of cell B2
Sh.Name = Sheets("Set-up Page").Range("B2").Value
End If

End Sub

I have pasted that code into the worksheet that needs to be renamed, but it doesn't work. I have also tried:

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = Sheets("Set-up Page").Range("B2") Then Sh.Name = Target
End Sub

I like the first one because it checks for a blank value. There will be blank values depending on how many worksheets each teacher needs, so it will just rename the ones that have values filled in. Each worksheet will have the code referencing to a different cell on the Set-Up Page worksheet.

View 9 Replies View Related

VLookup To Pull A Value Out Of A Range And Return Intended Data

Jan 25, 2010

I am trying to use a VLookup to pull a value out of a range and return my intended data. See attached spreadsheet. The data I'm pulling from is in the top chart. The bottom chart, in the factor column, is where I want the data to pull to. Ex) If the "Months of Development" column in the bottom chart =13, I want it to pull the factor value from the top chart where the month is 13. The problem is that the top chart has a range of values in the "Month Range" column of 13 - 24. I don't know how to get the VLookup to recognize this and pull the same factor into the chart below whether the value is 13 or 24. Maybe VLookup isn't the best option here.

View 2 Replies View Related

Duplicate And Rename Worksheet

Dec 11, 2008

I have what should be a simple question. I have a button in a form that needs to duplicate a sheet and rename it. It will duplicate but i am having problems with the renaming. The name of the sheet is coming from a userform text box. Here is the code i am trying.

View 2 Replies View Related

Rename Worksheet With Cell Value

Jun 26, 2012

I need to rename the sheet5 with the value in E3 of Sheet2.

View 1 Replies View Related

Rename Worksheet By Code Name

Dec 5, 2012

I have a worksheet named "TEMPLATE", it's codename is Sheet10.

I want to rename "TEMPLATE" using the codename for the worksheet.

How would I go about doing that?

View 4 Replies View Related

Possible To Have A Macro Rename A Worksheet?

Aug 2, 2008

Is it possible to have a macro re-name a worksheet. The context I want to use it would almost as a conditional formula.

Example: IF A1 = "time sheet" then sheet1 is then re-named to "time sheet"

View 9 Replies View Related

Insert New Worksheet And Rename

Jun 24, 2009

Can I get a macro to insert a new worksheet each time it runs and rename it to a value held in a cell on another tab eg Sheet1, cell A1 - this value will change each time the macro runs so there wont be any duplicated tab names ?

View 9 Replies View Related

Rename Active Worksheet

Aug 25, 2006

I'm looking for a macro to automatically rename the active worksheet to Sheet1.

View 3 Replies View Related

Rename Worksheet Code

Dec 13, 2006

I currently download a report and it has a different worksheet name everytime. Is there any way using a macro to change the worksheet name? (My macro extracts data from worksheet a and copies into worksheet b) Hope I've provided enough info.

View 7 Replies View Related

How To Create A Macro To Add New Worksheet And Rename It

Aug 25, 2009

I tried recording a macro to add to a new worksheet, but it gives error while running.

Issue as I understand is, by default excel gives a new worksheet a name (Say Sheet 4), and when u run macro worksheet, new created name may be different.

View 7 Replies View Related

How To Create Macro To Add New Worksheet And Rename It

Oct 3, 2012

I need to have many users press a button within the excel sheet that duplicates the form from the current page, opening a new tab (copying the form) that also prompts to input a specific name e.g. "Enter date and day or night shift" to name the tab itself.

I have just worked out how to put a Command Button in and used the above code to create the new page assigning the code to it.

I need the "my sheet" to be prompted and customisable.

View 3 Replies View Related

Rename Worksheet With Opening The File

Dec 12, 2008

I have the following code, which I use to open up a .xls file generated from Crystal Reports:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim fexport1 As String ' variables for the exported file
Dim fexport2 As String
Dim wb1 As String 'variables to change between the opened workbooks
Dim wb2 As String
strTemp = "Please Choose The Exported File"
MsgBox strTemp
fexport1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls)")
If InStr(fexport1, "False") = 0 Then
Workbooks.Open fexport1
wb1 = ActiveWorkbook.Name

Else
strTemp = "Operation Canceled"
End If

The problem is that Crystal Reports generates the file with an invalid worksheet name (it contains a backslash) and I do not have access to modify the Crystal Report. Althought the file can be automatically repaired by Excel when manually opened, the macro chokes and the "Application.DisplayAlerts = False" does not fix it. The only solution I can think of is renaming the worksheet without opening the file.

View 9 Replies View Related

Keyboard Shortcut To Rename Worksheet

May 4, 2009

Is there a keyboard shortcut to select the name of the active worksheet? At the moment I have to double click on the [Sheet1] and then retype.

If there isn't, is there a macro which can select the name of the active worksheet?

View 9 Replies View Related

How To Rename Worksheet Tabs Using Formula And Formatting

Jan 27, 2014

I have a workbook with 30+ worksheets

named 23 Wed, 24 Thur, 25 Fri, etc etc

meaning April 23, 2014 Wednesday, April 24, 2014 Thursday, etc etc

The next project may start on May 19, 2014 Monday - so I have to rename each worksheet beginning with 19 Mon, etc etc

Renaming each worksheet manually takes a long time and is prone to error

I would like to just rename the first worksheet to start date of May 19, 2014 and have the rest renamed automatically.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved