Databases
StackOverflow2013
Postsdbo
POMerge Multiple Worksheets from Multiple Workbooks
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMerge Multiple Worksheets from Multiple Workbooks
    primarykey
    data
    text
    <p>I have found multiple posts on merging data but I am still running into some problems. I have multiple files with multiple sheets. Example 2007-01.xls...2007-12.xls in each of these files are daily data on sheets labeled 01, 02, 03 ..... There are other sheets in the file so I can't just loop through all worksheets. I need to combine the daily data into monthly data, then all of the monthly data points into yearly.</p> <p>On the monthly data I need it to be added to the bottom of the page.</p> <p>I have added the file open changes for Excel 2007</p> <p>Here is what I have so far:</p> <pre><code>Sub RunCodeOnAllXLSFiles() Dim lCount As Long Dim wbResults As Workbook Dim wbMaster As Workbook Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbMaster = ThisWorkbook Dim oWbk As Workbook Dim sFil As String Dim sPath As String sPath = "C:\Users\test\" 'location of files ChDir sPath sFil = Dir("*.xls") 'change or add formats Do While sFil &lt;&gt; "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath &amp; "\" &amp; sFil) 'opens the file Set oWbk = Workbooks.Open(sPath &amp; "\" &amp; sFil) Sheets("01").Select ' HARD CODED FIRST DAY Range("B6:F101").Select 'AREA I NEED TO COPY Range("B6:F101").Copy wbMaster.Activate Workbooks("wbMaster").ActiveSheet.Range("B65536").End(xlUp)(2).PasteSpecial Paste:=xlValues Application.CutCopyMode = False oWbk.Close True 'close the workbook, saving changes sFil = Dir Loop ' End of LOOP On Error Goto 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub </code></pre> <p>Right now it can find the files and open them up and get to the right worksheet but when it tries to copy the data nothing is copied over.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCœur
    1. USDroter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThe `(2)` part looks out of place in `.Range("B65536").End(xlUp)(2).PasteSpecial`
      singulars
      1. POMerge Multiple Worksheets from Multiple Workbooks
      1. USbarrowc
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload