Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA Copy/Paste Special to New Worksheet

Posted on 2006-06-05
4
Medium Priority
?
7,903 Views
Last Modified: 2012-05-05
Can someone please assist me with a VBA routine to duplicate a worksheet to a new worksheet, using paste special?

Thanks much,
Seth
0
Comment
Question by:swilschutz
  • 2
  • 2
4 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 16838791
Hi swilschutz,
You didn't specify whether the copy should be to a new workbook or to the same one. Here are both alternatives:

Sub CopySheet()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Copy     'Create a new workbook from the worksheet
'Worksheets.Add after:=Worksheets(Worksheets.Count)  'Create a new worksheet in the same workbook
ws.Cells.Copy ActiveSheet.Cells      'This statement needed in case a cell contains more than 255 characters. It also copies the sheet formatting.
End Sub

Hoping to be helpful,

Brad
0
 

Author Comment

by:swilschutz
ID: 16842134
Brad,

I'm sorry; I reread my question and I wasn't very clear.

I need the new worksheet in the same workbook, but I would like to paste special (values), and thus strip the formatting.

The whole process is this:
1. Copy data from web site into Excel sheet.  When copied, it comes with merged cells, and strange formatting.
2. Copy and paste special (values) into new worksheet.
3. Clean data and remove unnecessary data -- already programmed a VBA routine to do this.

Can I modify the VBA routine above to do a Paste Special with xlValues?

Thanks,
Seth
0
 

Accepted Solution

by:
swilschutz earned 0 total points
ID: 16842525
0
 
LVL 81

Expert Comment

by:byundt
ID: 16842667
Seth,
Since you found your own answer, the right way to handle the situation is to refund your points (500) and give you credit for the answer. I've taken care of this for you.
byundt--Excel TA Page Editor
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question