VBA Code Query

[uzzimacro]

Hi All,

Ive recorded the following macro which does the function I wanted. I know that excel adds a lot of useless lines when you do this and since im trying to learn VBA, everytime I code something I try to learn and understand the code. So basically all it does is go to a specific tab and calculate something based on two columns then copy and pastes it in another tab in value form, goes back to the old tab and delete the column whee you originally calculated. However as I mentioned VBA recorder seems to add of useless lines, for me to understand it better does anyone know the below code written in its simplest format? Thnaks in advance.

Sheets("BIG Report").Select
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=RC[-12]-RC[-14]"
Range("Q6").Select
Selection.AutoFill Destination:=Range("Q6:Q55"), Type:=xlFillDefault
Range("Q6:Q55").Select
Selection.Copy
Sheets("REPORT D-2").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G13").Select
Sheets("BIG Report").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("REPORT D-2").Select
Range("F21").Select

 

[dmt32]

Hi,
Macro recorder is useful tool but does produce a lot of noise like Selection that can be normally be removed as it is seldom needed in code.

Here is one (untested) update to your post

 Sub Test()    
     Dim FillRange As Range
    
    Set FillRange = Sheets("BIG Report").Range("Q6:Q55")
    
    With FillRange
    With .Cells(1, 1)
        .FormulaR1C1 = "=RC[-12]-RC[-14]"
        .AutoFill Destination:=FillRange, Type:=xlFillDefault
    End With
        .Copy
    End With
       
    Sheets("REPORT D-2").Range("E6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                  SkipBlanks:=False, Transpose:=False
                                     
    Application.CutCopyMode = False
    
    FillRange.ClearContents


End Sub

Others here may have alternative solution

Dave

 

[wideboydixon]

I think you could shrink this quite a bit:

Public Sub uzzi()


With Sheets("BIG Report").Range("Q6:Q55")
    .Formula = "=E6-C6"
    Sheets("REPORT D-2").Range("E6:E55").Value = .Value
    .ClearContents
End With


End Sub

Basically, set the formula on the whole range (rather than using AutoFill) and then use the .Value property to copy to the destination before clearing the contents again. No selections and no switching tabs required.

WBD

 

[uzzimacro]

Thanks to the both of you! and Wideboydixon that's amazing how much it was simplified!

I have another similar query, the code below also Is very hard to learn from and feel like it can be simplified, its also very laggy and slow which is another reason itd be good to know to simplify these types of codes. this code opens up Bloomberg rate page copies and pastes the data into excel, copys the specific cell where the data is and then pastes it to the desried location and then deletes the copied data from the Bloomberg page


ub ExtractBloombergRateUSDEUR()
'
' NEWEXTRACTBLOOMBERGRATE Macro
'
'
Sheets("Data Reports").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://www.bloomberg.com/quote/EURUSD:CUR", Destination:=Range("$AB$32" _
))
.Name = "CUR"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=207
Range("AB233").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-237
Range("W4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AB32:AM301").Select
ActiveWindow.SmallScroll Down:=-138
Application.CutCopyMode = False
Selection.QueryTable.Delete
Selection.ClearContents
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.SmallScroll Down:=-18
Range("V9").Select
End Sub

 

[wideboydixon]

New question = new post. That way, other people get a chance to take a look. I often don't look at posts that already have a few replies.

WBD