Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
Função ao redor do ...
 
Notifications
Clear all

Função ao redor do nome da planilha

 
2024 - VBA & Macros
Post Recente por brunoxro 7 anos atrás
5 Posts
3 Usuários
0 Reactions
1,024 Visualizações
RSS
 aud
(@aud)
Posts: 2
New Member
Topic starter
 

Em uma das planilhas que estou trabalhando cheguei à um empasse que não sei muito bem como progredir,
Dentro dessa planilha importo duas delas e uma delas contem sempre o nome BO,
Até tentei estruturar, mas não sei por onde caminhar:

Alguém poderia me auxiliar?

Segue abaixo o que tentei realizar

   Sub filterthis()
Dim nomearquivo As Range
n = InputBox("BO")


Sub Macro3()
'
' Macro3 Macro
'

'
    Selection.End(xlToRight).Select
    Range("BF1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("BF1").Select
    Selection.End(xlToLeft).Select
    Range("G1").Select
    ActiveSheet.Paste
    Range("G1").Select
End Sub
 
Postado : 14/09/2017 1:23 pm
brunoxro
 brunoxro
(@brunoxro)
Posts: 698
Honorable Member
 

Boa tarde,

Não entendi o que você quer fazer. Explique mais detalhadamente.

Também anexe uma planilha de exemplo.

att,

 
Postado : 14/09/2017 4:10 pm
Fernando Fernandes
 Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
 

Boa noite aud

Como você é novato, para facilitar a tua participação no fórum, sugiro tomar conhecimento do conteúdo dos links abaixo:
viewtopic.php?f=7&t=16757
viewtopic.php?f=7&t=203
viewtopic.php?f=7&t=7903
viewtopic.php?f=7&t=3841
viewtopic.php?f=7&t=12600
viewtopic.php?f=7&t=3371

[]s
Patropi - Moderador

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

 
Postado : 14/09/2017 6:05 pm
 aud
(@aud)
Posts: 2
New Member
Topic starter
 

A minha macro trabalha em duas planilhas, só que eu gostaria de diferenciar a tratativa dessas
A maneira que eu acredito que funcionaria seria pelo nome dessas duas planilhas, pois se tratam de relatórios com exatamente o mesmo molde, segue código completo e irei sublinhar onde gostaria de adicionar esse "IF"

Sub preparareports()
'
' Macro2 Macro
'

    Dim LastRow
    Dim Caminho As String 'Caminho do arquivo
    Dim nomearquivo As String
    Dim CURLINE
    Dim ExcelApp As Object
    'Dim caminhocsv
    Dim hoyy As String
    
    hoyy = Format(Now, "yyyymmdd")
    
    CURLINE = 2
    
    
    
    If MsgBox("Relatório de Hoje?", vbYesNo) = vbYes Then
    
    
    Caminho = "C:Base" & hoyy & ".xlsx"
    
   [color=#0000BF] Else
    
    nomearquivo = InputBox("Insira o nome do arquivo")
   
'    caminhocsv = "C:Usersbr0208AppDataLocalVirtualStore" & nomearquivo & ".csv"
    
 '   Workbooks.Open Filename:=caminhocsv
    
    Caminho = "C:Base de Vendas" & nomearquivo & ".xlsx"
    
    End If
    
    Workbooks.Open Filename:=Caminho
    
    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/color]
        
    ActiveSheet.Name = "20140425"
         
        
    Range("a1:bz" & LastRow).Select
    Selection.AutoFilter
        ActiveSheet.AutoFilter.Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("20140425").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("20140425").AutoFilter.Sort.SortFields.Add Key:= _
        ActiveCell.Offset(0, 5).Range("A1:A1806"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("20140425").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        
    
    ActiveSheet.Range("$A$1:$AV$3239").AutoFilter Field:=6, Criteria1:=Array( _
        "'", "'0001", "'0002", "'0003", "'0004", "'0005", "'0006", "'0009", "'0014", "'0008"), Operator:= _
        xlFilterValues
    
    Range(Selection, Selection.End(xlDown)).Select
    Range("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    Range("A1").Select
    
    ' prepara arquivo
    
    

    
    'Trata FSREP
    'Columns("bh:bh").Select
    'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    'Range("bh2").Select
    'Selection.NumberFormat = "General"
    'ActiveCell.FormulaR1C1 = "=TEXT(RIGHT(RC[-1], 2),""000000"")"
    'Selection.AutoFill Destination:=Range("bh2:bh" & LastRow)
    'Range("bh2:bh" & LastRow).Select
    'Selection.Copy
    'Range("AV2").Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    '    :=False, Transpose:=False
    'Columns("AW:AW").Select
    'Application.CutCopyMode = False
    'Selection.Delete Shift:=xlToLeft
    
    LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    'Trata Filial------------------------------------------------
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2)"
    Selection.AutoFill Destination:=Range("B2:B" & LastRow)
    Range("B2:B" & LastRow).Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    'Trata NOTA--------------------------------------------------
    Columns("c:c").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],9)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & LastRow)
    Range("C2:C" & LastRow).Select
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    'Trata CODCLI-----------------------------------------------
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(MID(RC[-1],2,6),""000000"")"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D" & LastRow)
    Range("D2:D" & LastRow).Select
    Selection.Copy
    Range("c2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("d:d").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    'Trata CODVEND-----------------------------------------------
    Columns("m:m").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("m2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],2,6)"
    Range("m2").Select
    Selection.AutoFill Destination:=Range("m2:m" & LastRow)
    Range("m2:m" & LastRow).Select
    Selection.Copy
    Range("l2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("m:m").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    'Trata CNPJ----------------------------------------------------------
    Columns("e:e").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(MID(RC[-1],2,14),""00000000000000"")"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & LastRow)
    Range("E2:E" & LastRow).Select
    Selection.Copy
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("ba1").Select
    ActiveCell.FormulaR1C1 = "TAMANHO"
    Range("ba2").Select
    ActiveCell.FormulaR1C1 = "=LEN(TRIM(RC[-49]))"
    Selection.AutoFill Destination:=Range("ba2:ba" & LastRow)
    Range("ba2:ba" & LastRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
    
    'Trata CODPRO-----------------------------------------------------
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],2,LEN(RC[-1])-1)"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F" & LastRow)
    Range("F2:F" & LastRow).Select
    Selection.Copy
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    'Trata GRUPO--------------------------------------------------------
    Columns("g:g").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G" & LastRow)
    Range("G2:G" & LastRow).Select
    Selection.Copy
    Range("F2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    
    'Trata Pedido-------------------------------------------------------------------------------
    Columns("k:k").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("k2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],6)"
    Range("k2").Select
    Selection.AutoFill Destination:=Range("K2:K" & LastRow)
    Range("K2:k" & LastRow).Select
    Selection.Copy
    Range("j2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("k:k").Select
    Selection.Delete Shift:=xlToLeft
    
    'Trata ITEMPV--------------------------------------------------------------------------------
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2)"
    Range("l2").Select
    Selection.AutoFill Destination:=Range("l2:L" & LastRow)
    Range("L2:L" & LastRow).Select
    Selection.Copy
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("L:l").Select
    Selection.Delete Shift:=xlToLeft
    
    'TRATA CODTRAN-----------------------------------------------------------------
'    Columns("AA:AA").Select
'   Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'  Range("AA2").Select
' ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],6)"
'    Range("AA2").Select
'    Selection.AutoFill Destination:=Range("AA2:AA" & LastRow)
'    Range("AA2:AA" & LastRow).Select
'    Selection.Copy
'    Range("Z2").Select
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'        :=False, Transpose:=False
'    Application.CutCopyMode = False
'    Columns("AA:AA").Select
'    Selection.Delete Shift:=xlToLeft
        
    'TRATA VALORES--------------------------------------------------------------
  '      Columns("P:P").Select
   '     Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
    '    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
     '   Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      '  :=Array(1, 1), TrailingMinusNumbers:=True
        
  '      Columns("Q:Q").Select
   '     Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
    '    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
     '   Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      '  :=Array(1, 1), TrailingMinusNumbers:=True
        
  '      Columns("R:R").Select
   '     Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
    '    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
     '   Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      '  :=Array(1, 1), TrailingMinusNumbers:=True
        
   '     Columns("S:S").Select
  '      Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
    '    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
     '   Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      '  :=Array(1, 1), TrailingMinusNumbers:=True
        
        
  '      Columns("T:T").Select
   '     Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
    '    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     '   ReplaceFormat:=False
      '  Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
   '     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    '    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
     '   :=Array(1, 1), TrailingMinusNumbers:=True
      
        
     '   Columns("V:V").Select
     '   Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
     '   TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
     '   Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
     '   :=Array(1, 1), TrailingMinusNumbers:=True
        
        
        
    'TRATA CODVEND-----------------------------------------------------------------
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],2,LEN(RC[-1])-1)"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M" & LastRow)
    Range("M2:M" & LastRow).Select
    Selection.Copy
    Range("L2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    '
    '
    'TRATA CEP-----------------------------------------------------------------
    
    Columns("AE:AE").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],8)"
    Range("AE2").Select
    Selection.AutoFill Destination:=Range("AE2:AE" & LastRow)
    Range("AE2:AE" & LastRow).Select
    Selection.Copy
    Range("AD2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("AE:AE").Select
    Selection.Delete Shift:=xlToLeft

    'TRATA CODCIDADE-----------------------------------------------------------------

    Columns("AF:AF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],5)"
    Range("AF2").Select
    Selection.AutoFill Destination:=Range("AF2:AF" & LastRow)
    Range("AF2:AF" & LastRow).Select
    Selection.Copy
    Range("AE2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("AF:AF").Select
    Selection.Delete Shift:=xlToLeft
    
    '-----------------------------------------------------------------
    
    
    Range("A1").Select
    
    'Columns("H:H").Select
    'Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
     '   TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      '  Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
       ' :=Array(1, 4), TrailingMinusNumbers:=True
    
'PROGRAMAÇÃO




CURLINE = 2

Do Until Range("n" & CURLINE).Value = ""

    If Range("n" & CURLINE).Value = "ORDER        " And Range("h" & CURLINE).Value > Date + 3 Then
            
            Range("n" & CURLINE).Value = "PROGRAMADO"
            
                Else
            
            CURLINE = CURLINE + 1
               
    End If

Loop



'BO/BL------

CURLINE = 2

Do Until Range("n" & CURLINE).Value = ""

    If Range("n" & CURLINE).Value = "ORDER        " And Date - Range("g" & CURLINE).Value <= 4 Then
            
            Range("n" & CURLINE).Value = "BL"
            
                Else
            
            CURLINE = CURLINE + 1
               
    End If

Loop

CURLINE = 2

Do Until Range("n" & CURLINE).Value = ""

    If Range("n" & CURLINE).Value = "ORDER        " And Date - Range("g" & CURLINE).Value > 4 Then
        
        Range("n" & CURLINE).Value = "BO"
        
            Else
        
        CURLINE = CURLINE + 1

    End If
Loop
           
           
           
'Worksheets(nomearquivo).Columns("N").Replace _
 What:="INVOICE        ", Replacement:="FATURADO", _
 SearchOrder:=xlByColumns, MatchCase:=False
           
 Range("N:N").Replace _
 What:="INVOICE        ", Replacement:="FATURADO", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("N:N").Replace _
 What:="CREDIT NOTE    ", Replacement:="DEVOLUCAO", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
      Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1:BX4111").Select
    Selection.AutoFilter
    Range("E1").Select
    ActiveSheet.Range("$A$1:$BX$4111").AutoFilter Field:=5, Criteria1:=Array( _
   "66800060", "66800061", "66800133", "66800161", "66800162", "66800163", "66800164", "66800165", "66800423", "66800491", "66800492", "66800493", "66800494", "66800495", "66800496", "66800497", "66800504", "66800586", "66800587", "66800603", "66800611", "66800695", "66800697", "66800912", "66800913", "66800951", "66800952", "66800953", "66800954", "66800955", "66800956", "66800957", "66800958", "66801194", "1640202", "1640303", "66801358", "66801359", "66801360", "66801361", "66801362", "66801363", "66801364", "66801365", "66801357", "66801356", "66800794", "66800795", "66800796", "66800799", "66800971", "66800914", "66800916", "66801309"), Operator:=xlFilterValues
 
 Range("D:D").Replace _
 What:="60975737000232", Replacement:="60975737000232T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="60975737005110", Replacement:="60975737005110T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="60453024000390", Replacement:="60453024000390T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="60922168000771", Replacement:="60922168000771T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="61590410000124", Replacement:="61590410000124T", _
 SearchOrder:=xlByColumns, MatchCase:=False
           
 Range("D:D").Replace _
 What:="60884855000316", Replacement:="60884855000316T", _
 SearchOrder:=xlByColumns, MatchCase:=False
              
 Range("D:D").Replace _
 What:="60765823000130", Replacement:="60765823000130T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
  Range("D:D").Replace _
 What:="60726502000126", Replacement:="60726502000126T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="61599908000158", Replacement:="61599908000158T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="61442190000191", Replacement:="61442190000191T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="61849980001087", Replacement:="61849980001087T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="60552098000111", Replacement:="60552098000111T", _
 SearchOrder:=xlByColumns, MatchCase:=False
  
 Range("D:D").Replace _
 What:="60975737001204", Replacement:="60975737001204T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="60961422000155", Replacement:="60961422000155T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="60765823000482", Replacement:="60765823000482T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="60765823002779", Replacement:="60765823002779T", _
 SearchOrder:=xlByColumns, MatchCase:=False
  
 Range("D:D").Replace _
 What:="01498476001643", Replacement:="01498476001643T", _
 SearchOrder:=xlByColumns, MatchCase:=False
    
 Range("D:D").Replace _
 What:="61599908003173", Replacement:="61599908003173T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="51722957013160", Replacement:="51722957013160T", _
 SearchOrder:=xlByColumns, MatchCase:=False
  
 Range("D:D").Replace _
 What:="52164662000109", Replacement:="52164662000109T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="52164662000109", Replacement:="52164662000109T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="46367850000123", Replacement:="46367850000123T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
 Range("D:D").Replace _
 What:="10211939000159", Replacement:="10211939000159T", _
 SearchOrder:=xlByColumns, MatchCase:=False
 
     
           
'REPLACE----
           
           
    'Selection.Replace What:="INVOICE        ", Replacement:="FATURADO", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    'Selection.Replace What:="CREDIT NOTE    ", Replacement:="DEVOLUCAO", LookAt:=xlPart, _
     '   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ' ReplaceFormat:=False
                
    Columns("h:h").Select
    Selection.Replace What:="  /  /    ", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        
    Columns("a:a").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("a1:ad" & LastRow).Select
    Selection.Copy
    
    Range("AS:BZ").Select
    Selection.Delete
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close
        
        
    
End Sub
 
Postado : 15/09/2017 7:39 am
brunoxro
 brunoxro
(@brunoxro)
Posts: 698
Honorable Member
 

Boa tarde aud,

Use a ferramenta 'Code' para postar o código, assim fica mais fácil de ler e o tópico fica melhor organizado.

Anexe uma planilha de exemplo e explique mais. É mais fácil ajudar 'vendo' o arquivo e o código ao mesmo tempo.

att,

 
Postado : 15/09/2017 12:38 pm
Forum Jump:
  Tópico Anterior
Próximo Tópico