Copy, Paste in New Sheet, Remove Duplicates VBA

[beginvbaanalyst]

Hi Everyone,

I had an idea to create a multi step process for my business.
Created a custom template provided below:

I want to pull data from column that have a yellow box and assortments (A:B) to whatever row it's filled down to. Paste this data into a new sheet, continue to execute for (E:F), (I:J), (M:N) and etc then remove duplicate values for a validation step at the very end.
Would my code look something like this?

Range ("A2:B", Range("B" & Row.Count).End(xlUp)).Row
Sheet("Sheet1").Copy after:=("Sheet1")
ActivateSheet.Name = "PV LIST"

Worksheets('Sheets1").Activate
Range ("E2:F", Range("F" & Row.Count).End(xlUp)).Row

^I think this is completely wrong but I hope I have some sort of idea
I would like to paste below the data I took from A and B without it removing anything into my new sheet.
Is this possible?

 

[Legacy 456155]

Hi there. Just to be clear. You want to:

1. add a new worksheet
2. copy A2 to B[last row of data]
3. paste this into the new worksheet in A1:B[last row of pasted date]
4. and then repeat for E:F, I:J, and so on, continuing to paste into the new worksheet in A:B in the next empty row
5. remove duplicates based upon column A ONLY in the new worksheet

?

 

[JLGWhiz]

continue to execute for (E:F), (I:J), (M:N) and etc

etc. is not a valid description for column references. What are the last two columns columns you want copied?
Also, where on the new sheet do you want them to be placed? All in one column, side by side beginning in A1:B1 or in the same columns they were copied from on the source sheet?

 

[beginvbaanalyst]

Hi there. Just to be clear. You want to:

1. add a new worksheet
2. copy A2 to B[last row of data]
3. paste this into the new worksheet in A1:B[last row of pasted date]
4. and then repeat for E:F, I:J, and so on, continuing to paste into the new worksheet in A:B in the next empty row
5. remove duplicates based upon column A ONLY in the new worksheet

?

1. This can be removed I just made a new sheet called[PV LIST]
2. Correct
3.I would paste in new sheet[PV LIST] under A2:B[last row of pasted data]
4. Repeat step 2 for columns E;F, I:J and so on, continue to paste in new sheet[PV LIST] in Columns A:B with next empty row
5. Remove duplicates based upon column A AND B in the new sheet[PV LIST]. They don't need to be grouped together, I just want their values.

 

[beginvbaanalyst]

Hi there. Just to be clear. You want to:

1. add a new worksheet
2. copy A2 to B[last row of data]
3. paste this into the new worksheet in A1:B[last row of pasted date]
4. and then repeat for E:F, I:J, and so on, continuing to paste into the new worksheet in A:B in the next empty row
5. remove duplicates based upon column A ONLY in the new worksheet

?

1. This can be removed I just made a new sheet called[PV LIST]
2. Correct
3.I would paste in new sheet[PV LIST] under A2:B[last row of pasted data]
4. Repeat step 2 for columns E;F, I:J and so on, continue to paste in new sheet[PV LIST] in Columns A:B with next empty row
5. Remove duplicates based upon column A AND B in the new sheet[PV LIST]. They don't need to be grouped together, I just want their values.

 

[beginvbaanalyst]

etc. is not a valid description for column references. What are the last two columns columns you want copied?
Also, where on the new sheet do you want them to be placed? All in one column, side by side beginning in A1:B1 or in the same columns they were copied from on the source sheet?

I understand it isn't a valid description but the area of data filled will always be changing. It all depends on how many requests I take at once.
I would want pasted values to start in new sheet at A2:B2.

 

[JLGWhiz]

This does not do the duplicates yet but see if this is what you want for the copy paste.

Sub t()
Dim i As Long, sh As Worksheet, sh2 As Worksheet
Set sh = ActiveSheet
Set sh2 = Sheets.Add(After:=sh)
    For i = 1 To sh.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
        Intersect(sh.UsedRange.Offset(1), sh.Columns(i).Resize(, 2)).Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    Next
End Sub

 

[beginvbaanalyst]

This does not do the duplicates yet but see if this is what you want for the copy paste.

Sub t()
Dim i As Long, sh As Worksheet, sh2 As Worksheet
Set sh = ActiveSheet
Set sh2 = Sheets.Add(After:=sh)
    For i = 1 To sh.Cells(1, Columns.Count).End(xlToLeft).Column Step 4
        Intersect(sh.UsedRange.Offset(1), sh.Columns(i).Resize(, 2)).Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    Next
End Sub

Are you able to explain to me what each line of code represents?
It created the extra sheet, but it says the application-defined or object-defined error

 

[JLGWhiz]

This is revised based on your post # 5. The copy ranges are based on an assumtion that your columns will be every fourth and fifth, starting from column A.

Sub t()
Dim i As Long, sh As Worksheet, sh2 As Worksheet
Set sh = Sheets("Sheet1")
Set sh2 = Sheets("PV LIST")
    For i = 1 To sh.Cells(1, Columns.Count).End(xlToLeft).Column Step 4 'set up loop for every fourth column
        If sh2.Cells(1, 1) = "" Then 'Check if data exists
            Intersect(sh.UsedRange.Offset(1), sh.Columns(i).Resize(, 2)).Copy sh2.Range("A1") 'Copy/paste first 2 columns
        Else
            Intersect(sh.UsedRange.Offset(1), sh.Columns(i).Resize(, 2)).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2) 'copy/past remaining columss
        End If
    Next
    With sh2
            sh2.Range("A:B").RemoveDuplicates Array(1, 2), xlYes
    End With
End Sub

 

[beginvbaanalyst]

This is revised based on your post # 5. The copy ranges are based on an assumtion that your columns will be every fourth and fifth, starting from column A.

Sub t()
Dim i As Long, sh As Worksheet, sh2 As Worksheet
Set sh = Sheets("Sheet1")
Set sh2 = Sheets("PV LIST")
    For i = 1 To sh.Cells(1, Columns.Count).End(xlToLeft).Column Step 4 'set up loop for every fourth column
        If sh2.Cells(1, 1) = "" Then 'Check if data exists
            Intersect(sh.UsedRange.Offset(1), sh.Columns(i).Resize(, 2)).Copy sh2.Range("A1") 'Copy/paste first 2 columns
        Else
            Intersect(sh.UsedRange.Offset(1), sh.Columns(i).Resize(, 2)).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2) 'copy/past remaining columss
        End If
    Next
    With sh2
            sh2.Range("A:B").RemoveDuplicates Array(1, 2), xlYes
    End With
End Sub

It looks like this line is messing up as well:
Intersect(sh.UsedRange.Offset(1), sh.Columns(i).Resize(, 2)).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2) 'copy/past remaining columss

Application-defined or obhect-defined error