Copying values from a folder full of closed workbooks

**Simsam_g** · 08-31-2015, 11:06 AM

Hi Guys
I have been trying to experiment with copying files from a closed workbook. I am particularly interested in copying and pasting using the destination method where I don't need to copy and paste as values. I want to understand how the command works but I don't really know. My knowledge of VBA is cobbled together from copying other code and figuring out what each line of code does in relation to the spreadsheets. That is all well and good until I want to understand how a code works properly then I have to try and research and find out and when I fail to understand I am going to come here and ask in the hope someone can enlighten me. So far I have the code working in some capacity.

I have attached 2 files one named original with my original code which works well and the new extraction with the new code which doesn't work so well as it leaves me with #N/A in all of the rows below the first lot of data that it manages to extract.
It won't let me post the code in here which is the second time I have not been able to post code in the forums due to the security features of this site.
ORIGINAL DATA EXTRACTION.xlsm

NEW DATA EXTRACTION.xlsm

My lack of understanding of where to put lngrow means I am unsure how to incorporate it into this. I want the first 80 rows of data to fill up then follow on with the next 80 rows of data below it from the next closed workbook. I am unsure of how to do this with this particular command. I am really just trying to learn how to use VBA as efficiently as possible. I know there is many ways to doing things but I want to have code that executes quickly and effectively and whilst copy and paste values works well I understand that it is slow and cumbersome and have noticed how much faster the destination method is. Over time and experimenting with different lines of code I hope to pick up VBA enough to the point where I can write code off the top of my head without having to reference any materials ensuring what I write is the fastest possible way of performing the tasks. You don't notice how long some code takes until it has to work through many workbooks and I am learning that a few simple tweaks can make a world of difference. I would appreciate any help anyone can offer me on this

**AlphaFrog** · 08-31-2015, 12:35 PM

Original code:

        lngrow = lngrow + 80
        ws1.Range("A2:A81" & lngrow).Value = ws.Range("A16:A95").Value

In this instance you are using lngrow as an 80 row offset for each file you copy values from. So try something like this.

        ws1.Range("A2:A81").Offset(lngrow, 0).Value = ws.Range("A16:A95").Value
        lngrow = lngrow + 80

So on the first file there is 0 offset, the second file there is an 80 row offset from A2:A81, 3rd file offset 160 rows from A2:A81...

**Simsam_g** · 08-31-2015, 07:34 PM

Originally Posted by AlphaFrog

Original code:

        lngrow = lngrow + 80
        ws1.Range("A2:A81" & lngrow).Value = ws.Range("A16:A95").Value

In this instance you are using lngrow as an 80 row offset for each file you copy values from. So try something like this.

        ws1.Range("A2:A81").Offset(lngrow, 0).Value = ws.Range("A16:A95").Value
        lngrow = lngrow + 80

So on the first file there is 0 offset, the second file there is an 80 row offset from A2:A81, 3rd file offset 160 rows from A2:A81...

Thanks for that. This worked just as I was aiming for. Thanks for the explanation it has helped me understand how this works now too.

**Mumps1** · 08-31-2015, 12:38 PM

Try this approach.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            .Sheets("Sheet1").Range("A16:B95").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Sheets("Sheet1").Range("F16:F95").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Change the path in this line :

 Const strPath As String = "C:\Test\"

to match the path of the folder containing your source files. Also change the sheet names to match yours if necessary.

**AlphaFrog** · 08-31-2015, 09:20 PM

You're welcome. Thanks for the feedback.

Copying values from a folder full of closed workbooks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying values from a folder full of closed workbooks

Re: Copying values from a folder full of closed workbooks

Re: Copying values from a folder full of closed workbooks

Re: Copying values from a folder full of closed workbooks

Re: Copying values from a folder full of closed workbooks

Thread Information

Users Browsing this Thread

Similar Threads

Copying data from a master worksheet to a folder full of closed workbooks

[SOLVED] Macro to list worksheet names of all closed workbooks in a specified folder

[SOLVED] Macro to loop through workbooks in folder and attach and email using address in closed WB

Print Full Folder of Workbooks

VBA: Copying data from closed workbooks

Copying data from closed Workbooks

Possible ? Search all closed workbooks in a folder

Bookmarks

Bookmarks

Posting Permissions