Results 1 to 2 of 2

Thread: VBA code to copy and paste into workbook?
Thanks Thanks: 0 Likes Likes: 0

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

    Default VBA code to copy and paste into workbook?

    What VBA code can copy and paste data in separate workbooks all in the same file folder with the same column headings and all on Sheet 1 into a separate workbook?

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

    Default Re: VBA code to copy and paste into workbook?

    Save the file you want the data copied to as a macro enabled workbook (.xlsm) in the same directory as the files to be copied and copy the code below into the standard code module1 of that workbook. The code assumes that each workbook uses Sheet 1 for data content.
    Code:
    Sub t()
    Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
    Set sh = ThisWorkbook.Sheets("Sheet1") 'Edit sheet name
    fPath = ThisWorkbook.Path & "\"
    fName = Dir(fPath & "*.xl*")
        Do While fName <> ""
            If fName <> ThisWorkbook.Name Then
                Application.DisplayAlerts = False
                Set wb = Workbooks.Open(fPath & fName)
                wb.Sheets(1).UsedRange.Offset(1).Copy 'Don't copy headers
                sh.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues 'Don't copy formulas
                wb.Close False
                Application.DisplayAlerts = True
            End If
            fName = Dir
        Loop
    End Sub
    Last edited by JLGWhiz; Jul 19th, 2019 at 02:33 PM.
    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

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
  •