Creazione di tabelle in più tabs

Utilizzo un addon per estrarre 62 rapporti da Salesforce.com in Excel. L'addon mette each report su una scheda separata in un foglio di lavoro (che è perfetto). In precedenza, stavo copiando e incollandomi in un altro foglio di lavoro che era una copia a specchio, salvo che avesse tabelle impostate su ciascuna scheda. Sto cercando un modo per ridurre la mia copia e impostare una macro che selezionerebbe automaticamente l'intervallo in una scheda, creare una tabella e continuare in tutte le 62 tabs. Il problema che eseguiamo è che ciascuna scheda contiene una quantità variabile di righe.

Parte delle mie macro di formattazione hanno un intervallo fino alla row 580, che è una distanza sicura per formattare celle specifiche per scopi di colore del text. Poichè sto vagando intorno cercando di creare le tabelle, questo compone ciò che ho mentre crea la tabella fino alla row 580. Se c'è un modo per select solo le celle che dispongono di dati in esse per colonne specifiche (less la row A che sarebbe l'intestazione della tabella), che functionrebbe anche. Ho solo bisogno della tabella per comprendere le righe che contengono informazioni.

  • Excel Solver VBA: Valore di stato "1" non assunto
  • Libreria Java efficace per la lettura di file Excel?
  • Effettuare una sola casella di controllo in una fila?
  • Computazione dei rendimenti trimestrali totali dai dati giornalieri
  • Eliminare un singolo foglio in Excel utilizzando SSIS
  • Caratteri cinesi persi in Excel
  • Ho inviato la macro corrente che uso:

    Sub SAR_Format() ' ' SAR_Format Macro ' SAR Table Formating ' ' Dim ws As Worksheet For Each ws In Sheets ws.Activate Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:G").Select Selection.ColumnWidth = 7.86 Selection.ColumnWidth = 10 Columns("H:H").Select Columns("H:H").EntireColumn.AutoFit Columns("I:I").EntireColumn.AutoFit Columns("J:J").EntireColumn.AutoFit Columns("K:K").EntireColumn.AutoFit Columns("L:L").ColumnWidth = 8.86 Columns("L:L").ColumnWidth = 7.57 Rows("1:1").RowHeight = 29.25 With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("M:M").EntireColumn.AutoFit Columns("N:N").EntireColumn.AutoFit ActiveWindow.ScrollColumn = 2 Columns("N:N").EntireColumn.AutoFit Columns("O:O").EntireColumn.AutoFit Columns("P:P").EntireColumn.AutoFit ActiveWindow.ScrollColumn = 1 Range("C2:G580").Select Selection.NumberFormat = "$#,##0_);[Red]($#,##0)" With Selection.Font .Color = -65536 .TintAndShade = 0 End With Range("A2:P580").Select With Selection.Font .Name = "Calibri" .Size = 10 End With ActiveWindow.SmallScroll Down:=-348 ActiveWindow.ScrollRow = 191 ActiveWindow.ScrollRow = 189 ActiveWindow.ScrollRow = 186 ActiveWindow.ScrollRow = 184 ActiveWindow.ScrollRow = 179 ActiveWindow.ScrollRow = 174 ActiveWindow.ScrollRow = 167 ActiveWindow.ScrollRow = 159 ActiveWindow.ScrollRow = 149 ActiveWindow.ScrollRow = 141 ActiveWindow.ScrollRow = 133 ActiveWindow.ScrollRow = 124 ActiveWindow.ScrollRow = 118 ActiveWindow.ScrollRow = 89 ActiveWindow.ScrollRow = 86 ActiveWindow.ScrollRow = 82 ActiveWindow.ScrollRow = 79 ActiveWindow.ScrollRow = 76 ActiveWindow.ScrollRow = 74 ActiveWindow.ScrollRow = 71 ActiveWindow.ScrollRow = 70 ActiveWindow.ScrollRow = 67 ActiveWindow.ScrollRow = 65 ActiveWindow.ScrollRow = 63 ActiveWindow.ScrollRow = 48 ActiveWindow.ScrollRow = 45 ActiveWindow.ScrollRow = 43 ActiveWindow.ScrollRow = 40 ActiveWindow.ScrollRow = 38 ActiveWindow.ScrollRow = 35 ActiveWindow.ScrollRow = 32 ActiveWindow.ScrollRow = 30 ActiveWindow.ScrollRow = 28 ActiveWindow.ScrollRow = 26 ActiveWindow.ScrollRow = 25 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 20 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 16 ActiveWindow.ScrollRow = 15 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 Next ws End Sub 

    Questa macro corregge le larghezze delle mie colonne, fornisce la dimensione, il formato e il colore appropriati per le celle specifiche, ma non crea la tabella.

  • L'auto di gamma invia il corpo di posta elettronica
  • Come convertire un dato numero ordinario (da Excel) a una data
  • RTE 3706 Imansible trovare Provider Excel VBA
  • Riesaminare automaticamente l'UDF non volatile
  • C'è un modo per informare PowerPoint di non aprire Excel quando crea grafici?
  • Copiare le ultime 20 colonne con le celle non in bianco nell'intervallo
  • One Solution collect form web for “Creazione di tabelle in più tabs”

    Ho fatto alcune modifiche al tuo codice originale (vedi commenti nel codice) . Questa soluzione presuppone che i dati dei report iniziano con A1

     Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Fine con Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub . HorizontalAlignment = xlGeneral Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .WrapText = Vero Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .Orientazione = 0 Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .AddIndent = Falso Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .IndentLevel = 0 Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .ShrinkToFit = Falso Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .ReadingOrder = xlContext Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .MergeCells = Falso Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Fine con Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Con .DataBodyRange Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Fine con Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Fine con Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Con .Range Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub .Font.Size = 10 Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Fine con Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub Fine con: Avanti Sub SAR_Format() Dim Wsh As Worksheet, Lob As ListObject Dim rSrc As Range, lRowLst As Long Rem Loop Through Each Worksheet in Workbook For Each Wsh In ThisWorkbook.Worksheets 'Use this line if procedure is in the workbook with data 'For Each ws In Workbooks("WbkName").Sheets 'Use this line if procedure is not in the workbook with data - update workbook name With Wsh Rem Set Source Range Application.Goto .Cells(1), 1 lRowLst = .Cells(.Rows.Count, 1).End(xlUp).Row 'Get Last row of data Set rSrc = .Range("A1:P" & lRowLst) Rem Add ListObject - Excel Table Set Lob = .ListObjects.Add(xlSrcRange, rSrc, , xlYes) End With Rem Work with Excel Table (ListObject in VBA) With Lob .TableStyle = "TableStyleMedium6" .ShowTableStyleRowStripes = False Rem Header Settings With .HeaderRowRange .RowHeight = 29.25 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rem Body Settings With .DataBodyRange With Range(.Columns(3), .Columns(7)) .NumberFormat = "$#,##0_);[Red]($#,##0)" .Font.Color = RGB(0, 0, 255) End With 'It's not clear if this setting corresponds to the header or to columns H 'I have it in both just delete the one no needed ' With .Columns(8) ' .HorizontalAlignment = xlGeneral ' .VerticalAlignment = xlBottom ' .WrapText = True ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False ' End With End With Rem General Settings With .Range .Font.Name = "Calibri" .Font.Size = 10 .EntireColumn.AutoFit .Columns(12).ColumnWidth = 7.57 Range(.Columns(3), .Columns(7)).ColumnWidth = 10 End With End With: Next End Sub 

    Suggerisci di visitare queste pagine:

    Variabili e costanti , Oggetto applicazione (Excel) , Oggetti di Excel

    Con l'istruzione , l' object di gamma (Excel) , i membri di ListObject (Excel)

    Fathemes sapere di qualunque domanda che si possa avere sul codice.

    Microsoft Office Excel Spreadsheet è il miglior software di Office, Excel VBA e formule Excel rendono il foglio di calcolo più veloce.