VBA Formula help

[WishfulExcelWizard]

Hello
I am trying to write a VBA formula to automatically copy one row from one tab to another tab within the same workbook based on a criteria of "completed" identified in a drop down. Below is the code I found online and keep received out of range error message. I am very new to this and trying to find some help! Thank you

Dim StageColAs Range
Dim Stage As Range
Dim PasteCell As Range

Set StageCol = Sheet1.Range("M2:M14")

For Each Stage in StageCol

If Sheet2.Rnage("A2") = "" Then
Set PasteCell = Sheet2.Rnage("A2')
Else
Set PasteCell = Sheet2.Range ("A1").End(x1Down).Offset(1, 0)
End If
If Stage = "Completed"" Then Stage.EntireRow.Copy PasteCell

Next Stage

End Sub

 

[Joe4]

Welcome to the Board!

First and foremost, you have a few typos there:

If Sheet2.Rnage("A2") = "" Then
Set PasteCell = Sheet2.Rnage("A2')

Notice the single-quote after A2 also.

If you still get errors after that, if you get the "Debug" option, click that button and tell us which line of code it highlights.

 

[WishfulExcelWizard]

Welcome to the Board!

First and foremost, you have a few typos there:

If Sheet2.Rnage("A2") = "" Then
Set PasteCell = Sheet2.Rnage("A2')

Notice the single-quote after A2 also.

If you still get errors after that, if you get the "Debug" option, click that button and tell us which line of code it highlights.

I actually don’t have any typos in the formula on the worksheet that was me free range typing it since I couldn’t copy and paste from my work computer (locked down)

But thank you for checking those

The line it’s highlighting is “if sheet2.range(“A2”) = “” Then

 

[Joe4]

OK, rather than trying to work with some faulty code, I think it would just be better if you show us what your data looks like, and what you would like your expected result to look like, espcially so much depends on the layout of your data. Can you show us those two things?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[WishfulExcelWizard]

I cannot get the xlbb to download

I have a 20 column document with one column labeled stage which would be filled in as completed from a drop down. That drop down identifier would then auto generate the whole row to be copied over to the new tab. Same columns on the new tab. I want the row to copy from one tab to the other tab with all the same info when it is labeled completed. Sorry not trying to be difficult but everything on my work computer is locked down so I cannot download or upload anything.

 

[Joe4]

Since you appear to be using Excel 365, why not use the new FILTER function instead?
It is completely dynamic and requires no VBA (it is one of my favorite new functions in a long time!)

See: FILTER function - Microsoft Support

 

[WishfulExcelWizard]

Since you appear to be using Excel 365, why not use the new FILTER function instead?
It is completely dynamic and requires no VBA (it is one of my favorite new functions in a long time!)

See: FILTER function - Microsoft Support

I did see this feature but Does it auto pull? I’m trying to remove human work from the equation and setting up a way that every time we humans change one to completed it copies it to another tab for someone else to look at.

 

[Joe4]

I did see this feature but Does it auto pull?

Yes, that is what I mean when I say it is dynamic!
It is a "spill" function. That means that you only enter the formula once, in the first cell you want to return the values in, and it automatically fill all the other matching values below it.
As you mark more values in your original list as "Complete", it will automatically update what is returned.
Try it and see for yourself! It is super cool, and makes things like this extremely easy!

 

[WishfulExcelWizard]

This is amazing and totally working for what I need it to do. Only issue I am having

1) is there a way to keep hyperlinks active when the data transfers over?
2) if hyperlinks are not able to remain active how can I go into the cell and copy and paste the link that’s there? If I go into the cell the link just disappears

 

[Joe4]

This is amazing and totally working for what I need it to do. Only issue I am having

1) is there a way to keep hyperlinks active when the data transfers over?
2) if hyperlinks are not able to remain active how can I go into the cell and copy and paste the link that’s there? If I go into the cell the link just disappears

You are welcome.

Your question about hyperlinks sounds like a different question altogether (and one that I don't know that I would be able to help out with anyhow).
Your best bet would probably be to post that as a new question, so everyone sees it in the "Unanswered threads" listing.