Autofill last row error

[marlline]

Hi guys,

I know there are many posts on this topic. However I still could not figure out for my specific case. Please help. Thank you!
It errors at
Selection.AutoFill Destination:=ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & LastRow), Type:=xlFillCopy

The full code is as follows:
Sub DoFolder(Folder)
Dim SubFolder
Dim WKB_SOURCE As Workbook
Dim LastRow As Integer
Dim targetrow As Integer




For Each SubFolder In Folder.SubFolders
DoFolder SubFolder
Next
Dim File
For Each File In Folder.Files
targetrow = ThisWorkbook.Sheets("Output").Range("D20000").End(xlUp).Row + 1
Set WKB_SOURCE = Workbooks.Open(File, False)
WKB_SOURCE.Sheets(1).Unprotect ("BD MAP")
LastRow = WKB_SOURCE.Sheets(1).Range("C2000").End(xlUp).Row

WKB_SOURCE.Sheets(1).Range("C10:K" & LastRow).Copy
ThisWorkbook.Sheets("Output").Activate
ThisWorkbook.Sheets("Output").Range("D" & targetrow).PasteSpecial xlPasteValues
ThisWorkbook.Sheets("Output").Range("D" & targetrow).PasteSpecial xlPasteFormats

WKB_SOURCE.Sheets(1).Range("D2:D4").Copy
ThisWorkbook.Sheets("Output").Activate
ThisWorkbook.Sheets("Output").Range("A" & targetrow).PasteSpecial xlPasteValues, Transpose:=True
LastRow = ThisWorkbook.Sheets("Output").Range("D20000").End(xlUp).Row
ThisWorkbook.Sheets("output").Activate
ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & targetrow).Select
Selection.AutoFill Destination:=ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & LastRow), Type:=xlFillCopy





WKB_SOURCE.Sheets(1).Protect ("BD MAP")
WKB_SOURCE.Close savechages

Next
LastRow = ThisWorkbook.Sheets("Output").Range("D50000").End(xlUp).Row
ThisWorkbook.Sheets("Output").Range("D2").Copy
ThisWorkbook.Sheets("Output").Range("A2:C" & LastRow).PasteSpecial xlPasteFormats

End Sub

 

[marlline]

Re: Autofill last row error Please help!

anyone

Hi guys,

I know there are many posts on this topic. However I still could not figure out for my specific case. Please help. Thank you!
It errors at
Selection.AutoFill Destination:=ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & LastRow), Type:=xlFillCopy

The full code is as follows:
Sub DoFolder(Folder)
Dim SubFolder
Dim WKB_SOURCE As Workbook
Dim LastRow As Integer
Dim targetrow As Integer




For Each SubFolder In Folder.SubFolders
DoFolder SubFolder
Next
Dim File
For Each File In Folder.Files
targetrow = ThisWorkbook.Sheets("Output").Range("D20000").End(xlUp).Row + 1
Set WKB_SOURCE = Workbooks.Open(File, False)
WKB_SOURCE.Sheets(1).Unprotect ("BD MAP")
LastRow = WKB_SOURCE.Sheets(1).Range("C2000").End(xlUp).Row

WKB_SOURCE.Sheets(1).Range("C10:K" & LastRow).Copy
ThisWorkbook.Sheets("Output").Activate
ThisWorkbook.Sheets("Output").Range("D" & targetrow).PasteSpecial xlPasteValues
ThisWorkbook.Sheets("Output").Range("D" & targetrow).PasteSpecial xlPasteFormats

WKB_SOURCE.Sheets(1).Range("D2:D4").Copy
ThisWorkbook.Sheets("Output").Activate
ThisWorkbook.Sheets("Output").Range("A" & targetrow).PasteSpecial xlPasteValues, Transpose:=True
LastRow = ThisWorkbook.Sheets("Output").Range("D20000").End(xlUp).Row
ThisWorkbook.Sheets("output").Activate
ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & targetrow).Select
Selection.AutoFill Destination:=ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & LastRow), Type:=xlFillCopy





WKB_SOURCE.Sheets(1).Protect ("BD MAP")
WKB_SOURCE.Close savechages

Next
LastRow = ThisWorkbook.Sheets("Output").Range("D50000").End(xlUp).Row
ThisWorkbook.Sheets("Output").Range("D2").Copy
ThisWorkbook.Sheets("Output").Range("A2:C" & LastRow).PasteSpecial xlPasteFormats

End Sub

 

[MARK858]

Re: Autofill last row error Please help!

I don't get an error when I just run the autofill code by itself (I wouldn't personally use Select though but I don't think that will be your issue) so what does the error actually state?

 

[ukmikeb]

Re: Autofill last row error Please help!

Hi

Try changing :-

Dim LastRow As Integer
    Dim targetrow As Integer

to

Dim LastRow As Long
   Dim targetrow As Long

as in the last part you have :-

LastRow = ThisWorkbook.Sheets("Output").Range("D50000").End(xlUp).Row

which could exceed the Integer limit of 32767.

hth

 

[marlline]

Re: Autofill last row error Please help!

Hi when I run it with one record in the file, I run into error.

 

[marlline]

Re: Autofill last row error Please help!

I have one file with only 1 record. When I modify the code and rerun it, I still run into error.
Run-time Error "1004":
AutoFill method of Range class failed.

When I debug, it directs me to the line of:
Selection.AutoFill Destination:=ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & LastRow), Type:=xlFillCopy.

Please help again!!

 

[MARK858]

Re: Autofill last row error Please help!

What are the values of targetrow & LastRow when the code is run?
Either hover your mouse over the variables or put

MsgBox targetrow
MsgBox LastRow

immediately before the line that errors.

 

[marlline]

Re: Autofill last row error Please help!

1142

 

[marlline]

Re: Autofill last row error Please help!

Needs help!!!!!!

 

[MARK858]

Re: Autofill last row error Please help!

Asked you for 2 numbers not 1.