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!

 

[DreyFox]

Is there a way to keep the formatting when doing this? For example I'd like cells that were merged to stay merged when they're copied over

Any help is greatly appreciated!

 

[Fluff]

Best advice is. Do not used merged cells, they are an abomination & should be avoided like the plague.

 

[DreyFox]

Okay thank you! Is there a reason why? Do they mess with VBA?

 

[Fluff]

VBA does not like merged cells, if you use them you will be continually having to find workrounds to get things working.
Also they can cause problems with filtering & sorting.