+ Reply to Thread
Results 1 to 2 of 2

Paste copied range into added worksheet

  1. #1
    Registered User
    Join Date
    12-02-2014
    Location
    Louisville, KY
    MS-Off Ver
    2013
    Posts
    1

    Paste copied range into added worksheet

    Hi all, first time posting. Thanks in advance for any help that comes from this, I've been reading through forums for a while, but decided to become a more active participant. for experience sake, I consider myself a newb. So here goes:

    In a nutshell, I am trying to create a Macro which, once a button is clicked, a range is selected, copied and pasted onto a new sheet added within my workbook. Each new sheet is named "Results1", "Results2", etc. I was able to paste my copied data when I only had one results sheet, but now that I am trying to allow for multiple results to be in the same workbook, I am struggling with how to tell VBA which worksheet to paste the data on. I have included what I have as code so far. Any help at all would be much much appreciated!

    Private Sub CommandButton1_Click()

    Dim varResponse As Variant

    varResponse = MsgBox("Have you input all the desired codes?", vbYesNo, "Selection")
    If varResponse <> vbYes Then Exit Sub

    Application.ScreenUpdating = False


    ' To select and copy the references on the input tab
    Worksheets("Input").Range("C5:c" & Cells(Rows.Count, "C").End(xlUp).Row).Select
    Selection.Copy
    ' To add a new worksheet to paste the results in

    Sheets("Master").Visible = True
    Sheets("Master").Copy After:=Worksheets(Worksheets.Count)
    ActiveWindow.ActiveSheet.Name = "Results" & Worksheets.Count - 3
    Sheets("Master").Visible = False

    'Paste the values onto the newly added sheet
    Sheets("Results" & Worksheets.Count - 3).Range ("B5")
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Application.ScreenUpdating = True

    End Sub

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Paste copied range into added worksheet

    While unconventional, how you are naming your sheets will work. The problem you are running into is here:

    Please Login or Register  to view this content.
    You are technically missing a .Select
    Please Login or Register  to view this content.
    But it can just be removed entirely to be
    Please Login or Register  to view this content.
    Here is my suggestion on your code:
    Please Login or Register  to view this content.
    Last edited by stnkynts; 12-02-2014 at 02:26 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Protection to be added in copied worksheet in the macro
    By graiggoriz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2014, 03:42 AM
  2. [SOLVED] Copy/Paste from a Filtered Range and Insert copied data NOT overwrite
    By Simon.Ward in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-12-2013, 11:14 AM
  3. [SOLVED] VBA Code to Paste copied range as a picture
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 01:51 AM
  4. [SOLVED] Copy row of data, paste in another worksheet, delete some of the cells copied
    By dev111ski in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-14-2012, 05:58 PM
  5. looking for an empty row to paste a range of copied cells
    By jayers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2009, 10:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1