How to generate a worksheet from another using VBA

I made the code below following good programming practices, using more appropriate variable names with the use of them, besides not using ActiveCell , Offset and the like. I hope you prefer it.

Option Explicit
Option Private Module
Sub Principal()

    Dim PlanilhaAtual As Worksheet
    Dim PlanilhaNova As Worksheet

    Set PlanilhaAtual = Worksheets(1)

    GerarPlanilha ("Nova")
    Set PlanilhaNova = Worksheets("Nova")

    Dim UltimaLinha As Long
    UltimaLinha = PlanilhaAtual.Cells.Find("*", LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

' Copiando os valores para a planilha nova
    Dim Linha As Long
    For Linha = 2 To UltimaLinha
        PlanilhaNova.Cells(Linha, 1).Value = PlanilhaAtual.Cells(Linha, 2).Value
        PlanilhaNova.Cells(Linha, 2).Value = _
            ConverterCategoria(CStr(PlanilhaAtual.Cells(Linha, 1).Value))
        PlanilhaNova.Cells(Linha, 3).Value = "yes"
        PlanilhaNova.Cells(Linha, 4).Value = PlanilhaAtual.Cells(Linha, 4).Value
        PlanilhaNova.Cells(Linha, 5).Value = PlanilhaAtual.Cells(Linha, 2).Value
        PlanilhaNova.Cells(Linha, 6).Value = ""
        PlanilhaNova.Cells(Linha, 7).Value = PlanilhaAtual.Cells(Linha, 6).Value
        PlanilhaNova.Cells(Linha, 8).Value = PlanilhaAtual.Cells(Linha, 7).Value
    Next

End Sub
Sub GerarPlanilha(Nome As String)

    Dim Planilha As Worksheet

' Excluindo planilha existente se houver
    For Each Planilha In Worksheets
        If Planilha.Name = Nome Then
            Application.DisplayAlerts = False
            Planilha.Delete
            Application.DisplayAlerts = True
        End If
    Next

' Criando a planilha nova
    Set Planilha = Worksheets.Add(After:=Sheets(Sheets.Count))
    Planilha.Name = Nome

' Gerando cabeçalho na planilha nova
    Range("A1").Value = "name(pt-br)"
    Range("B1").Value = "categories"
    Range("C1").Value = "shipping"
    Range("D1").Value = "quantity"
    Range("E1").Value = "model"
    Range("F1").Value = "sku"
    Range("G1").Value = "price"
    Range("H1").Value = "date_added"

End Sub
Function ConverterCategoria(Texto As String) As Long

'  O valor 0 é retornado caso o texto não tenha um valor correspondente
    Select Case Texto
        Case "Multimídia > Multilaser"
            ConverterCategoria = 1
        Case "Sestini > Meninos"
            ConverterCategoria = 2
        Case Else
            ConverterCategoria = 0
    End Select

End Function

The code contains a main routine (the one you should run), a subroutine to create the new worksheet (and delete if it exists), and a function to generate the category value.

The use of the function is better in this case, since new values can be added, so keep it apart, without mixing with the rest of the code. I also put the return 0 in case the text can not be found. If you find a category with a value of 0, you have to see which category (s) you did not plan to add to the code.

If there is any question about the code or some snippet that needs to be better explained, just ask me what I answer.

Luccas,

I do not know how many rows will be executed. But I would do so.

A new CSV query to export the CSV data, already breaking into columns by the delimiter. With this, every time it is run, it only needs to update this query and you will have all the lines of the CSV, which updates like this:

Sheets("Planilha1").Select
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

After that, with a code similar to this, you can bring the data from one to another already in the desired format.

Range("A2").Select
NumeroLinhas = Range("A2", Range("A2").End(xlDown)).Rows.Count

Range("A2").Select
For x = 1 To NumeroLinhas
    Range("Planilha3!" + ActiveCell.Address) = ActiveCell.Value
    Range("Planilha3!" + ActiveCell.Offset(0, 1).Address) = ActiveCell.Offset(0, 1).Value
    Range("Planilha3!" + ActiveCell.Offset(0, 2).Address) = ActiveCell.Offset(0, 2).Value
    Range("Planilha3!" + ActiveCell.Offset(0, 3).Address) = ActiveCell.Offset(0, 3).Value

    ActiveCell.Offset(1, 0).Select
Next

Notice that you are going to paste the direct value into the column you need by just moving with offset .

Checks the following code ... The logic of assigning the values of the respective columns is missing

Sub copyTemplate()

Dim wk As Workbook
Dim fileOriginal As Worksheet
Dim lastRow, i As Integer
Set wk = Workbooks.Add

'adicionar cabeçalho
wk.Worksheets("Sheet1").Cells(1, 1) = " name(pt-br)"
wk.Worksheets("Sheet1").Cells(1, 2) = "categories"
wk.Worksheets("Sheet1").Cells(1, 3) = "quantity"
wk.Worksheets("Sheet1").Cells(1, 4) = "price"
wk.Worksheets("Sheet1").Cells(1, 5) = "date_added"
wk.Worksheets("Sheet1").Cells(1, 6) = "shipping"
wk.Worksheets("Sheet1").Cells(1, 7) = "sku"
wk.Worksheets("Sheet1").Cells(1, 8) = "model"

'file original onde estão os dados para ser copiados
'nome do ficheiro original...
'quando executares a macro o ficheiro deve estar a aberto
Set fileOriginal = Workbooks("StackOverflowCopyTest.xlsm").Worksheets("Folha1")
'busca a ultima linha do ficheiro original
lastRow = fileOriginal.Cells(fileOriginal.Rows.Count, 1).End(xlUp).Row
'percorre o ficheiro original e copia para o novo ficheiro
For i = 2 To lastRow
'cria a logica aqui
'Cells(i, 1) -> coluna do name
wk.Worksheets("Sheet1").Cells(i, 1) = fileOriginal.Cells(i, 2)

'exemplo -> verificar a primeira coluna se é Multimídia > Multilaser
    If fileOriginal.Cells(i, 1) = "Multimídia > Multilaser" Then
         wk.Worksheets("Sheet1").Cells(i, 2) = 1
    Else
        wk.Worksheets("Sheet1").Cells(i, 2) = 2
    End If

'cria aqui a logica
'Estrutura do cells .Cells(#LINHA, #Coluna)
wk.Worksheets("Sheet1").Cells(i, 3) = fileOriginal.Cells(i, 3)
wk.Worksheets("Sheet1").Cells(i, 4) = fileOriginal.Cells(i, 4)
wk.Worksheets("Sheet1").Cells(i, 5) = fileOriginal.Cells(i, 5)
wk.Worksheets("Sheet1").Cells(i, 6) = fileOriginal.Cells(i, 6)
wk.Worksheets("Sheet1").Cells(i, 7) = fileOriginal.Cells(i, 7)
wk.Worksheets("Sheet1").Cells(1, 8) = fileOriginal.Cells(i, 8)

Next

End Sub

I tested it here and it worked, insert a button in the worksheet with the macro below. It will generate another worksheet in the same file.

Sub geraPlanilha()
    Dim contador As Integer
    Dim planilhaOriginal As Worksheet
    Dim novaPlanilha As Worksheet
    Set planilhaOriginal = Workbooks("teste.xlsm").Worksheets(1)
    Set novaPlanilha = Workbooks("teste.xlsm").Worksheets.Add()

    'adicionar cabeçalho
    novaPlanilha.Cells(1, 1) = "name(pt-br)"
    novaPlanilha.Cells(1, 2) = "categories"
    novaPlanilha.Cells(1, 3) = "shipping"
    novaPlanilha.Cells(1, 4) = "quantity"
    novaPlanilha.Cells(1, 5) = "model"
    novaPlanilha.Cells(1, 6) = "sku"
    novaPlanilha.Cells(1, 7) = "price"
    novaPlanilha.Cells(1, 8) = "date_added"

    contador = 2
    'Faz um loop em todas as linhas em que a primeira coluna estiver preenchida
    Do While planilhaOriginal.Cells(contador, 1) <> ""
        'name(pt-br)
        novaPlanilha.Cells(contador, 1) = planilhaOriginal.Cells(contador, 2)
        'categories
        If planilhaOriginal.Cells(contador, 1) = "Multimídia > Multilaser" Then
             novaPlanilha.Cells(contador, 2) = 1
        Else
            novaPlanilha.Cells(contador, 2) = 2
        End If
        'shipping
        novaPlanilha.Cells(contador, 3) = "yes"
        'quantity
        novaPlanilha.Cells(contador, 4) = planilhaOriginal.Cells(contador, 4)
        'model
        novaPlanilha.Cells(contador, 5) = planilhaOriginal.Cells(contador, 2)
        'sku
        novaPlanilha.Cells(contador, 6) = ""
        'price
        novaPlanilha.Cells(contador, 7) = planilhaOriginal.Cells(contador, 6)
        'date_added
        novaPlanilha.Cells(contador, 8) = planilhaOriginal.Cells(contador, 7)

        contador = contador + 1
    Loop
End Sub

You can improve by making these categories look in another worksheet instead of getting fixed in the code, but it's up to you.