473,216 Members | 1,477 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

How to create macro to copy worksheet and paste values only

I am trying to create a macro that copies the active worksheet to a new worksheet and paste values only. For instance, Sheet1 is the active sheet. I want to make Sheet1(2) with values only. Additionally, I evaluate each row of the Sheet1(2) to look for a value of X in a cell in each row. If there is an X, then the row is deleted.

My attached code kinda works. It copies Sheet1 to Sheet1(2) and deletes all of the rows with an X in a particular cell, the problem is it is a duplicate copy with the formulas. I just want it to copy and paste the values (so it would be a static worksheet of values only, no formulas).

I'm not the best at programming so any suggestions I really appreciate.

Expand|Select|Wrap|Line Numbers
  1. Sub SPACER_Button4_Click()
  2. ' Compile Button to Generate Quote
  3. '
  4. 'variables definitions
  5. ActiveSheetValue = ActiveSheet.Name
  6. '
  7. 'This section creates a copy of the active worksheet and names it with the next corresponding number.
  8.  
  9. Sheets(ActiveSheetValue).Copy After:=Sheets(ActiveSheetValue)
  10.  
  11. 'This section should look for X value in each row, column 4. If value equals X, it deletes the row on the copied sheet
  12.  
  13. Dim i As Integer
  14. i = 26
  15. Do Until i > 300
  16.     If ActiveSheet.Cells(i, 11).Value = "X" Then
  17.         Rows(i).Delete
  18.         Skip = True
  19.     End If
  20.     '
  21.     If Skip = False Then
  22.         i = i + 1
  23.     End If
  24.     '
  25.     Skip = False
  26. Loop
  27.  
  28. 'This part hides columns on Right K thru R of new copied sheet
  29.  
  30. Sheets(ActiveSheet.Name).Range("K:R").EntireColumn.Hidden = True
  31.  
  32. '
  33. End Sub
Feb 21 '19 #1
2 3919
Luuk
1,047 Expert 1GB
You seems to have gotten pretty far so far (based on the 'I'm not the best at programming')

For the question 'How to paste values in Excel', I would like to suggest to Google for 'excel macro paste as values'.

It will for sure give some examples of how to do that!
(Ok, I know, I CAN put this code here but 😊)

Secondly: why are you copying a whole sheet and after that deleting certain rows?
Is it not simples to copy just the rows you need?
Feb 23 '19 #2
SioSio
272 256MB
After copying a sheet, what about "clear format" of all cells in the copied sheet?
Expand|Select|Wrap|Line Numbers
  1. Cells.ClearFormats
  2.  
Dec 23 '19 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Wladimir Borsov | last post by:
I want to mark certain text on a web page in InternetExplorer and then copy and paste it into a normal text editor. With the copied text not only the pure text should be transferred but also the...
1
by: Steve | last post by:
Does anyone know a way to copy and paste or similar a list of fields in an Excel worksheet into Access to create a table? Example, Sheet1 has this list in A1 to A7: CustomerID FirstName LastName...
6
by: XmlAdoNewbie | last post by:
Hi All, I would like to put a method for copy, cut and paste into my application and this seems to be easy enough except that it's not working the way i would like it to, I thought someone might...
2
by: Matt | last post by:
Hello, I have a copy button and a paste button. What code should I add to the copy button and the paste button to do it's work? Thanks, Matt
6
by: NuB | last post by:
I have a winform and a menu on the form, It allows the users to copy and paste text from text boxes. I never had to do this before, how can I copy and paste text from one box to another?
1
by: Rich Kayton | last post by:
Is there a way to copy values from one table and paste into the current table. For instance, in an order entry system I want to paste the current unit cost into the order. I don't want this to be...
17
by: Steve | last post by:
I'm trying to code cut, copy, and paste in vb 2005 so that when the user clicks on a toolbar button, the cut/copy/paste will work with whatever textbox the cursor is current located in (I have...
0
by: gcardozo | last post by:
Could anyone help me with the task of copying and pasting the values of changing cell references in excel using code for a macro?(macro to copy and paste values within a currently selected column). ...
5
by: phill86 | last post by:
Hi I have a main form that holds records for scheduled meetings, date time location etc... in that form i have a sub form that has a list of equipment resources that you can assign to the meeting in...
1
by: Kyle Pellum | last post by:
I am trying to write code to copy and paste a worksheet from one workbook to another. The thing I cannot figure out is how to reference the workbook to be copied because the name will be different...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.