468,315 Members | 1,441 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,315 developers. It's quick & easy.

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 2797
Luuk
1,043 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
241 128KB
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

Post your reply

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

Similar topics

1 post views Thread by Steve | last post: by
6 posts views Thread by XmlAdoNewbie | last post: by
2 posts views Thread by Matt | last post: by
6 posts views Thread by NuB | last post: by
1 post views Thread by Rich Kayton | last post: by
17 posts views Thread by Steve | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.