COPY DATA from WORKBOOK to WORKBOOK

[acakoning]

Hi all, so I have already computed a MACRO which takes a look into a particular FOLDER and counts the number of workbooks, then, my SHOWPROGBAR MACRO will create the required number of sheets (NUMBER_OF_SHEETS) to match the number of workbooks in the FOLDER. Now I would like the MACRO to open each workbook and COPY the data from sheet "00" into sheet "1", close that workbook and open the next, COPY the data from sheet "00" into sheet "2", etc. How can i do this? Here is my code so far:

Sub CombineSheets()
    Dim sPath As String
    Dim sFname As String
    Dim Count As Integer
    Dim wBk As Workbook
    Dim wSht As Variant
    Dim NUMBER_OF_WORKBOOKS As Integer
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Call Delete
    
    sPath = InputBox("Enter a full path to workbooks")
    If sPath = "" Then Exit Sub
    ChDir sPath
    sFname = InputBox("Enter a filename pattern (for example 140630*) ")
    sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)
    
    Do While sFname <> ""
        Count = Count + 1
         sFname = Dir()
    Loop
    
        Range("NUMBER_OF_SHEETS").Value = Count
    
    Call ShowProgIndicator
    
    ActiveWorkbook.Save
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

 

[acakoning]

The reason for doing this in this particular way is because I don't want to copy the Name Manager or any related stuff which is present in the workbooks.

 

[acakoning]

So I have isolated the copy pasting part for better understanding: THIS IS NOT WORKING! why does it not understand were to copy it? The MACRO is in the file identified as Master:

Sub COPYPASTE()
    Dim sPath As String
    Dim sFname As String
    Dim wBk As Workbook
    Dim Master As String
    Dim wSht As Variant
    Dim wSW As Variant
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    sPath = InputBox("Enter a full path to workbooks")
    ChDir sPath
    sFname = InputBox("Enter a filename pattern (for example 140630*) ")
    sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal)
    wSht = "00"
    Do While sFname <> ""
        Set wBk = Workbooks.Open(sFname)
        Windows(sFname).Activate
        Range("A1:CP148").Select
        
            Selection = Selection.Value
            Selection.Copy
        
    Master = "FC Template_Operations_Cons_v2.1.xlsm"
    wSW = x + 1
                
            Workbooks(sFname).Sheets(wSht).Range("A1:CP148").Copy
            Workbooks(Master).Sheets(wSW).Range("A1:CP148").PasteSpecial Paste:=xlPasteValues
            
        wBk.Close False
        sFname = Dir()
    Loop
    ActiveWorkbook.Save
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
 
End Sub