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?

 

[JLGWhiz]

I cannot trouble shoot it from here. It ran OK in my test set up. What that line of code does is create a column reference based on the value of 'i' for that iteration in the loop, So if it is copying any of the columns and then throwing the error, it is probably because the value for i is for a column number that exceeds the last column of the used range. In and earlier post I asked what the last column to be copied was and never got an answer, so I just used the last column of row 1. That may or may not bee a good number.

 

[JLGWhiz]

I cannot get oit to djuplicate the error based on what I thought might be causing it. I tried with varying numbers of columns and it worked as expected without error in my set up. So, without being able to check it on the actual sheet, I don't have an answer.

 

[beginvbaanalyst]

I cannot get oit to djuplicate the error based on what I thought might be causing it. I tried with varying numbers of columns and it worked as expected without error in my set up. So, without being able to check it on the actual sheet, I don't have an answer.

How can I share you a file of my excel document?

 

[JLGWhiz]

How can I share you a file of my excel document?

This site does not allow uploads to its server so you would have to use something like 'Dropbox' and then provide a link in the the reply box of this thread. Be sure you mark it to share.l

 

[beginvbaanalyst]

This site does not allow uploads to its server so you would have to use something like 'Dropbox' and then provide a link in the the reply box of this thread. Be sure you mark it to share.l

I uploaded what my spreadsheet looks like, here is the link.
excel document

 

[JLGWhiz]

There is something wrong with the link. It only takes me to Dropbox sign in, not the file.