CODEWITHSUNDEEP
vbaexcel
Leeoneal
Leeoneal

Reputation: 3

How to copy range of cells using inputbox and paste to newly created sheet

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

Upvotes: 0

Views: 1043

Answers (4)

Cris
Cris

Reputation: 1

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

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

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

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 0

Rob Anthony
Rob Anthony

Reputation: 1813

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

Upvotes: 0

Related Questions