+ Reply to Thread
Results 1 to 2 of 2

Loop formula to copy and paste worksheet from multiple workbooks within same folder

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    1

    Loop formula to copy and paste worksheet from multiple workbooks within same folder

    Hello,

    I am currently working with some code that will consolidate all the "Data" tab worksheets from multiple workbooks within a specific folder into a Master Sheet.

    The columns and rows will be a fixed range to copy across. The data sheet consists of hard coded cells and formula cells picking up from a larger model within each workbook.

    The code below currently goes through each workbook and picks up the data sheets perfectly. However, it pastes it into the "New Sheet" worksheet created as formulas rather than paste special values. Could some one please assist in adding a paste special values to the code below?


    Sub Merge2MultiSheets()
    Dim xRg As Range
    Dim xSelItem As Variant
    Dim xFileDlg As FileDialog
    Dim xFileName, xSheetName, xRgStr As String
    Dim xBook, xWorkBook As Workbook
    Dim xSheet As Worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    xSheetName = "Sheet1"
    xRgStr = "A1:D4"
    Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    With xFileDlg
    If .Show = -1 Then
    xSelItem = .SelectedItems.Item(1)
    Set xWorkBook = ThisWorkbook
    Set xSheet = xWorkBook.Sheets("New Sheet")
    If xSheet Is Nothing Then
    xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.Count)).Name = "New Sheet"
    Set xSheet = xWorkBook.Sheets("New Sheet")
    End If
    xFileName = Dir(xSelItem & "\*.xlsx", vbNormal)
    If xFileName = "" Then Exit Sub
    Do Until xFileName = ""
    Set xBook = Workbooks.Open(xSelItem & "\" & xFileName)
    Set xRg = xBook.Worksheets(xSheetName).Range(xRgStr)
    xRg.Copy xSheet.Range("A65536").End(xlUp).Offset(1, 0)
    xFileName = Dir()
    xBook.Close
    Loop
    End If
    End With
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub



    Many Thanks
    L2C

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Loop formula to copy and paste worksheet from multiple workbooks within same folder

    Try replacing your xRg.Copy ... line to this:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy range of data from all workbooks in folder and paste to multiple sheets in new book
    By Brontosaurus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-19-2019, 08:52 AM
  2. [SOLVED] Loop Copy and paste multiple ranges from multiple workbooks
    By Ravenous in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2017, 05:46 PM
  3. [SOLVED] VBA to copy cell data from same worksheet in multiple workbooks within a folder.
    By lee_har in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-25-2017, 02:37 PM
  4. Copy and Paste from Workbooks to Worksheet Loop
    By David Harris 1987 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2015, 04:38 AM
  5. Copy Active Worksheet into Multiple Workbooks in Separate Folder
    By FinkKosek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2014, 04:37 PM
  6. Copy worksheet to multiple workbooks in a folder
    By MysticGenius in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2012, 07:10 AM
  7. copy & paste data from multiple workbooks to new workbook in a folder
    By Ignesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2012, 03:11 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1