Editing the code for a recorded Macro

Question

I am brand new to using macros and code, and am running into a small problem. I have a sheet where I update values along a row. Since these updates are part of a process, I want excel to record my row as a sort of history in next sheet in my workbook. I recorded a macro where when I press cntrl+r, it copies the first row from my data sheet, inserts a row onto the history page, and then copies the data onto that new row. THe only problem I that my data sheet will have multiple rows of data, and I would like the macro to copy my selected row, not just the first row everytime. I have put the code below.

Thank you!

Sub RecordTracker()
RecordTracker Macro
Records the updated row as a history row in Documentation Sheet
Keyboard Shortcut: Ctrl+r

    Sheets("Documentation").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Tracker").Select
    Range("A3:S3").Select
    Selection.Copy
    Sheets("Documentation").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

Sorry if my formatting of the code is a little off. First post!

RBarryYoung · Answer 1 · 2018-06-13T20:54:24.587

1

Add this line before your first .Select:

r = ActiveCell.Row

then you want to change this line:

Range("A3:S3").Select

to this:

Range("A" & Cstr(r) & ":S" & Cstr(r)).Select

I think that that should do it.

NOTE: Using .Select is slow and has some other reliability/interaction problems, so we usually advise against it. However, it's hard to do a true Copy operation without it, so it's probably OK in this case. However, if you don't really need a true copy (formats, formulas, et. al.), but only want the value, then there's a better way to do this.

Here's a better way, since you only need the data. I have broken it into individual lines so that you can see how it is done.

Sub RecordTracker()
'RecordTracker Macro
'Records the updated row as a history row in Documentation Sheet
'Keyboard Shortcut: Ctrl r

    ' get the worksheet objects
    Dim wsDoc As Worksheet, wsTrak As Worksheet
    Set wsDoc = Sheets("Documentation")
    Set wsTrak = Sheets("Tracker")

    ' get the source (current) row
    Dim r As Long
    r = ActiveCell.Row

    'Make the output row
    wsDoc.Range("A2").EntireRow.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove '.Range("A2:S2").Select

    ' get the source data
    Dim dat() As Variant
    dat = wsTrak.Range("A" & CStr(r) & ":S" & CStr(r))

    ' set the output range
    Dim outRng As Range
    Set outRng = wsDoc.Range("A2:S2")

    ' copy data to the output range
    outRng = dat
End Sub

edited Jun 13 '18 at 20:54

answered Jun 13 '18 at 20:05

RBarryYoung

55,398
14
96
137

I am only looking to copy the values! I definitely do not want to copy the formulas. – J. Cain Jun 13 '18 at 20:14
This worked great for me so far. Would you also be able to help me change where the row is inserted on the Documentation page? It is putting it at the top, but I would like to save the top row for titles for tables so I can sort the history. – J. Cain Jun 13 '18 at 20:27
@J.Cain where do you want it inserted? – RBarryYoung Jun 13 '18 at 20:36
I would like it to insert a new row at A2 rather than A1, i.e. a new second row. – J. Cain Jun 13 '18 at 20:40
@J.Cain Also, are you the J.Cain that I know? – RBarryYoung Jun 13 '18 at 21:10
Okay haha well I hope you find your J. Cain. Thanks for the help! – J. Cain Jun 14 '18 at 17:56

urdearboy · Accepted Answer · 2018-06-13T21:13:13.683

1

Will Insert a new Row on sheet Documentation from (A2)
Copy the row you have selected (from sheet Tracker, Range A:S)
Paste values onto Documentation A2 (which is your newly created row)

This will need to be pasted in VBE on your sheet Tracker to execute

Notice that your code can be greatly reduced (as far as lines go) by avoiding the .Select method. You can find some useful information here

Sub StoreChanges()

    Sheets("Documentation").Range("A2").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Tracker").Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "S")).Copy
    Sheets("Documentation").Range("A1").PasteSpecial Paste:=xlPasteValues

End Sub

edited Jun 13 '18 at 21:13

answered Jun 13 '18 at 20:19

urdearboy

14,439
5
28
58

Just to clarify, I right click on Documentation, show code, and paste there? – J. Cain Jun 13 '18 at 20:34
Fantastic, thank you. Should I be deleting what I have on VBE for the Tracker sheet? If so, how to I get this process to run? Thank you! – J. Cain Jun 13 '18 at 20:45
I meant how to add a shortcut to run the macro as I will be having multiple people using the sheet, but I can definitely figure that out! :) Unfortunately, when I run the macro I run into an error that reads: 400 – J. Cain Jun 13 '18 at 20:52
Ah no worries! Works like a charm now, thank you so much! – J. Cain Jun 13 '18 at 21:03

score 0 · Answer 3 · answered Jun 13 '18 at 20:40

I am assuming that in the datasheet (Tracker) you want the selected range/row to be pasted and not just the third row A3:S3

I recorded a macro where when I press cntrl+r,it copies the first row from my data sheet, inserts a row onto the history page, and then copies the data onto that new row

Try removing the Range("A3:S3").Select line of code. Now when you select a row in the sheet Tracker manually, that row should be used in the code Selection.Copy

Sheets("Documentation").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Tracker").Select
## removed Range("a3:s3").select
Selection.Copy
Sheets("Documentation").Select

And if you want to add a new row second row onwards, just add Sheets("Documentation").Rows("2:2").Select before the code Selection.Insert

Editing the code for a recorded Macro

3 Answers3