Excel VBA Macro Creation

Question

Hi!

I am at the very beginning of macro creation and unfortunately, I wasn't able to adapt any youtube tutorial or google search guide to solve this.

So with a hopeful mind, I am asking for your help. :)

I am trying to create a macro that can be used to automatically copy the data from the master sheet called "ALL" to sub sheets called "US-FR" and "ES-PT", based on the value present in the sheet "ALL" in columns G,H,I,J - meaning if the column has an "X" in it for that particular row, I would like to have it copied into the other sub sheets.

Columns G,H representing US-FR and Columns I and J representing ES-PT

Is there any way to create a macro like this?

Could someone show me how it can be made?

Picture attached below :)

Thanks!

Jack

What I came up with is this: (please be gentle, it's my first time)

Answer 1

Here is a very crude solution (for US-FR only)... try to use it to solve the problem for the second sheet also.

Sub copyRows()

Dim lRow As Long, lRow1 As Long, lRow2 As Long, i As Long, outRow As Long

With Sheets("ALL")

    lRow1 = .Cells(Rows.Count, "G").End(xlUp).Row

    lRow2 = .Cells(Rows.Count, "H").End(xlUp).Row

    lRow = WorksheetFunction.Max(lrwo1, lRow2)

    outRow = 1

    For i = 1 To lRow

        If .Range("G" & i).Value = "X" Or .Range("H" & i).Value = "X" Then

            .Rows(i).Copy Destination:=Sheets("US-FR").Range("A" & outRow)

            outRow = outRow + 1

        End If

    Next i

End With

End Sub

Answer 2

You are most welcome. I am sure you will be able to modify it to suit your requirement.

See the hint below (again not the best solution but to start it is ok)

Sub copyRows()

Dim lRow As Long, lRow1 As Long, lRow2 As Long, i As Long, outRow As Long

With Sheets("ALL")

    lRow1 = .Cells(Rows.Count, "G").End(xlUp).Row

    lRow2 = .Cells(Rows.Count, "H").End(xlUp).Row

    lRow = WorksheetFunction.Max(lrwo1, lRow2)

    outRow = 1

    For i = 1 To lRow

        If .Range("G" & i).Value = "X" Or .Range("H" & i).Value = "X" Then

            .Rows(i).Copy Destination:=Sheets("US-FR").Range("A" & outRow)

            outRow = outRow + 1

        End If

    Next i

' Added for ES-PT

    lRow1 = .Cells(Rows.Count, "G").End(xlUp).Row  ' <--- Change here

    lRow2 = .Cells(Rows.Count, "H").End(xlUp).Row  ' <--- Change here

    lRow = WorksheetFunction.Max(lrwo1, lRow2)

    outRow = 1

    For i = 1 To lRow

        If .Range("G" & i).Value = "X" Or .Range("H" & i).Value = "X" Then  ' <--- Change here

            .Rows(i).Copy Destination:=Sheets("US-FR").Range("A" & outRow)  ' <--- Change here

            outRow = outRow + 1

        End If

    Next i

End With

End Sub

Code above not tested...

Also you should start OutRow at row 2 and have headers in Row 1

Clean output sheets before running the loops

Merge both loops into one

Use variables for sheetnames .... and so on. You will improve as you learn more.

Answer 3

Thank you very much, Sheelo!

It works like a miracle!

I will jump on the second sheet :)

Thanks a lot again <3