New Tab Copy Paste Closed

Question

Hello, 
Being extremely new to VBA I am trying to create a macro that Creates a new worksheet and then have it copy and paste data into the new worksheet from a current worksheet. This is what I have. Any help would be great. 

Sub Macro1()

    On Error GoTo MyError

    Sheets.Add
    ActiveSheet.Name = Format(DateAdd("M", -2, Now), "MMM-YY")
    
    Exit Sub

MyError:
    MsgBox "There is already a sheet called that."
   

End Sub
Sub CopyPaste()


' CopyPaste Macro

    Sheets("Current Risk Dist Sum").Select
    Range("A7:T171").Select
    Selection.Copy
    ActiveSheet.Paste

End Sub



System Configuration: Windows / Chrome 61.0.3163.91

ac3mark · Answer

What happens when you actually record the MACRO and look at the VBA the sheet creates for itself?  If you are new to VBA, then I would like to go ahead and encourage you to RECORD your own MACROS, then reverse engineer the code.  Then figure out a way to change that STATIC code, into a dynamic solution with the aid of VARIABLES.

It is kinda FUN!

I have recorded this:

Sub Macro1()'' Macro1 Macro''    Sheets("Sheet1").Select    Selection.Copy    Sheets.Add After:=ActiveSheet    ActiveSheet.Paste    Sheets("Sheet2").Select    Sheets("Sheet2").Name = "Here is the new tab name"End Sub

Then I would change it to what?  If I make the first change, finish it up, OK?

Sub Macro1()'' Macro1 Macro''    Sheets("Sheet1").Select    Selection.Copy    Sheets.Add After:=ActiveSheet    ActiveSheet.Paste    ActiveSheet.Select    Sheets("Sheet2").Name = "Here is the new tab name"End Sub

Post back if you need anymore help!  Have Fun!

New Tab Copy Paste

1 response

Similar discussions

Subscribe To Our Newsletter!