0

It's my 1st time here and needed some help. not good with coding as I just started with the help of youtube. I saw a post here that helps you create sheets with VBA. and this is what i started on. MAybe you can help me along the way. 

Sub cutcell()

Dim number, name As Variant

'ask the number of cell and name of new sheet

number = InputBox("Number of cells to cut")
name = InputBox("Name of new sheet")

' select Cell from A1 to the number of sheet inputted
Range("A1:A(number)").Select
Selection Cut

'creates a new worksheet
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = name.Value ' renames the new worksheet
Range("A1").Select
activeheet.Paste

End Sub
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Leeoneal
  • 3
  • 2
  • 1
    welcome to Stack Overflow. You need to improve your question as it isn't a question! What is the problem? What do you want help with? The more you explain, the better the help you will get. – Rob Anthony Jul 30 '17 at 07:55
  • Hi Rob, the problem is. its not working. the code is imcomplete. – Leeoneal Jul 30 '17 at 08:21

4 Answers4

1

Try it like this...

Sub cutcell()
Dim wsNew As Worksheet
Dim RngToCut As Range
Dim number, NewName As Variant

Application.ScreenUpdating = False

'ask the number of cell and name of new sheet
number = Application.InputBox("Number of cells to cut", Type:=1)    'This will only allow a number input

If number = 0 Then
    MsgBox "You didn't enter  number.", vbCritical
    Exit Sub
End If

Set RngToCut = Range("A1:A" & number)

'Ask user to input name of the New Sheet
NewName = InputBox("Name of new sheet")

If NewName = "" Then
    MsgBox "You didn't input the name of New Sheet.", vbCritical, "New Sheet Name Missing!"
    Exit Sub
End If

Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
wsNew.name = NewName

RngToCut.Cut wsNew.Range("A1")
Application.ScreenUpdating = True
End Sub
Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22
  • Thanks sktneer. everyone here is awesome – Leeoneal Jul 30 '17 at 09:14
  • What will you do if the user enters "ttt" or some other string in he number section? If you want to add error handling, at least use the Application.InputBox – Shai Rado Jul 30 '17 at 09:14
  • 1
    @ShaiRado Please look the code. For number input I have already used Application.InputBox. ;) Also do you remember there is no end of If and buts as I mentioned it in another question. :) – Subodh Tiwari sktneer Jul 30 '17 at 09:18
  • @ShaiRado Thanks. :) – Subodh Tiwari sktneer Jul 30 '17 at 09:20
  • Thanks both of you. now that it worked. i want to try if it can be automatic. i'll have 1 sheet with the names of my team mate. and the other sheet with the cases that we will work on. which is equally divided. is it possible? like if i have 10 names on sheet1 and all the cases will be on sheet2 (1000 cases). when i hit the button. it will create 10 new sheets with our names. and the cases will automatically divided to the 10 of us (100 each)? thanks in advance – Leeoneal Jul 30 '17 at 12:01
  • @Leeoneal I don't understand your behavior. Why did you deselect the answer? Are you not clear about which solution would work for you? – Subodh Tiwari sktneer Jul 30 '17 at 15:52
  • @sktneer i apologize for deselecting. i didnt know that you can only choose one. noob here hehe – Leeoneal Aug 05 '17 at 15:47
0

One problem is here:

Range("A1:A(number)").Select

You need to work out the range but putting it in quotes takes it as literally what you say. Try this:

Range("A1:A" + number).Select

Another problem is here:

activeheet.Paste

You have misspelled ActiveSheet. Try:

ActiveSheet.Paste
Rob Anthony
  • 1,743
  • 1
  • 13
  • 17
0

It's better if you stay away from Select, Selection and ActiveSheet, and instead use fully qualified Range and Worksheets objects.

Read here How to avoid using Select in Excel VBA .

Also, the Cut>>Paste is a 1-line syntax (see code below), just try to keep the 2 actions as close as can be (create the new Worksheet object before this action).

Code

Option Explicit

Sub cutcell()

Dim number As Long, name As String
Dim OrigSht As Worksheet
Dim NewSht As Worksheet

'ask the number of cell and name of new sheet
number = InputBox("Number of cells to cut")
name = InputBox("Name of new sheet")

' save the currebt active sheet
Set OrigSht = ActiveSheet ' <-- I still prefer to use Worksheets("SheetName")

' first create the new worksheet
Set NewSht = Sheets.Add(After:=Sheets(Sheets.Count))
NewSht.name = name ' renames the new worksheet

' select Cell from A1 to the number of sheet inputted , use Cut>>Paste in 1 line
OrigSht.Range("A1:A" & number).Cut Destination:=NewSht.Range("A1")

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • Super Thanks Shai – Leeoneal Jul 30 '17 at 09:08
  • Sub AddSheets2() Dim myRange2, sheetname As Variant myRange2 = InputBox("Please Select a Range") Range("A1:A" + myRange2).Select Selection.Cut sheetname = InputBox("Name of new sheet") Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).name = sheetname ActiveSheet.Paste End Sub – Leeoneal Jul 30 '17 at 09:08
  • this is what I did. but yours is way more better and clean looking – Leeoneal Jul 30 '17 at 09:08
0

A inputbox type 8 could be used for that purpose, since it lets user pick the desired range.

You might find other examples in here.

Cris

Cris
  • 1
  • thanks cris. that was the 1st thing i tried. i want the user to just input the number of cells he wants withouht looking at the sheet. but thanks though – Leeoneal Jul 30 '17 at 11:57