VBA create worksheets without duplicates and copy/paste data to new sheet based on conditions

Question

I've been trying to create a macro for creating worksheets. The code should do the following:

Create worksheets based on names from Columne "AG" in sheet (page 1) the new sheets should be added after sheet (page 1) and cannot be duplicates
The range of column AG is variable
Rename each worksheet as per the name in each cell in column AG (no duplicates)
Copy row data to the new sheet based on names form Columne AG

Column AG can have the same name multiple times eg.

Finland 100 
Norway 50
Finland 90

The code should create a new sheet called "Finland" and copy all the row data that contains Finland in Column AG and do the same for Norway.

Code example for copy and paste data. However, it seems like it doesn’t work all the time and the code can't create sheets.

Sub Copycontent()

a = Worksheets("Page 1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To a 

   If Worksheets("Page 1").Cells(i, 33).Value = "NO" Then

       Worksheets("Page 1").Rows(i).Copy
       Worksheets("Norway").Activate
       b = Worksheets("Norway").Cells(Rows.Count, 1).End(xlUp).Row
       Worksheets("Norway").Cells(b + 1, 1).Select
       ActiveSheet.Paste
       Worksheets("Page 1").Activate 

  ElseIf Worksheets("Page 1").Cells(i, 33).Value = "FI" Then

       Worksheets("page 1").Rows(i).Copy
       Worksheets("Finland").Activate
       c = Worksheets("Finland").Cells(Rows.Count, 1).End(xlUp).Row
       Worksheets("Finland").Cells(c + 1, 1).Select
       ActiveSheet.Paste
       Worksheets("Page 1").Activate

End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Page 1").Cells(1, 1).Select

End Sub

Thank you in advance

There are a fair number of actions missing from your code, and it's not very efficient to start with. I recommend [reading this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to avoid using `select` and `activate`, which is considered bad practice. Also [check out this](https://www.automateexcel.com/vba/add-and-name-worksheets/) to help with adding sheets. I'm afraid at the moment your code has too many separate issues to fix in one concise answer. — Plutian, Nov 27 '19 at 11:17

VBA create worksheets without duplicates and copy/paste data to new sheet based on conditions

0 Answers0