Extracting the same range from different workbooks

[yanm11]

Hey,

First of all thanks for everyone who helps out.

I’m new to this forum so hope my post will be as clear as possible.

What I am after is a VBA code that will let me chose a file in my computer and then extract from it the rows from Q26-U(something) until the range end. in different workbooks it might end in different row numbers.

Those rows then need to be copied to my working sheet.

This is a bit above my pay grade at the moment but hope somebody can help out or maybe reference material online because I couldn’t find something.

Thanks

 

[mumps]

Do you want to choose one file each time the macro is run or do you want the macro to loop through all the files in the folder and copy the data from each file? What is the name of the sheet that contains the range to be copied? What is the name of the sheet where the data will be pasted and where on the destination sheet do you want to paste the data?

 

[yanm11]

Yea I want to chose each time the files. Because the number of files differ from month to month. And also their location.
Each workbook has only one sheet and the names differ. But it’s always the first sheet. Same with where the data is copied too.
Only one sheet and it’s should copy from one file paste it in the table on the target sheet then copy from the next file and paste it after the last paste.
It should start to paste on A2 and continue from there.


Thanks a lot for helping out mumps.
I appreciate the help.

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wsDest As Worksheet, wkbSource As Workbook, FolderName As String
    Set wsDest = ThisWorkbook.Sheets(1)
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       FolderName = .SelectedItems(1) & "\"
    End With
    ChDir FolderName
    strExtension = Dir("*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(FolderName & strExtension)
        Range("Q26", Range("U" & Rows.Count).End(xlUp)).Copy wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
        wkbSource.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[yanm11]

Sorry for the late reply I was on vacation for the past week.

I just tried it but when It lets me pick the files I go into the file where there are a couple of workbooks but it doesn’t show them at all.
Like none.

What do you think is wrong?

 

[yanm11]

Okay my mistake
I see what you did now.

I changed the extension to xlsm since that is actually the right one. And it worked.

The thing is I would have prepared to chose individual workbooks rather than files full of them.

Is it possible? So I can for instance input the number of workbooks I need and chose individually each of them and then the loop is over.

Can this work?

Thanks a lot this is already a huge help.

 

[yanm11]

Alright after some tries I got it to work how I wanted it to

Thanks a lot mumps!!
You really helped me a ton!

 

[mumps]

You are very welcome.