kathyb0527
04-16-2009, 03:41 PM
I have a macro that loops through files, copies and pastes a block of data (variable rows), then copies and pastes the name of the file from a different worksheet, and closes. Before the next workbook is opened, I want to autofill the file name down to the last entry. Everything I've tried so far has caused an error. Here is the code I am currently using. Everything works until the autofill line.
Private Sub DoProcessT(MyFile As String, Thssht As Worksheet, Bk As Long)
Dim wb As Workbook
Dim ws As Worksheet
Dim Rw As Long, i As Long, x As Long
Dim LastRowinA As Long
Set wb = Workbooks.Open(MyFile)
Set ws = wb.Sheets(1)
'get longest column
For i = 1 To 3
x = ws.Cells(Rows.Count, i).End(xlUp).Row
If x > Rw Then Rw = x - 2
Next
'set paste location
If Bk = 1 Then
TgtRw = 2
Else
TgtRw = TgtRw + Rw
End If
Range("H26:H73").Copy
With Thssht.Cells(TgtRw, 1)
.PasteSpecial Paste:=xlPasteValues
End With
Range("N26:N73").Copy
With Thssht.Cells(TgtRw, 2)
.PasteSpecial Paste:=xlPasteValues
End With
LastRowinA = Range("A65536").End(xlUp).Row
Thssht.Cells(TgtRw, 3).Value = wb.Sheets(2).Range("B1").Value
Thssht.Cells(TgtRw, 3).AutoFill Destination:=Range(Thssht.Cells(TgtRw, 3) & LastRowinA)
wb.Close
End Sub
Thanks for your help!
Private Sub DoProcessT(MyFile As String, Thssht As Worksheet, Bk As Long)
Dim wb As Workbook
Dim ws As Worksheet
Dim Rw As Long, i As Long, x As Long
Dim LastRowinA As Long
Set wb = Workbooks.Open(MyFile)
Set ws = wb.Sheets(1)
'get longest column
For i = 1 To 3
x = ws.Cells(Rows.Count, i).End(xlUp).Row
If x > Rw Then Rw = x - 2
Next
'set paste location
If Bk = 1 Then
TgtRw = 2
Else
TgtRw = TgtRw + Rw
End If
Range("H26:H73").Copy
With Thssht.Cells(TgtRw, 1)
.PasteSpecial Paste:=xlPasteValues
End With
Range("N26:N73").Copy
With Thssht.Cells(TgtRw, 2)
.PasteSpecial Paste:=xlPasteValues
End With
LastRowinA = Range("A65536").End(xlUp).Row
Thssht.Cells(TgtRw, 3).Value = wb.Sheets(2).Range("B1").Value
Thssht.Cells(TgtRw, 3).AutoFill Destination:=Range(Thssht.Cells(TgtRw, 3) & LastRowinA)
wb.Close
End Sub
Thanks for your help!