By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,404 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,404 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.
  9. Sheets(ActiveSheetValue).Copy After:=Sheets(ActiveSheetValue)
  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
  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
  28. 'This part hides columns on Right K thru R of new copied sheet
  30. Sheets(ActiveSheet.Name).Range("K:R").EntireColumn.Hidden = True
  32. '
  33. End Sub
Feb 21 '19 #1
Share this Question
Share on Google+
2 Replies

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

P: 150
After copying a sheet, what about "clear format" of all cells in the copied sheet?
Expand|Select|Wrap|Line Numbers
  1. Cells.ClearFormats
Dec 23 '19 #3

Post your reply

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