Marcro Recorder is unexpectedly using standardWidth and GapWidth on non-chart worksheet

DavidH56

New Member
Joined
Jul 29, 2011
Messages
33
I recorded a macro to copy and paste vales from one worksheet to another (in the same workbook) then to copy formats from the column immediately to the left of the one that was just pasted. The Macro Recorded used StandardWidth and GapWidth in the code, which I wasn't expecting and haven't seen before.

"FormA_CA" is the worksheet I'm copying data from, and "2020 fuel rates" is the worksheet I"m copying to. Neither of these worksheets are supposed to be chart sheets nor do they contain charts. Is it possible that they are incorrectly labeled as chart sheets? Can someone please help me understand what's going on?

Thanks,

David

Recorded code below:

Sub CopyVal
'
' CopyVal Macro
'

'

StandardWidth("FormA_CA").Select
Range("B3:B16").Select
.
StandardWidth("2020 fuel rates").Select
. GapWidth := xlPasteValuesAndNumberFormats, := xlNone, := FALSE, := FALSE
Range("H5:H18").Select
Application. = FALSE
.
Range("I5").Select
. GapWidth := xlPasteFormats, := xlNone, := FALSE, := FALSE
StandardWidth("Inputs").Select
End Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Here is a method to identify chart sheets in your workbook
The message box pops up for every chart sheet VBA finds
VBA Code:
Sub Loop_ChartSheets()
    Dim Cht As Chart
    For Each Cht In ActiveWorkbook.Charts
        MsgBox Cht.Name
    Next Cht
End Sub
 

DavidH56

New Member
Joined
Jul 29, 2011
Messages
33
Yongle, Thanks for the reply and a apologies for the lateness of my response, we had a death in the family.

I ran the code you provided and it confirmed that I have no charts in my workbook, so I'm puzzled as to why the macro recorder would use StandardWidth and GapWidth. I'm copying a range from one worksheet and pasting values into another, so I would expect to see standard Copy and Paste Special/Values code...what am I missing?

Thanks,

David
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Condolences on your loss

I have no idea what the macro recorder did or why

What are you trying to copy?
- Sheet name & range?
Where is the paste cell?
- Sheet name and cell ref?
What is being pasted?
- everything? values? formats ? column widths?
 

DavidH56

New Member
Joined
Jul 29, 2011
Messages
33

ADVERTISEMENT

Thank you.

I was trying to record the steps to copy from sheet “FormA_CA” (cells B3:B16), then paste as values to sheet “2020 fuel rates” cell J5.

Row 5 of sheet “2020 fuel rates” has monthly headers, e.g. 1/1/2020 in Col C to 6/1/2020 in cell I; so next step would be to modify the code to paste into the first unused column in row 5.

I'm not great with VBA but I've recorded a number of marcos and I've never seen this behavior before.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
This should do what you require
VBA Code:
Sub CopyCells()
    Dim rng1 As Range, rng2 As Range
    Set rng1 = Sheets("FormA_CA").Range("B2:B16")
'find last used cell in row 5, offset 1 column , resized to same size as other range
    Set rng2 = Sheets("2020 fuel rates").Cells(5, Columns.Count).End(xlToLeft).Offset(, 1).Resize(rng1.Rows.Count)
'copyrange
    rng1.Copy rng2
'convert to values
    rng2.Value = rng2.Value
End Sub

RECORDER PROBLEM
Create a new workbook with the same 2 sheet names and try recoding the macro again
- the recorder will probably record something similar to what you expect
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

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
Top