VBA: Button that create a new worksheets and copy data to the new one

[hebbebe]

Hello!

Im having a bit of a problem with my code, and it might be very easy for you. Im very new at this.

Problem: I want a commandbutton that create new worksheets. The new worksheets gets its name from the value in CELL B12.
And when it have created the new worksheet, it should copy a range of cells from the mainsheet called "Indata" and paste it in the new sheet.

Code

Private Sub CommandButton1_Click()


Dim a As String
a = ActiveSheet.Cells(12, 2).Value
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = a


Range("A1:J30").copy Worksheet("a").Range("A1")


End Sub

It works with creating the new sheet, but wont paste the data.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub hebbebe()
   Dim Nme As String
   
   Nme = Range("B12").Value
   Sheets.Add(, Sheets(Sheets.Count)).Name = Nme
   Sheets("Indata").Range("A1:J30").Copy Sheets(Nme).Range("A1")
End Sub

 

[hebbebe]

Im still getting the same problem. It creates the worksheet, but it's blank.

 

[salim hasan]

Try this macro

Option Explicit


Private Sub CommandButton1_Click()


Dim Main_sheet As Worksheet
Set Main_sheet = Sheets("ABCD") ' Change ABCD to your Main sheet's name
Dim a As String                   'i.e The paper we copy from
a = ActiveSheet.Cells(12, 2).Value
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = a
'Main_sheet.Range("A1:J30").Copy  Sheets(a).Range("A1")
  '''''''''''''''''''''  OR
Sheets(a).Range("A1").Resize(30, 10).Value = _
Main_sheet.Range("A1").Resize(30, 10).Value
End Sub

 

[Fluff]

Im still getting the same problem. It creates the worksheet, but it's blank.

Do you get any error messages?
Also is the new sheet named correctly?

 

[hebbebe]

Well that did the job! Thanks for the help.