How do I get Excel to copy a range of cells into another cell?

[DreyFox]

Hello!

This is the functionality I would like. First the user clicks a command button, which will let them name the sheet that they will create, then copy a certain range of the current sheet into the new sheet keeping its formatting. What I have tried (code):

Private Sub CommandButton1_Click()
Dim val As String

val = InputBox("Enter the configuration name for the current selection")

Sheets.Add(After:=Sheets(Sheets.Count)).Name = val

Worksheets("Feed Configurator").Range("A108:M109").Copy Worksheets(val)

End Sub

It unfortunately does not work. Any help or assistance would be greatly appreciated! Thank you!

 

[Fluff]

Where in the new sheet should the range go?

 

[DreyFox]

Where in the new sheet should the range go?

Always the first rows and columns.

 

[maras]

Try.

Worksheets("Feed Configurator").Range("A108:M109").Copy Worksheets(val).[a1]

 

[DreyFox]

Try.

Worksheets("Feed Configurator").Range("A108:M109").Copy Worksheets(val).[a1]

It says "Run time error 1004, Application defined or object defined error"

 

[Fluff]

How about

Private Sub CommandButton1_Click()
Dim ShtName As Variant

ShtName = InputBox("Enter the configuration name for the current selection")
If ShtName = "" Then Exit Sub
Sheets.Add(After:=Sheets(Sheets.count)).Name = ShtName

Worksheets("Feed Configurator").Range("A108:M109").Copy Worksheets(ShtName).Range("a1")

End Sub

You should not use VBA keywords like Val as the names of variables of procedures.

 

[DreyFox]

How about

Private Sub CommandButton1_Click()
Dim ShtName As Variant

ShtName = InputBox("Enter the configuration name for the current selection")
If ShtName = "" Then Exit Sub
Sheets.Add(After:=Sheets(Sheets.count)).Name = ShtName

Worksheets("Feed Configurator").Range("A108:M109").Copy Worksheets(ShtName).Range("a1")

End Sub

You should not use VBA keywords like Val as the names of variables of procedures.

Thank you! This works!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[DreyFox]

Glad we could help & thanks for the feedback.

Sorry for opening this thread again, but is there a way to copy the values only (for example copy the cell values, but not the formulas)?

 

[Fluff]

You can use

Worksheets(ShtName).Range("A1:M2").Value = Worksheets("Feed Configurator").Range("A108:M109").Value