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
Bookmarks