Ho scritto la macro qui sotto allegata:
- Codice: Seleziona tutto
Sub Cartik1()
'Estrazione Codice Filiale'
'!!!!!!!!!Convertire in numero la filiale 21!!!!!!!!!!!!!!'
Range("C:C").NumberFormat = "#,##0"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = "=stringa.estrai(a1;1;2)"
Dim stgFormula As String
stgFormula = Sheets("ElencoDip").Range("B1").Formula
Sheets("ElencoDip").Range("B:B").Formula = stgFormula
Range("B:B").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
Sub Cartik2()
'Conteggio Buoni da Ordinare'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = "=stringa.estrai(a1;3;3)"
stgFormula = Sheets("ElencoDip").Range("C1").Formula
Sheets("ElencoDip").Range("C:C").Formula = stgFormula
Range("C:C").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("G:G").Activate
ActiveCell.FormulaLocal = "=se(b1=21;21;26)"
stgFormula = Sheets("ElencoDip").Range("G1").Formula
Sheets("ElencoDip").Range("G1:G315").Formula = stgFormula
End Sub
Sub Cartik3()
Columns("I:I").Activate
ActiveCell.FormulaLocal = "=(e1-g1)"
stgFormula = Sheets("ElencoDip").Range("i1").Formula
Sheets("ElencoDip").Range("i1:i315").Formula = stgFormula
Columns("K:K").Activate
ActiveCell.FormulaLocal = "=se(b1=21;21;26)"
stgFormula = Sheets("ElencoDip").Range("k1").Formula
Sheets("ElencoDip").Range("k1:k315").Formula = stgFormula
End Sub
Sub Cartik4()
Columns("m:m").Activate
ActiveCell.FormulaLocal = "=(k1+i1)"
stgFormula = Sheets("ElencoDip").Range("m1").Formula
Sheets("ElencoDip").Range("m1:m315").Formula = stgFormula
Range("G:G").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("I:I").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("K:K").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("M:M").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("F:F").Activate
ActiveCell.FormulaLocal = "0"
stgFormula = Sheets("ElencoDip").Range("f1").Formula
Sheets("ElencoDip").Range("f1:f315").Formula = stgFormula
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = "=se(e1<10;concatena(f1;f1;e1);concatena(f1;e1))"
stgFormula = Sheets("ElencoDip").Range("g1").Formula
Sheets("ElencoDip").Range("g1:g315").Formula = stgFormula
Range("g:g").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("I:I").Select
ActiveCell.FormulaLocal = "=concatena(f1;h1)"
stgFormula = Sheets("ElencoDip").Range("i1").Formula
Sheets("ElencoDip").Range("i1:i315").Formula = stgFormula
Columns("M:M").Select
ActiveCell.FormulaLocal = "=concatena(f1;l1)"
stgFormula = Sheets("ElencoDip").Range("m1").Formula
Sheets("ElencoDip").Range("m1:m315").Formula = stgFormula
Columns("o:o").Select
ActiveCell.FormulaLocal = "=se(n1<10;concatena(f1;f1;n1);concatena(f1;n1))"
stgFormula = Sheets("ElencoDip").Range("o1").Formula
Sheets("ElencoDip").Range("o1:o315").Formula = stgFormula
Range("h:h").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("i:i").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("m:m").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("o:o").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
Sub Cartik5()
'Cancello colonne non utilizzate ed inserisco colonne cognome e nome + filler'
Range("N:N").Activate
Selection.EntireColumn.Delete
Range("L:L").Activate
Selection.EntireColumn.Delete
Range("K:K").Activate
Selection.EntireColumn.Delete
Range("J:J").Activate
Selection.EntireColumn.Delete
Range("H:H").Activate
Selection.EntireColumn.Delete
Range("F:F").Activate
Selection.EntireColumn.Delete
Range("E:E").Activate
Selection.EntireColumn.Delete
Range("D:D").Activate
Selection.EntireColumn.Delete
Range("A:A").Activate
Selection.EntireColumn.Delete
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = " "
stgFormula = Sheets("ElencoDip").Range("b1").Formula
Sheets("ElencoDip").Range("B1:B315").Formula = stgFormula
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = " "
stgFormula = Sheets("ElencoDip").Range("d1").Formula
Sheets("ElencoDip").Range("d1:d315").Formula = stgFormula
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = "cccccccccccccccccccccccccccccc"
stgFormula = Sheets("ElencoDip").Range("e1").Formula
Sheets("ElencoDip").Range("e1:e315").Formula = stgFormula
Columns("f:f").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = "nnnnnnnnnnnnnnnnnnnn"
stgFormula = Sheets("ElencoDip").Range("f1").Formula
Sheets("ElencoDip").Range("f1:f315").Formula = stgFormula
Columns("h:h").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = " "
stgFormula = Sheets("ElencoDip").Range("h1").Formula
Sheets("ElencoDip").Range("h1:h315").Formula = stgFormula
Columns("j:j").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = " "
stgFormula = Sheets("ElencoDip").Range("j1").Formula
Sheets("ElencoDip").Range("j1:j315").Formula = stgFormula
Columns("l:l").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaLocal = " "
stgFormula = Sheets("ElencoDip").Range("l1").Formula
Sheets("ElencoDip").Range("l1:l315").Formula = stgFormula
Columns("n:n").Select
ActiveCell.FormulaLocal = " "
stgFormula = Sheets("ElencoDip").Range("n1").Formula
Sheets("ElencoDip").Range("n1:n315").Formula = stgFormula
Range("O:O").Activate
Selection.EntireColumn.Delete
End Sub
che mi esegue delle operazioni su un foglio di lavoro di excel.
A questo punto dovrei salvarlo in .txt mantenendo le formattazioni che ho inserito, comprese le colonne nelle quali ho inserito degli spazi (il programma di import su un gestionale Unix me le richiede così).
Ho già provato ad utilizzare il Salva con Nome, ma non mi mantiene suddetti spazi.
C'è un modo per poter inserire nella macro le operazioni da eseguire per salvarlo direttamente dalla macro?
Grazie dell'aiuto!