Results 1 to 6 of 6

Thread: Copy Range from specific sheet of all workbooks in a folder to a template and create new template in the Master sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Range from specific sheet of all workbooks in a folder to a template and create new template in the Master sheet

    Greetings! I am quite new to this forum and know very little about VBA. Seeking your expertise in a matter that I've been searching for a couple of days to no avail. I am looking for a vba code to copy a Range from a sheet named "Estimation" of multiple workbooks in a folder to a template and the template needs to be duplicated every time for a different workbook. There are many threads for appending data to the same sheet. In my case I need the template to be duplicated in the same Master workbook and then the ranges to be copied from the other workbooks to the new template. If possible, the new template could be renamed to the actual workbooks name or atleast a part of the workbooks name.
    The range from Estimation to be copied to the newly created template.
    The ranges I need to be copied and pasted is from Sheet named "Estimation" to the Template.
    "Q13:R112" to "Q13:R104" & "S13:T104" to "U13:V104"

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,031
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copy Range from specific sheet of all workbooks in a folder to a template and create new template in the Master sheet

    This assumes that
    1. the template workbook will be in the same folder as all the other workbooks and
    2, the code will be run from the template workbook
    3. the template workbook will be open, and
    4. the template sheet will be the active sheet.
    Code:
    Sub t()
    Dim sh As Worksheet, ws As Worksheet, wb As Workbook, fPath As String, fName As String
    Set sh = ActiveSheet
    fPath = ThisWorkbook.Path & "\"
    fName = Dir(fPath & "*.xl*")
        Do While fName <> ""
            If fName <> ThisWorkbook.Name Then
                Set wb = Workbooks.Open(fPath & fName)
                On Error Resume Next
                    wb.Sheets("Estimation").Range("Q13:R112").Copy
                    If Err.Number = 0 Then
                        sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                        ActiveSheet.Range("Q13").PasteSpecial xlPasteValuesAndNumberFormats
                        wb.Sheets("Estimation").Range("S13:R104").Copy ActiveSheet.Range("U13")
                    End If
                    On Error GoTo 0
                    Err.Clear
                    wb.Close False
            End If
            fName = Dir
        Loop
        Beep
        MsgBox "All Sheets have been processed.", vbInformation, "PRCESSING COMPLETE"
    End Sub
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Range from specific sheet of all workbooks in a folder to a template and create new template in the Master sheet

    @JLGWhiz - That's splendid! thank you very much... for being so prompt and it does exactly what I want it to do. I'm very grateful! God Bless!!!

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Range from specific sheet of all workbooks in a folder to a template and create new template in the Master sheet

    @JLGWhilz - Just out of Curiosity, is there anyway we can also rename the sheets with the name of the files. Maybe with a string function to accommodate the character length.

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,031
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copy Range from specific sheet of all workbooks in a folder to a template and create new template in the Master sheet

    See if this works

    Code:
    Sub t2()
    Dim sh As Worksheet, ws As Worksheet, wb As Workbook, fPath As String, fName As String
    Set sh = ActiveSheet
    fPath = ThisWorkbook.Path & "\"
    fName = Dir(fPath & "*.xl*")
        Do While fName <> ""
            If fName <> ThisWorkbook.Name Then
                Set wb = Workbooks.Open(fPath & fName)
                On Error Resume Next
                    wb.Sheets("Estimation").Range("Q13:R112").Copy
                    If Err.Number = 0 Then
                        sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                        ActiveSheet.Range("Q13").PasteSpecial xlPasteValuesAndNumberFormats
                        wb.Sheets("Estimation").Range("S13:R104").Copy ActiveSheet.Range("U13")
                        ActiveSheet.Name = Left(fName, InStr(fName, ".") - 1)
                    End If
                    On Error GoTo 0
                    Err.Clear
                    wb.Close False
            End If
            fName = Dir
        Loop
        Beep
        MsgBox "All Sheets have been processed.", vbInformation, "PRCESSING COMPLETE"
    End Sub
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Range from specific sheet of all workbooks in a folder to a template and create new template in the Master sheet

    @JLGWhiz Thank you for your support, it doesn't work though. I tried several times to no avail. Would it be because of an update link prompt that it gives everytime I run the macro? it still does not copy the workbook names to the worksheets. It shows as Template (2), Template (3) and so on.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •