Paste values in another sheet after last Cell containing Data

[rynardc]

Hi All
I am sure this is not very tricky to do, i have been trying but for the life of me i can`t get it to work. I have 2 sheets, sheet1 contains a simple Data entry form that populates cells D8 and D11, i need these 2 cells copied and then paste special with transpose set to true on Sheet2, also need the data to paste after the last captured data on sheet2 as there will be multiple entries captured on the Data Entry on sheet1

 

[rynardc]

Managed to get it working, although i don`t think it is an optimal solution it works for now

Worksheets("Input").Activate
    Range("D8,D11").Select
        Selection.Copy
Worksheets("Scorecards").Activate
    Range("A200").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

 

[Joe4]

You can simplify it a bit by getting rid of most of the Select statements, i.e.

Worksheets("Input").Activate
Range("D8,D11").Copy
Worksheets("Scorecards").Activate
Range("A200").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

Note that it is not usually necessary to select ranges in order to work with them. And it actually makes your code run a little slower to have them all in there.
I am guessing that you got that code using the Macro Recorder. The Macro Recorder is very literal, and records every cell selection.
So your recorded code can often be cleaned up a little afterwards to make it shorter and more efficient.

 

[rynardc]

Thanks Joe4, that does look alot cleaner i will update my code. Yes i use the Macro recorder combined with bits of code i find on google

 

[Joe4]

Thanks Joe4, that does look alot cleaner i will update my code. Yes i use the Macro recorder combined with bits of code i find on google

You are welcome.

Yep, the Macro Recorder is a great tool to get little snippets of code. Just know that you are can often "clean it up a bit", i.e. getting rid of things like "Selects" and "Scrolls".

 

[Umma123]

Great job @Joe4 for the super clean and simplified solution! I’ve recently tried using SkySuite AI, it’s a great Excel extension with a built-in formula builder tool that can help you create formulas based on your instructions. It also includes an AI assistant, which saves a lot of time.