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

hebbebe

New Member
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

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
How about
Code:
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
 

salim hasan

Board Regular
Try this macro
Code:
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
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top