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

[DavidH56]

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

 

[Yongle]

Here is a method to identify chart sheets in your workbook
The message box pops up for every chart sheet VBA finds

Sub Loop_ChartSheets()
    Dim Cht As Chart
    For Each Cht In ActiveWorkbook.Charts
        MsgBox Cht.Name
    Next Cht
End Sub

 

[DavidH56]

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]

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]

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]

This should do what you require

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

 

[DavidH56]

Thank you Yongle! That worked perfectly.