SOLVED

Get Macro to copy/paste values only after running in what was active cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1482942%22%20slang%3D%22en-US%22%3EGet%20Macro%20to%20copy%2Fpaste%20values%20only%20after%20running%20in%20what%20was%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482942%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20pretty%20basic%20macro%20to%20create%20a%20unique%20ID%20for%20a%20sales%20file%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20it's%20done%20that%20%2C%20I%20want%20to%20copy%20then%20paste%20special%20(values%20only)%20to%20hard%20wire%20the%20random%20ID%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20%2C%20it%20does%20the%20first%20part%20beautifully%20%2C%20but%20as%20I%20recorded%20it%20(rather%20than%20write%20it)%20when%20it%20does%20the%20copy%20%2F%20paste%20it%20always%20uses%20the%20Range(%22B12%22).select%20line%20and%20I%20need%20it%20to%20use%20what%20WAS%20the%20active%20cell%20so%20it%20copy%2Fpastes%20the%20right%20one%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20last%20couple%20of%20lines%20are%20junk%20where%20I%20finished%20by%20clicking%20off%20the%20active%20cell%20to%20leave%20spreadsheet%20'tidy'%20(no%20paste%20outline)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22shotgun-68_0-1592899649135.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200425i178399F5CC08828E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22shotgun-68_0-1592899649135.png%22%20alt%3D%22shotgun-68_0-1592899649135.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1482942%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483016%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20Macro%20to%20copy%2Fpaste%20values%20only%20after%20running%20in%20what%20was%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F534282%22%20target%3D%22_blank%22%3E%40shotgun-68%3C%2FA%3E%26nbsp%3BInstead%20of%20%3CSTRONG%3ERange(%22B12%22).Select%3C%2FSTRONG%3E%20use%20%3CSTRONG%3EActiveCell.Select%3C%2FSTRONG%3E%20.%20That%20should%20work%2C%20though%2C%20I've%20not%20been%20able%20to%20test%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483033%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20Macro%20to%20copy%2Fpaste%20values%20only%20after%20running%20in%20what%20was%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F534282%22%20target%3D%22_blank%22%3E%40shotgun-68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%20than%20copy%2Fpaste%20to%20overwrite%20a%20formula%2C%20simply%20convert%20to%20value.%26nbsp%3B%20I%20assume%2C%20since%20there%20is%20no%20cell%20selection%20preceding%20the%20code%2C%20that%20you%20select%20where%20the%20formula%20will%20be%20inserted%20manually%20before%20running%20the%20code%20(if%20not%2C%20this%20should%20be%20added)%20-%20try%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20ID()%3CBR%20%2F%3E'%3CBR%20%2F%3E'ID%20Macro%3CBR%20%2F%3E'%3CBR%20%2F%3E'Keyboard%20Shortcut%3A%20Ctrl%2Bk%3CBR%20%2F%3E'%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20_%3CBR%20%2F%3E%22%3DConcatenate(rc1%2Cleft(rc3%2C3)%2Crandbetween(1%2C100000)%2Cleft(rc6%2C2))%22%3CBR%20%2F%3EActiveCell.Value%20%3D%20ActiveCell.Value%3CBR%20%2F%3ERange(%22C3%3AC4%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22%22%3CBR%20%2F%3ERange(%22B4%22).Select%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1483048%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20Macro%20to%20copy%2Fpaste%20values%20only%20after%20running%20in%20what%20was%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1483048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%2C%20thanks%20%2C%20worked%20perfectly%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a pretty basic macro to create a unique ID for a sales file 

 

Once it's done that , I want to copy then paste special (values only) to hard wire the random ID 

 

However , it does the first part beautifully , but as I recorded it (rather than write it) when it does the copy / paste it always uses the Range("B12").select line and I need it to use what WAS the active cell so it copy/pastes the right one 

 

The last couple of lines are junk where I finished by clicking off the active cell to leave spreadsheet 'tidy' (no paste outline)

 

shotgun-68_0-1592899649135.png

 

 

 

3 Replies
best response confirmed by shotgun-68 (Occasional Contributor)
Solution

@shotgun-68 Instead of Range("B12").Select use ActiveCell.Select . That should work, though, I've not been able to test it.

@shotgun-68 

 

Rather than copy/paste to overwrite a formula, simply convert to value.  I assume, since there is no cell selection preceding the code, that you select where the formula will be inserted manually before running the code (if not, this should be added) - try below:

 

Sub ID()
'
'ID Macro
'
'Keyboard Shortcut: Ctrl+k
'
ActiveCell.FormulaR1C1 = _
"=Concatenate(rc1,left(rc3,3),randbetween(1,100000),left(rc6,2))"
ActiveCell.Value = ActiveCell.Value
Range("C3:C4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("B4").Select
End Sub