Delete Residual Code From Worksheet After Sheet Copy
Oct 25, 2006
I have a worksheet containing code to hide itself when the sheet is deselected:
Private Sub Worksheet_DeActivate()
Sheet16.Visible = xlSheetVeryHidden
End Sub
After unhiding this sheet and modifying it, I copy it. This new copy becomes the 'production' copy, and the self-hiding sheet is blanked, leaving me with a hidden blank template and a new sheet. However, the self-hiding code is copied along into the new sheet. I'm looking for a way to delete the sheet code from the copy. The best I've found is how to remove a module from the workbook.
Sub New_Sheet()
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.Name = Range(" F8").Value
Sheet16.Visible = True
Sheet16.Select
Range("B13:F22").ClearContents
Sheets(2).Select
ActiveSheet.Shapes("Button 1").Delete
Range("A8").Select
ActiveSheet.PageSetup.PrintArea = Range("A1:R35").Address
Call Sort_Sheets
End Sub
View 5 Replies
ADVERTISEMENT
Jan 27, 2014
I have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".
I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.
There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.
Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).
Transfer Macro : [Code]....
Delete Macro : [Code]....
Overall Macro : [Code]....
View 7 Replies
View Related
Jan 19, 2010
Can someone help me out with this script?.
I keep getting "Subscript Out of Range Error"
I'm trying to delete all the code on sheets(1) of the active workbook.
View 8 Replies
View Related
Dec 13, 2006
how to write a procedure to delete macro code that is in the worksheet property? I created a and excel model for a colleague which had many macro procedures including "Worksheet_Activate". The problem is now this individual moves the worksheets into new workbooks and this is cause many runtime errors. Is there a way I can write a copy/move procedure that also deletes the Worksheet Activate macro so that error does not appear?
View 2 Replies
View Related
Mar 16, 2009
How do you delete a single sheet from a workbook by name? I assumed something like this, but it doesn't work
View 2 Replies
View Related
Oct 2, 2009
I want DELETE the rows that contains 0 (zero or -) in column F (SALDO BRUTO), when I click the Button (Clear). And How I can copy from this sheet to a new sheet in this workbook, but the names of new sheet automatic rename to next date or 2, and next when I click the Button "Copy to New Sheet".
View 2 Replies
View Related
Mar 30, 2014
Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.
Also, the sheetname will be unknown, the macro must get the sheet name first.
View 2 Replies
View Related
Oct 28, 2011
I would like to disable cut/copy/paste and maybe delete inside a worksheet. Reading earlier threads on this subject provided the code to put in the ThisWorkbook module, where it works great for all my worksheets. However, there is one worksheet where it is necessary to have copy/paste, either with control c or right click/copy. Here is part of that code provided by Tom Urtis:
Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub
What do I replace Application with to work on just the worksheet level?
View 2 Replies
View Related
Mar 18, 2014
I have a macro below where it looks at sheets name and deletes if Jan is not there.
I would like to change this a bit so that it looks for all 12 months (Jan to Dec) instead of looking for Jan only.
View 8 Replies
View Related
Jun 7, 2007
I need to cycle and extract row data from the A, B and D columns and copy it into another worksheet while maintaining the copied data structure. Right now I am manually selecting the data....
View 9 Replies
View Related
May 26, 2013
I am trying to put togther a VBA form button click to do the following: I have several customer names all in master sheet A1 - A300. I want the code to notice that there is a new customer and generate a new sheet, naming the sheet the customers name and copying and paste the entire sheet named 'worksheet' to this newly generated sheet.
View 2 Replies
View Related
Aug 15, 2006
i am trying to make a VBA that will find a certain value (BELCS), select the value, and copy it to another worksheet. this is what i have so far:
For Each BELCS In ActiveSheet. Range("B2:B" & ActiveSheet.Range("B51161").End(xlUp).Row)
If BELCS.Value = "BELCS" Then
BELCS.Select
Selection.Copy
Sheets("BELCS").Select
ActiveSheet.Paste
End If
Next BELCS
i am trying to get it to select the whole row when it finds that value but when it runs it says that i can't make a selection of the BELCS,
View 3 Replies
View Related
Jul 19, 2013
I need to do something to my workbook, and I need to do this task:
When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..
Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..
So I don't need to insert/delete the row and copy the formula manually for each worksheet..
I know that I could simply solve it with grouping the sheet tab..
But I have plenty of data that needed to be inserted and applied with the formula..
I will attach the little example : insert.xlsx
And one more thing, I received this VB code from [URL] ..... for inserting the column:
VB:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
And this code for deleting the column:
VB:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
View 9 Replies
View Related
Mar 20, 2008
I am using following codes to find and delete number in series.
HTML Option Explicit
Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
If Val(Me.TextBox1) > Val(Me.TextBox2) Then
MsgBox "End number must be greater than start number"
Me.TextBox2.Value = ""
Me.TextBox1.SetFocus
Me.TextBox2.SetFocus
Exit Sub
End If
lVal = Val(Me.TextBox1): uVal = Val(Me.TextBox2)
Application.ScreenUpdating = 0
For i = 1 To Sheets.Count
With Sheets(i)
Set Rng = .Range("a1", .Range("a1").SpecialCells(xlCellTypeLastCell))
For Each r In Rng
If r >= lVal And r
View 9 Replies
View Related
Nov 10, 2009
I could use some assistance in creating a macro that will delete all and only text boxes on the active sheet. Some text boxes will be empty, but I still wish for them all to be deleted. Through searching the forums I see a similiar code, but it deletes all shapes on the active sheet:
View 8 Replies
View Related
Mar 31, 2014
So I have an option on my userform that the user input selected.
I need the data once written to the worksheet [Master] to move based on cell AD [Customer Divert / TBP Divert / Failed Delivery]
Example: Row 12 has Customer Divert in cell AD I need to keep a copy on the master sheet but also copy the entire row to the customer divert sheet.
Need code and here to input in the Code sheet?
View 7 Replies
View Related
Jan 26, 2008
I have two workbooks: Workbook1 has two sheets – Cash Register and Suppliers; and Workbook2 has one sheet – Clients. The users of Workbook2 do not have access to Workbook1, and information entered in Workbook2 is required (and has to be manually re-entered) in Workbook1.
I tried IFs and VLOOKUPs to pull the data, but due to the large number of cells, the workbook is awfully slow – taking a very long time to save (even with Automatic Calculation/Recalculate before save turned off).
I am using Excel 2007.
This is what I am trying to achieve:
When a user enters an ID number, which begins with either “S” (e.g., SB-00010) or “P” (e.g., PADA-012-034-0567), in column E of Cash Register:
(1)
If that ID number begins with “S”, I want the code to look up that ID number in column A of Suppliers and copy the corresponding cell from each of the following columns:
B
C
D
E
F
G...........
View 14 Replies
View Related
Dec 7, 2011
I am trying to copy worksheet between 1 workbook to another. But even the worksheet was successfully copied but the code inside wasn't. Do I need to copy the VBA module manually (create new proc/sub) ? Or there is a setting during Worksheet copy process?
View 4 Replies
View Related
Mar 14, 2013
I am looking for VBA code that will select a data validation cell, copy the selection, and paste the value of that cell in a different worksheet.
The data validation list is in cell L47. The user will select a date from cell D31, type a description of activities in the adjacent column and then select initials from the aforementioned validation list in cell L47. I need the code to fit into a button I created so that when they click it to approve the activity, the code will copy the value of the initials and paste it into column AB in a separate worksheet. Column AB runs parallel to column A, which contains all of the dates located on Sheet1 in cell D31. I think I might need some sort of loop to run this so that it pastes initials on the correct date.
View 3 Replies
View Related
Feb 21, 2008
I am revamping someones Monthly Stat sheet for them. I am hoping to get a code that will copy the most current sheet which will always be the last one of the Workbook. I then would like the user to be Prompted to rename the sheet (Unless it can automatically generate the name which would be the next month). For example the sheet I need to copy is currently "January 2008" AND THE new sheet should be titled February 2008. At the end of February they need to run the code again and the sheet will of course be named March 2008 and so on...
The other thing I need to do is after the sheet is named:
1) I need the sheet named placed in cell A1 of the NEW sheet
2) I need to copy the VALUES in E9 to O9 and Paste them into E5 to 05 of the Newly created sheet
View 9 Replies
View Related
Oct 9, 2008
I am trying to find a code that will copy a Range (MasterMinutes) from one Worksheet and then paste it on another worksheet titled "Master"
After it has been pasted I need to select the used area (EXCEPT for the first row) on the Master and Sort it by Columns B C and D (all ascending)
View 9 Replies
View Related
Aug 10, 2007
What VBA code will copy column A in Sheet 1 to column B in Sheet 3? The range of column A to be copied begins in row 2 and varies in length. Column A ends right before the last nonblank cell.
View 4 Replies
View Related
Jun 20, 2007
I have a worksheet that is composed a header row, and columns of data (client call results).
Each client is identified by a unique ID number in column A, a seperate column F has the 'call number, from 1 to x'.
So a client can have a single or multiple rows of data. Each client group of rows is sequenced with call 1 as line 1, call 2 at line 2, etc. etc. as your proceed down the rows.
The entire worksheet is already sorted (with a header row) ascending by client id and call #
What I want to accomplish is this
1 - copy the existing worksheet (CallRecords) to a new worksheet (CallToday)
2 - in the new worksheet, scan down (or up) from first data row (2) to the last row {ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row} and delete duplicate rows of the Client ID retaining the row with the max call number for that client id.
3 - sort the new worksheet (CallToday) ascending by column (G) date, and column (H) time.
View 6 Replies
View Related
Sep 5, 2006
I have to rewrite this code so that when i copy the sheet and paste it, to paste one Toggle button with its assigned macro. I don't want the whole workbook cause there are one combo box, another four toggle buttons and a command button, that i dont' need in the new file. So only to insert one toggle button with its macro.
Sub FileSaveCopyAs_Click()
Dim strFileName As String
Dim theNumber As Variant
Dim theFirm As String
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
theNumber = Sheets(1).Range("F13")
theFirm = Sheets(1).Range("E1")
strFileName = Right(theNumber, 4) & " " & theFirm
ActiveSheet. SaveAs Filename:="C:Documents and SettingsSecretaryDesktopFacturi" & strFileName & (".xls")
End Sub
View 9 Replies
View Related
Dec 30, 2011
Here is the code I have:
Windows("2.xlsm").Activate
Sheets("Report_P").Select
Sheets("Report_P").Copy After:=Workbooks("New_report.xlsx").Sheets(9)
2.xlsm is open. it contains a sheet called Report_P New_report.xlsx is open and has 9 existing sheets
Every time this tries to execute I get a "Run time error 1004 Copy Method of Worksheet Failed".
What is wrong with this code? I have an identical line in another macro, the only difference is that there are 5 sheets pre-existing, and that works fine.
View 6 Replies
View Related
Feb 20, 2013
I am trying to put together some code to have Excel to copy all the text files from a folder with numerous text files, all of which have identical formatting, to one worksheet. Furthermore, I need the copying event to occur every time the workbook is opened so that it will "refresh/overwrite" the worksheet with all of the files in the folder.
I have lifted some code from several sites which would seem to an untrained eye to accomplish what I am trying to get done, however I run into a file path error.
View 7 Replies
View Related
Jun 29, 2008
how to use VBA to write a formula in one sheet that refers to a fixed cell on the sheet to the right. The referenced sheet may have different names and it may be in a different order (i.e. not Sheet(2)) but it will always be one sheet to the right. I just want to have cell A1=(cell B2 one sheet to the right).
View 7 Replies
View Related
Dec 16, 2008
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code.
Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?
View 6 Replies
View Related
Nov 17, 2011
i have a workbook with over 600 worksheets and any vba code to do the following.... (each worksheet contains different number of lines)
At the moment the data is in columns a to d
What i need is the data currently in cell a1 (in each worksheet) to appear beside every line in that worksheet
Then i need to take all this data and put it onto one single worksheet .
View 2 Replies
View Related
Dec 14, 2011
I am using the following code to copy the contents of a DDE feed.
Code:
Private Sub Worksheet_Calculate()
Worksheets("Sheet2").Range("A1").EntireRow.Insert Shift:=xlDown
Worksheets("Sheet1").Range("A3:F3").Copy
Worksheets("Sheet2").Range("A1:F1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
What I want to do is only copy the row when the columns contain data, as it stands at the moment it's copying blank rows to sheet 2. Is there a way that I can do this?
View 3 Replies
View Related
