Copy and paste values only from multiple workbooks on a apecific worksheet in each workbook into one sheet in Excel

Question

I am trying to copy and paste values only, from a specific range from multiple workbooks located in one folder that all has a worksheet named "summary". The code below I'm using Pastes the formatting and all, I only want to paste values. This is my current code:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\Users\St\Desktop\ATP\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("SUMMARY").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("SUMMARY").Range("A2:AG" & LastRow).Copy wkbDest.Sheets("SHEET1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

How do I change it so it only pastes values?

dupe https://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues — , Jun 14 '18 at 15:02
I want to only paste values, not the formatting, my current code pastes the formatting. — Sapphire, Jun 14 '18 at 15:04
Possible duplicate of [Make range of cells equal to another range of cells in different workbooks](https://stackoverflow.com/questions/36312997/make-range-of-cells-equal-to-another-range-of-cells-in-different-workbooks) — BruceWayne, Jun 14 '18 at 15:13

BruceWayne · Answer 1 · 2018-06-14T15:12:51.143

0

Just set the values equal:

With wkbSource
    lastRow = .Sheets("SUMMARY").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    wkbDest.Sheets("SHEET1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = .Sheets("SUMMARY").Range("A2:AG" & lastRow).Value
    .Close savechanges:=False
End With

Note: You may need to tweak the first part (...Offset(1,0).Value) to be the same size as the origin information. I think the below would work:

Dim infoToCopy As Range
Set infoToCopy = wkbSource.Sheets("SUMMARY").Range("A2:AG" & lastRow)
Dim firstRow As Long

With wkbSource
    lastRow = .Sheets("SUMMARY").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    firstRow = wkbDest.Sheets("SHEET1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    wkbDest.Sheets("SHEET1").Range("A" & firstRow & ":A" & firstRow + infoToCopy.Rows.Count - 1).Value = infoToCopy.Value
    .Close savechanges:=False
End With

edited Jun 14 '18 at 15:12

answered Jun 14 '18 at 15:05

BruceWayne

22,923
15
65
110

where do I place the information for the first part? – Sapphire Jun 14 '18 at 15:27
Can you add your edits to my code so that I can copy and paste it please? – Sapphire Jun 14 '18 at 15:27
@Sapphire - You should be able to do that. It's pretty much exactly your code, just swap it out. It will also help you learn how/where/when to edit the code. – BruceWayne Jun 14 '18 at 15:44
I know. I'm trying and trying and I keep getting Runtime Error '424': Object required on this line : Set infoToCopy = wkbSource.Sheets("SUMMARY").Range("A2:AG" & LastRow) – Sapphire Jun 14 '18 at 15:49
Set infoToCopy = wkbSource.Sheets("SUMMARY").Range("A2:AG" & LastRow) – Sapphire Jun 14 '18 at 16:00
@Sapphire - To clarify, you are copying a *single cell* and want that value to populate the range from `A2` to `AG[lastRow]`? – BruceWayne Jun 14 '18 at 16:03
No. I am copying a range of cells from A2:AG from multiple work books and want to copy and paste the all of those values into one sheet. – Sapphire Jun 14 '18 at 16:15
after one work book A2:AG is pasted on a master sheet, I want it to find the next empty row and paste the next work book A2:AG on the same master sheet and so on and so on – Sapphire Jun 14 '18 at 16:17

Copy and paste values only from multiple workbooks on a apecific worksheet in each workbook into one sheet in Excel

1 Answers1