How to return to previous sheet on VBA?-CodePudding

Hello I have a dataset that I need to reformat. The data comes from multiple columns, but I have consolidate into one then do an xlookup. I figured I could write a script that would copy the records and paste it onto the sheet where the data clean will happen, except when I use the command "lstsheet.select" I get an run time 424 error. The reason why I have to use last sheet rather than the specific sheet is because I have to make it dynamic so the end user doesn't have to select the specific macro.

This is how the data looks

Column A: Driver

Column B: Tractor

Column C: Trailer

but I would like it to stack on each other so on the other sheet it would show one column with all three values.

Sub Asset_Export()

Range("E5:E100").Copy

Worksheets("Asset Upload").Range("A2").PasteSpecial xlPasteValues

LstSht.Select

Range("F5:F100").Copy

Worksheets("Asset Upload").Range("A98").PasteSpecial xlPasteValues

End Sub

Data Source

Result of Data

CodePudding user response：

You can give a dynamic sheet number by getting a value via Input Box option.

i = inputbox("Enter the sheet number")

sheets(i).range(XX).copy

and goes on...