Need help shortening this excel 2013 macro

excel vba disable screen update and calculation
excel 2016 slow macros
excel vba efficient copy paste
how to improve vba skills
excel vba slow down macro
activesheet displaypagebreaks false
excel macro running really slow
application displaystatusbar false

This isn't complex by far but I'm only a novice at excel macros. I've found online and edited this for my use but I know it's so long. The single ranges all refer to the same cell which is just the value of =today(). I know that can be integrated, I just don't know how. The rest copies a row and pastes it over at the bottom of specific rows, one for each employee. I'm sure there are even better ways to do this since the rows being copied are only there for this code and isn't the main data source. But one step at a time. Lol

Sub LastRowDtDataTEST()
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Long
Set wb = ActiveWorkbook
Set ws = ThisWorkbook.Sheets("Buyer Trend Metrics")
ws.Select

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Range("J" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B61:H61").Copy
LastRow = Cells(Rows.Count, "K").End(xlUp).Row ' get last row with data in column "K"
Range("K" & LastRow + 1).PasteSpecial Paste:=xlPasteValues ' paste values

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "S").End(xlUp).Row
Range("S" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B62:H62").Copy
LastRow = Cells(Rows.Count, "T").End(xlUp).Row
Range("T" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "AB").End(xlUp).Row
Range("AB" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B63:H63").Copy
LastRow = Cells(Rows.Count, "AC").End(xlUp).Row
Range("AC" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "AK").End(xlUp).Row
Range("AK" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B64:H64").Copy
LastRow = Cells(Rows.Count, "AL").End(xlUp).Row
Range("AL" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "AT").End(xlUp).Row
Range("AT" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B65:H65").Copy
LastRow = Cells(Rows.Count, "AU").End(xlUp).Row
Range("AU" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "BC").End(xlUp).Row
Range("BC" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B66:H66").Copy
LastRow = Cells(Rows.Count, "BD").End(xlUp).Row
Range("BD" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "BL").End(xlUp).Row
Range("BL" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B67:H67").Copy
LastRow = Cells(Rows.Count, "BM").End(xlUp).Row
Range("BM" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "BU").End(xlUp).Row
Range("BU" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B68:H68").Copy
LastRow = Cells(Rows.Count, "BV").End(xlUp).Row
Range("BV" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "CD").End(xlUp).Row
Range("CD" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B69:H69").Copy
LastRow = Cells(Rows.Count, "CE").End(xlUp).Row
Range("CE" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B58").Copy  ' Copies the value of =Today() from a cell. Would be easier to just integrate today's date instead.
LastRow = Cells(Rows.Count, "CM").End(xlUp).Row
Range("CM" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

Range("B70:H70").Copy
LastRow = Cells(Rows.Count, "CN").End(xlUp).Row
Range("CN" & LastRow + 1).PasteSpecial Paste:=xlPasteValues

End Sub

There's a pattern to how you're copying/pasting.

Copying every row, pasting to every 9th column after column 10.

I've added two lines for finding the last row - either find it once and paste everything to that row, of find it before you copy each time. Uncomment whichever you prefer.

This will copy B61:H61 to K:P on the last row (with date in J), then B62:H62 to T:Z with the date in R.

The date will also appear correctly formatted rather than as a number.

Public Sub WhateverYouWantToCallIt()

    Dim x As Long, y As Long
    Dim lLastRow As Long

    With ThisWorkbook.Worksheets("Buyer Trend Metrics")

        'This will set the same last row for each copy.
        lLastRow = .Cells(.Rows.Count, 10).End(xlUp).Row + 1

        y = 10
        For x = 61 To 70

            'This will set the last row on each set of data.
            'lLastRow = .Cells(.Rows.Count, y).End(xlUp).Row + 1

            .Cells(lLastRow, y) = Date

            .Range(.Cells(lLastRow, y + 1), .Cells(lLastRow, y + 7)) = _
                .Range(.Cells(x, 2), .Cells(x, 8)).Value

            '-OR-
            '.Range(.Cells(x, 2), .Cells(x, 8)).Copy
            '.Cells(lLastRow, y + 1).PasteSpecial Paste:=xlPasteValues

            y = y + 9
        Next x
    End With

End Sub

Need help shortening this excel 2013 macro, Need help shortening this excel 2013 macro. excel macros how to speed up macros in excel 2016 excel vba slow down execution excel macros running slow in  I'm a database developer who knows Access VBA. Excel VBA, very little. This is what I need to do: 1) On a worksheet, go to last row - 1 (second to last row) [blanks have already been deleted] 2) COPY. 3) CUT. 4) INSERT CUT ROW into Row 12. Probably very simple but all this positional stuff is a mystery to me. Thanks!

Here are some things for you to look at...

  1. ALWAYS use Option Explicit. See here for an explanation.
  2. When you're performing an action such as copying data, it's extremely helpful to be very clear in defining the source and destination of the data. This includes defining which Workbook the data is going to. You'll thank me later for building this habit now.

As an example:

Dim srcWB As Workbook
Dim dstWB As Workbook
Set srcWB = ThisWorkbook
Set dstWB = ThisWorkbook

Dim srcWS As Worksheet
Dim dstWS As Worksheet
Set srcWS = srcWB.Sheets("Sheet1") ' <--- you didn't specify this in your code
Set dstWS = dstWB.Sheets("Buyer Trend Metrics")
  1. When you're performing the same (or very similar) actions over and over, it's the perfect situation to create a separate function that will perform the action for you. When you break out this section of code, it's called "functional isolation". This means that if you have a problem to fix, you only have to fix it in one place instead of finding all the different spots in your code that do the same thing.

In your case, you are performing a copy from one range of cells to another range of cells. So breaking that out into a separate routine looks like this:

Private Sub CopyMyData(ByRef fromData As Range, ByRef toData As Range)
    Dim lastrow As Long
    With toData.Parent
        lastrow = .Cells(.Rows.Count, toData.Column).End(xlUp).Row
    End With

    fromData.Copy
    toData.Cells(lastrow).PasteSpecial Paste:=xlPasteValues
End Sub

Notice here how I'm using variable names that describe what the code does (fromData and toData). This makes it clear what's happening.

Put it all together and your code will look something like this:

Option Explicit

Public Sub StartCopying()
    Dim srcWB As Workbook
    Dim dstWB As Workbook
    Set srcWB = ThisWorkbook
    Set dstWB = ThisWorkbook

    Dim srcWS As Worksheet
    Dim dstWS As Worksheet
    Set srcWS = srcWB.Sheets("Sheet1") ' <--- you didn't specify this in your code
    Set dstWS = dstWB.Sheets("Buyer Trend Metrics")

    CopyMyData fromData:=srcWS.Range("B58"), toData:=dstWS.Range("J:J")

    CopyMyData fromData:=srcWS.Range("B61:H61"), toData:=dstWS.Range("K:K")

    CopyMyData fromData:=srcWS.Range("B58"), toData:=dstWS.Range("S:S")

    CopyMyData fromData:=srcWS.Range("B61:H62"), toData:=dstWS.Range("T:T")
End Sub

Private Sub CopyMyData(ByRef fromData As Range, ByRef toData As Range)
    Dim lastrow As Long
    With toData.Parent
        lastrow = .Cells(.Rows.Count, toData.Column).End(xlUp).Row
    End With

    fromData.Copy
    toData.Cells(lastrow).PasteSpecial Paste:=xlPasteValues
End Sub

Need help shortening this excel 2013 macro - excel - iOS, Need help shortening this excel 2013 macro - excel. The rest copies a row and pastes it over at the bottom of specific rows, one for each employee. I'm sure  Click the Microsoft Office Button, and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode.

  1. Do not double space every single line. You should use these as strategic separators, not the standard. This isn't MLA.
  2. Use a worksheet variable to quickly refer to your sheets (ws refers to the sheet that has the cells to be copied and ds (destination sheet) refers to the sheet where the cells are to be pasted
  3. You can use a value transfer instead of a copy/paste which does not require multiple lines as well

In general, when shortening code, you want to look for repetitiveness. I can see that you are constantly copying the value from Range("B58") so you can also shorten this. You have comments saying you want the value to just be today so you can just do something like

ds.Range("?") = Today Repeat as needed


Option Explicit

Sub LastRowDtData()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim ds As Worksheet: Set ds = ThisWorkbook.Sheets("Buyer Trend Metrics")
Dim LR As Long

LR = ds.Range("J" & ds.Rows.Count).End(xlUp).Offset(1)
ds.Range("J" & LR).Value = ws.Range("B58").Value

LR = ds.Range("K" & ds.Rows.Count).End(xlUp).Offset(1)
ds.Range("K" & LR).Resize(1, 7).Value = ws.Range("B61:H61")

LR = ds.Range("S" & ds.Rows.Count).End(xlUp).Offset(1)
ds.Range("S" & LR).Value = ws.Range("B58").Value


'Repeat for below ranges
'------------------
Range("B62:H62").Copy
Range("B58").Copy
Range("B63:H63").Copy
Range("B58").Copy
Range("B64:H64").Copy
Range("B58").Copy
Range("B65:H65").Copy
Range("B58").Copy
Range("B66:H66").Copy
Range("B58").Copy
Range("B67:H67").Copy
Range("B58").Copy
Range("B68:H68").Copy
Range("B58").Copy
Range("B69:H69").Copy
Range("B58").Copy
Range("B70:H70").Copy


End Sub

How to shorten recorded Macro Please?, Hi, I would very much appreciate if anyone can assist me to shorten recorded macro.as you can see from the macro I'm copying (Q3:V70) Apr 1, 2013 Learn Excel 2010 - "Next Invoice Number": Podcast #1505  Two ways to speed up Excel macros by Susan Harkins in Microsoft Office , in Software on August 14, 2017, 1:56 PM PST When it comes to executing actions and performing tasks, faster is always better.

Excel 2013 VBA and Macros: Excel 2013 VBA and Macros _p1, Excel 2013 VBA and Macros _p1 Bill Jelen, Tracy Syrstad. You can shorten this code by using [S17] as a shorter way to refer to Left Referring to a Specific Chart The macro recorder has an unsatisfactory way of writing code for the chart​  Choose File→Options or press Alt+FT to open the Excel Options dialog box. Click the Customize Ribbon tab, select the Developer check box under Main Tabs in the Customize the Ribbon list box on the right side of the dialog box, and then click OK. Even if you don’t add the Developer tab to the Ribbon, the Excel Status bar at the bottom of the Excel 2013 program window contains a Record Macro button.

Two ways to speed up Excel macros, You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please  Hi I am new to VBA I have started creating a new Spreadsheet which for me worked fine but now my whole department want s to use this. 1st i would like to shorten the macro as for me i was only using it for 50 people and they want to increase this up to 900 or so. 2nd I would like the macro to

Excel 2013: Macros in Depth Training, And there are some lines that we need to adjust. Let me Let us delete that, and we could even shorten that if we wanted to, but that is good enough for now. Using the VBA Help files. If you learn how to use the Excel Help system effectively, it will often be the fastest and easiest way to get help on a topic. You need to remember two basic tenants of the Excel Help system: Location matters when asking for help. Excel has two Help systems. One provides help with Excel features and the other provides

Comments
  • Well the first obvious step is to not double space your code
  • Then you can declare a work sheet variable to replace the repetitive With Block
  • You can also use this for each paste line:.Range ("J" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
  • I tried that line, Davesexcel, but it doesn't work without With which I'd like to get away from like Urdearboy mentioned, I found a WS variable to try. This works, but I feel these lines could be condensed further, especially the date. Can any of these ranges for the dates be like B1, C4, D9 etc etc? I edited my code above.
  • Sounds like a job for Code Review. SO is for code problems.
  • Just noticed in your pre-edited code that there's two sheets. Copying from one and pasting to the other? Just prefix the PasteSpecial line with a reference to the sheet you're pasting to (same with lLastRow).
  • I like this script, but I need it to paste each row onto the bottom row of the column. Each employee has different numbers of rows like Date | Col 1 | Col 2 | Col 3 | etc etc. So each day i do this, it makes another row under the headers, so I need help there. I don't think it changes anything but this is all on 1 sheet.
  • Comment out the first lLastRow line and uncomment the second - it will then find the last row for each section.
  • Awesome, it works great! This one also seems like the easiest to update should I have to move things around.