Trouble with Dynamic Range

Hello All,

I hope someone can help me out with this issue. I have hundreds of excel files from which I'm extracting data and copying on to a new worksheet.
Each set of data is associated to a Project ID that I must copy to each row of data once it is copied. The set of data can range from 1 to presumably 65536 rows.

I've written the code below thinking I had it, but my destination range gives me a runtime error of 438. I believe my syntax might be wrong but can't quite figure it out.

Thanks in advance for the help.

J.

    Const MyFolder = "N:\DFO Financial Control\Financial Management\Temp Budgets\CESDFO\"
    
    Filename = Dir(MyFolder & "*.xls")
    Do While Filename <> ""
    Workbooks.Open (MyFolder & Filename)
    ActiveWorkbook.Sheets("Input").Select
    Range("B21").Select
    x = Range("B65536").End(xlUp).Row
    ActiveSheet.Range(Cells(21, 2), Cells(x, 10)).Select
    Selection.Copy
    Application.DisplayAlerts = False
    Workbooks("Macro for Diana.xls").Activate
    ActiveWorkbook.Sheets("DataCapturing").Select
    Dim Sourc As Range
    Set Sourc = Sheets("DataCapturing").Range("a1")
    i = 1
    Do While IsEmpty(Sourc.Cells(i, 1)) = False
    i = i + 1
    Loop
    Sourc.Cells(i, 1).Select
    ActiveSheet.Paste
    
    'copy project type
    Windows(Filename).Activate
    Range("C12").Select
    Selection.Copy
    Windows("Macro for Diana.xls").Activate
    ActiveWorkbook.Sheets("DataCapturing").Select
    Dim LastRowProjectType As Long
    With ActiveSheet
    LastRowProjectType = .Cells(.Rows.Count, "J").End(xlUp).Row
    nProjectType = ActiveSheet.Range("A2").End(xlDown).Row
    Selection.Paste Destination:=Range("J" & LastRowProjectType & "" & ":J" & nProjectType), Type:=xlFillDefault
    End With

Hi

Try changing

Selection.Paste Destination:=.Range("J" & LastRowProjectType & ":J" & nProjectType), Type:=xlFillDefault

to

activesheet.Paste Destination:=.Range("J" & LastRowProjectType & ":J" & nProjectType)

rylo

Rylo,

Thanks a lot! I think it was the "Type:=xlFillDefault" that was causing problems.

Thanks again...

J.