Jan 21 2021 05:01 AM
Hello guys!
I've recorded a macro that applies to my current sheet open. It then copies 2 columns (uses absolute reference), creates a new sheet, pastes the 2 columns, and the troublesome part: I want it to return to the previous sheet and copy 1 more column from there. The problem: it uses the original sheet name that I had open when I recorded the macro to copy the cell values and returns to (opens) the "previous" sheet (the original sheet that I used when the macro was recorded) and then it shows a dialogue box saying something went wrong.
What I need help with is editing the VBA script to make it versatile and applicable with any sheet, and not use the absolute reference. (Btw, I recorded the macro as relative cell reference)
Any help is GREATLY appreciated!!🙏🏻
Jan 21 2021 05:59 AM - edited Jan 21 2021 06:01 AM
@JohnnyB12 because you inserted an image instead of text of sample file, instead of recreating your code I just wrote a new function:
Function Step71()
Dim source, dest As Worksheet
Set source = ActiveSheet
Set dest = ActiveWorkbook.Sheets.Add(, source)
dest.Range("a:b").Formula = source.Range("f:g").Formula
dest.Range("j:j").Value = source.Range("o:o").Value
dest.name = "My New Sheet"
End Function
notice how I declare "source" and "dest" as Worksheet type
then set source to present activesheet and then dest as the new added sheet (note the part in () is to add the new sheet after the source sheet, you could delete that if you want it before that sheet)
then I assign the columns "a:b" in the destination to be a copy of the formulas found in "f:g" of the source (WARNING: it will copy the formula as they are and not shift them so formula in g2 looking at f2 in the new sheet will still be looking at f2)
then I assign the column "j:j" to be the VALUES from column O:O
and for fun I set the NAME of the new sheet = "My New Sheet"
I hope that makes it easier for you and you learn a little about VBA