Not tested, but this has a lot of the errors, extraneous stuff removed

Sub Import()
Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet
Dim FileName As Workbook
Dim iLstRow As Long

Set DestinationWB = ThisWorkbook 'This file
Set DestinationWS = DestinationWB.Worksheets("Import") 'This file Sheet
Import"
Set FileName = ThisWorkbook.Worksheets("Data").Range("D10") 'contents of
this file, sheet "data", Cell D10

'Open the file named in cell D10 on on sheet "data"
Set SourceWB = Workbooks.Open(FileName:=FileName, UpdateLinks:=0)
HiddenCells = Range("AV3").Value
ActiveSheet.Unprotect Password:=HiddenCells 'Unprotect Sheet
iLastRow = Range("AG72").End(xlDown).Row
Range("B2").Resize(iLastRow - 1, 31).Copy _
Destination:=DestinationWS.Range("A2").End(xlDown) .Offset(1, 0)
Application.CutCopyMode = False
SourceWB.Protect Password:=HiddenCells, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True 'Reprotect Workbook
SourceWB.Save
SourceWB.Close
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen McKenzie" wrote in message
...
I've compiled the following code to open a file (determined by value in
range
D10), identify range of cells to be copied, then copy them into the next
available row in another spreadsheet.

Sub Import()


Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet
Dim FileName As Workbook

Set DestinationWB = ThisWorkbook 'This file
Set DestinationWS = DestinationWB.Worksheets("Import") 'This file Sheet
"Import"
Set FileName = ThisWorkbook.Worksheets("Data").Range("d10") 'contents
of
this file, sheet "data", Cell D10
Set SourceWB = FileName

Workbooks.Open SourceWB, UpdateLinks:=0 'Open the file named in cell
D10
on on sheet "data"
ActiveSheet.Select
Let HiddenCells = Range("AV3")
ActiveSheet.Unprotect Password:=HiddenCells 'Unprotect Sheet
Range("AG72:B72").Select
Range(Selection, Selection.End(xlDown)).Select 'Select rows from row 72
down which has data in column AG
Selection.Copy
Destination:=DestinationWB.DestinationWS.Range("A2 ").Select
'Paste data selected above into this workbook
'sheet "import" after last row with data in column A
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
SourceWB.Protect Password:=HiddenCells, DrawingObjects:=True,
Contents:=True, Scenarios:=True 'Reprotect Workbook
SourceWB.Save
SourceWB.Close
End Sub

I'm getting nowhere with this and am hoping someone can help me with the
code. I'm trying to learn VBA so need to understand where I'm going wrong