Report

New Tab Copy Paste

Ask a question Justinjwx - Last answered on Sep 29, 2017 at 06:28 PM by ac3mark
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



Helpful
+0
plus moins
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!
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!