C
Crownman
Hi once more:
I have a workbook that includes a macro - created with some
considerable help from Otto Moerback, one of the regular contributors
to this group. The macro sequentially copies a named range from a
group of worksheets and pastes the VALUES of the copied data into the
workbook that contains the macro. This code works perfectly. The
current code is as follows:
Option Explicit
Dim wbThis As Workbook
Dim wbOther As Workbook
Dim PathsList As Range
Dim i As Range
Dim ThePath As String
Dim TheFile As String
Sub MakeDatabase()
With Sheets("FOLDERS")
Set PathsList = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
End With
Set wbThis = ThisWorkbook
For Each i In PathsList
ThePath = i.Value
ChDir ThePath
TheFile = Dir("*.xls")
Do While TheFile <> ""
Application.EnableEvents = False
Set wbOther = Workbooks.Open(ThePath & "\" &
TheFile)
Sheets("DATABASE").Select
Application.EnableEvents = True
With wbThis.Sheets("DATA")
Range("DISTFEED").Copy
.Range("A6").End(xlDown).Offset(1,
0).PasteSpecial Paste:=xlPasteValues
End With
wbOther.Close SaveChanges:=False
TheFile = Dir
Loop
Next i
End Sub
Now I need to copy the actual data rather than the values of the
data. I have tried simply changing the line of code where the pasting
is done to the following:
..Range("A6").End(xlDown).Offset(1, 0).Paste
I now get the following error message on this modified line of code:
Run-time error '438'
Object doesn't support this property or method
Any advice would be most appreciated.
TIA
Crownman
I have a workbook that includes a macro - created with some
considerable help from Otto Moerback, one of the regular contributors
to this group. The macro sequentially copies a named range from a
group of worksheets and pastes the VALUES of the copied data into the
workbook that contains the macro. This code works perfectly. The
current code is as follows:
Option Explicit
Dim wbThis As Workbook
Dim wbOther As Workbook
Dim PathsList As Range
Dim i As Range
Dim ThePath As String
Dim TheFile As String
Sub MakeDatabase()
With Sheets("FOLDERS")
Set PathsList = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
End With
Set wbThis = ThisWorkbook
For Each i In PathsList
ThePath = i.Value
ChDir ThePath
TheFile = Dir("*.xls")
Do While TheFile <> ""
Application.EnableEvents = False
Set wbOther = Workbooks.Open(ThePath & "\" &
TheFile)
Sheets("DATABASE").Select
Application.EnableEvents = True
With wbThis.Sheets("DATA")
Range("DISTFEED").Copy
.Range("A6").End(xlDown).Offset(1,
0).PasteSpecial Paste:=xlPasteValues
End With
wbOther.Close SaveChanges:=False
TheFile = Dir
Loop
Next i
End Sub
Now I need to copy the actual data rather than the values of the
data. I have tried simply changing the line of code where the pasting
is done to the following:
..Range("A6").End(xlDown).Offset(1, 0).Paste
I now get the following error message on this modified line of code:
Run-time error '438'
Object doesn't support this property or method
Any advice would be most appreciated.
TIA
Crownman