Code anomaly

Nygie

Board Regular
Joined
Apr 15, 2015
Messages
50
Good afternoon all.

I have the following code in a spreadsheet and on clicking a command button it simply saves the file using a cell reference for the filename. Selects an area, copies and pastes it using number values and formats. Then it deletes the command button. Could someone test this out as it used to work perfectly. Recently I added more columns to the right of E column but this should have no affect. But stepping through the code it was as it should until "Range("B3:C15").Select" where it proceeds to select B3:E15 and then copies and pastes that range.
Has anyone come across this before?
Many thanks
Nigel

Private Sub CommandButton1_Click()
'Dim Path As String
Dim filename As String

Path = "X:\Corporate Clients Excel\Weekly takings\"
filename = Range("G3")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx", FileFormat:=51
Range("B3:C15").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Shapes("CommandButton1").Delete
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sounds like you have some merged cells in that range.
 
Upvote 0
Also note you don't actually need to select the range:

Code:
Range("B3:C15").Copy
Range("B3:C15").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

or even:
Code:
With Range("B3:C15")
    .Copy
    .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
 
Upvote 0
Many thanks Fluff, you are indeed correct.
Thanks RoryA, always good to optimise :)
 
Upvote 0
Does the code Rory posted solve the issue?
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top