Seeking Excell macro code  Summary of several county worksheets in the folder, Excell data in a multi-sheet Excell

3 Answers

1. +2Votes  

   “Summary of the Excel1 data of several counties under the folder in a multi-sheet Excell”

   Read it several times and feel embarrassed.

   Question: There are several worksheets in the excel table, which summarizes the data of several counties in the same blank excel table with the same format

   Answer: See “The format of multiple files is the same. It is too efficient to copy one by one and paste it in the same summary table. Can you edit the macro code?”
   Know that this is not a “summary” or a “merger.”

   Answer: The summary needs to be calculated. Merging does not require calculations, it is moving together.

   Answer: Sub merge in the same workbook ()
       n = ActiveSheet.Name
       Sheets.Add After:=Sheets(Sheets.Count) ‘Add a worksheet to the last (right) side
       Sheets(n).Shapes(“Button 1”).Copy ‘Copy button for worksheet name=n
       Range(“E2”).Select: ActiveSheet.Paste: Range(“A1”).Select ‘Paste button on the added worksheet E2 cell
       Sheets(n).Select ‘Select worksheet, name=n
   For i = 1 To Sheets.Count – 1
       AbRcou ​​= Sheets(Sheets.Count).Cells(Rows.Count, 1).End(xlUp).Row ‘Number of rows added to worksheet A
       Sheets(i).[a1].CurrentRegion.Copy _
       Sheets(Sheets.Count).Cells(1, 1).Offset(IIf(AbRcou ​​= 1 And _
       Sheets(Sheets.Count).[a1] = “”, 0, AbRcou)) ‘The contents of the worksheet are copied to the added worksheet
   Next i
   End Sub

   Question: Thank you for your patience.
   The problem is a bit complicated.
   There are several excel files and unfilled blank excel files in the same folder. Each county is a separate excel file. There are several worksheets in each county excel file, and one row is fixed in each worksheet. There is only one county data, and the data of several counties are merged into the same blank excel table of the same format.

   Answer: Reference code
   Sub merge()
   ‘Merge the contents of the first worksheet of multiple workbooks under the same path into the first worksheet of the workbook with this code
   Dim MyPath$, MyName$, sh As Worksheet, AbRcou%
   Application.ScreenUpdating = False
   Set sh = ThisWorkbook.Sheets(1)
   MyPath = ThisWorkbook.Path &”\”
   MyName = Dir(MyPath &”*.xls”)
   [a1].CurrentRegion.Offset(1).Clear
   Do While MyName <> “”
   If MyName <> ThisWorkbook.Name Then
   With GetObject(MyPath &MyName)
   AbRcou ​​= sh.Cells(Rows.Count, 1).End(xlUp).Row
   Workbooks(MyName).Worksheets(1).[a1].CurrentRegion.Offset(IIf(AbRcou ​​= 1, 0, 1)).Copy sh.Cells(1, 1).Offset(IIf(AbRcou ​​= 1, 0, AbRcou))
   .Close False
   End With
   End If
   MyName = Dir
   Loop
   Application.ScreenUpdating = True
   MsgBox “ok”
   End Sub

   Question: How to debug

   James Jones- December 8, 2018 |

2. +5Votes  

   This function can be solved

   If multiple files have the same format, you can record a macro first, and reference it. Data, copy and modify

   Question: The format of multiple files is the same, it is too efficient to copy one by one, and then paste it in the same summary table. Code?

   Diane Hall- December 8, 2018 |

3. +7Votes  

   Installing an easy-to-use treasure is OK

   Janet- December 8, 2018 |