Macro Code problem

[dolphn22]

Hi all,

I was provided code to go to a folder and combine the tabs in each file and put into one workbook, one worksheet. It works to the point where it opens the first file, and copies, but then does not paste and won't complete. It gets stuck at the line bolded below. Any help here would be greatly appreciated. Thank you gentleman!


Dolphn22


Sub Macro()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer
RowofCopySheet = 2 ' Row to start on in the sheets you are copying from
ThisWB = ActiveWorkbook.Name
path = ("C:\Users\derbert\Desktop\Combined")
Application.EnableEvents = False
Application.ScreenUpdating = False
Set shtDest = ActiveWorkbook.Sheets("Upload File")
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy
Sheets("Upload File").Select
Dest.PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False 'Clear Clipboard
Wkb.Close False
End If
Filename = Dir()
Loop

Range("A1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

 

[par60056]

The problem is that the "Upload File" sheet is not in the active workbook. Preface it with "Workbooks(ThisWB)" or use shtDest.Select.

 

[dolphn22]

Thank you for your quick response. So that line should look like this?

Workbooks(ThisWB) Sheets("Upload File").Select

I am still learning about macros, so I apologize in advance for having to ask.

 

[par60056]

Close. Need a "." before Sheets

Workbooks(ThisWB).Sheets("Upload File").Select


You need to tell it which workbook to find the sheet in

 

[dolphn22]

OK, so I tried the line of code above exactly how you had it, and also tried it where it says "This WB", I changed it to the workbook name, and neither worked, it still hung up at that point. Do you have any ideas?

 

[par60056]

What is the error given?

Maybe try Activate instead of Select

Workbooks(ThisWB).Sheets("Upload File").Activate