By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

How to create macro to copy worksheet and paste values only

P: 1
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
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 1,035
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

Post your reply

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