Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Copy certain row to another worksheet based on a determinated number

0

Good afternoon,

I've been searching everywhere to get an answer for what i want, but i simply can't put anything working. But i think its due to my knowledge in what concerns to VB, that is almost null.

Anyway i hope you can help me out.

I attached the file im working on, and i have one sheet called "Facturas" and thats the main sheet, is where all the info of the other sheets come from.

In this case i want is a macro that copies all the rows that includes the number 71710223 to the Sheet 71710223. The same thinh to the 71710224 and so on. This number will come from F column of the "Facturas" sheet.

Can you help me out?

Kind regards,

André

Answer
Discuss

Answers

0
Selected Answer

You got it alllmost right.

Replace the current macro with this one and it should work:

Sub Facturas()

    Dim tfCol As Range, Cell As Object

    Set tfCol = Sheets("Facturas").Range("F2", "F10000")

    For Each Cell In tfCol

        If Cell.Value = "71710223" Then
            Cell.EntireRow.Copy
            ActiveSheet.Range("A65536").End(xlUp).Select
            Selection.Offset(1, 0).Select
            ActiveSheet.Paste
        End If

    Next

End Sub

To access a specific sheet, you need to do it like this: Sheets("Facturas"). and then put the rang object after it.

After that, the issue is that you exit the macro if you find an empty cell and, given your data set, this causes it to not go through the entire data set.

So I also removed this part:

If IsEmpty(Cell) Then
   Exit Sub
End If

Those were the two main changes, along with changing the format for referencing the range, removing the "su" before the "Sub" and removing the argument you had inbetween the parentheses.

Discuss

Discussion

P E R F E C T!! Thank you very very very MUCH!

I was so angry, and it was so simple, djisus!

It does exactly what i want.

Thank you once again, have a nice day!
jenekos (rep: 2) Apr 12, '17 at 9:12 am
There's only one detail i missed when i explained what i wanted, and i only realised now when started to put in practice in the real file.
The program do what i wanted it to do, but is always copying all the cells, so if i add a 71710223 to another row on "Facturas", instead of coping only that last one i added, it will copy all the cells that have 71710223 in it.

I guess i need a filter or something, any guesses?

Another thing, is there anyway to get it refreshing automaticly, when i put a "Factura" row with the 71710223 in it?

I made this alone, entirely just by observing another people doing it, i have no knowledge of VB, never studied, but its quite interesting what we can do with this!

Thank you in advance!

André Reis
jenekos (rep: 2) Apr 12, '17 at 9:51 am
There are a number of different ways to do this. Sorry, I just saw your comment now.

To avoid confusion, it will be easier if you ask this in a separate new question and include the current version of your macro/workbook in that question. (also I won't miss it then :P)
don (rep: 1989) Apr 17, '17 at 2:14 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login