Copy specific cells from multiple worksheets into one sheet

Question

:)

I wrote this code, but in this way I can only import one file at the time. I need to copy specific cells and not the whole range. Does anyone know how to do that?

Thank you! :)

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim nextEmptyCell As Range

    Sheet_name = Worksheets("Algemene pagina").Range("C2")
    
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(FileFilter:="Excel files (*.*), *.*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(Sheet_name).Range("A6").Copy
        ThisWorkbook.Worksheets("SelectFile").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        OpenBook.Sheets(Sheet_name).Range("A5").Copy
        ThisWorkbook.Worksheets("SelectFile").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        OpenBook.Sheets(Sheet_name).Range("A7").Copy
        ThisWorkbook.Worksheets("SelectFile").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        OpenBook.Close False
        
    End If
    Application.ScreenUpdating = True

End Sub

Yes, but Now can only open one worksheet at the time. I want to copy the specific cells of multiple worksheets in the same time. — Tess Bakker, Mar 30 '22 at 15:58
Multiple work*sheets* (tabs), or multiple work*books* (files)? — Tim Williams, Mar 30 '22 at 16:05
See https://stackoverflow.com/q/30575923/4961700 for an idea — Solar Mike, Mar 30 '22 at 16:06
So change the `Sheet_name`? I would just set the cells in worksheet B to the cells in worksheet A and avoid copy paste. — findwindow, Mar 30 '22 at 16:08
Where are these workbooks? If they're in a folder then look at using `Dir()` to loop over them. Lots of posts here covering that. — Tim Williams, Mar 30 '22 at 16:41
Eg see https://www.google.com/search?q=vba+loop+folder+site:stackoverflow.com — Tim Williams, Mar 30 '22 at 16:49

Copy specific cells from multiple worksheets into one sheet

0 Answers0