Need help tweaking VBA

[vmpage]

Hello.

I have a macro that loops through files in a folder and the data is pasted to a summary sheet (Copy Paste Values).

The problem I am having is that it isn't getting everything! Each file that is copied has up to 10 lines of info, but for some reason the first and second files only have 9 of the 10 lines pasted, and the third file is pasted twice, the first time skipping line 10 the second time showing all 10 lines!

I haven't had to mess with any of this in a while and I'm having trouble figuring out what I'm doing wrong!

Below is the code I'm using:

Sub MergeAllWorkbooks()
Dim CopyPasteValues As Worksheet
Dim FolderPath As String
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range

' Set copypastevalues to activeworkbook/activesheet where the macro runs
Set CopyPasteValues = ActiveWorkbook.ActiveSheet

' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\Vanessa.Page\Desktop\Daily POD Updates"

' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xlsx")

' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)


'loop through all Sheets in WorkBk
For Each sh In WorkBk.Worksheets

' Set the source range to be A2 through B11.
Set SourceRange = Worksheets(1).Range("A2:B11")

' Set the destination range to start at A1 and
' be the same size as the source range.
Set DestRange = CopyPasteValues.Range("A" & CopyPasteValues.Range("A" & Rows.Count).End(xlUp).Row + 0)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)

' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value

Next sh

' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False

' Use Dir to get the next file name.
FileName = Dir()
Loop

' Call AutoFit on the destination sheet so that all
' data is readable.
ActiveSheet.Columns.AutoFit
End Sub

Any help is greatly appreciated! Thank you!

 

[Fluff]

Try making this change

Set SourceRange = [COLOR=#ff0000]sh.[/COLOR]Worksheets(1).Range("A2:B11")

at the moment it will repeatedly copy from whatever sheet is active

 

[vmpage]

Thank you for the quick reply. I made the change but now I am getting an error and not able to run it.

 

[Fluff]

Apologies, it should be

Set SourceRange = [COLOR=#ff0000]sh.[/COLOR]Range("A2:B11")

 

[vmpage]

No error but I'm still only getting 9 of the 10 lines in each file, and now there is something extra pasted at the bottom but I'm not sure where it's coming from.

 

[Fluff]

What is the something extra?

 

[vmpage]

Last names in A11:A14 and B11:B19, and headings from a different sheet in the destination workbook in A10 and B10

 

[Fluff]

Make these 2 changes

Set DestRange = CopyPasteValues.Range("A" & Rows.Count).End(xlUp).Offset(1)
Set DestRange = DestRange.Resize(10, 2)

If that doesn't help then place the cursor anywhere in the code & press F8. This will enable you to step through the code line by line. Hopefully that will enable you to see what is going wrong & why

 

[vmpage]

That change resulted in nothing being pasted in row 1, only the lines from the first file being pasted, and still the extra info that isn't anywhere in that file being pasted in the end (last names and column headers from the master workbook).

So, I DID get all 10 lines, but only from one source, and there is extra info that isn't from the A2:B11 range.

I'm not sure what I'm looking for with F8? It highlights and moves down.

Thank you for the help. I really appreciate it

 

[Fluff]

I'm not sure what I'm looking for with F8? It highlights and moves down.

You need to step through the code looking at the worksheet while you do it in order to see what is (or isn't) happening. To try & figure out what is going wrong.