regarding variables, instead of writing out the ranges each time you
want to do something with them, you assign a variable to them. you
can tell excel to figure out the range for you. for instance, in this
section of code:

Range("AG72:B72").Select
Range(Selection, Selection.End(xlDown)).Select 'Select rows from row 72
down which has data in column AG
Selection.Copy

you never know if you've added more rows & perhaps 72 is not the final
row anymore. so instead, you can let excel find it for you. going
from the bottom UP is better than going xlDown because if there's any
blanks, it will stop @ the first blank.

dim myRange as range
dim myRow as integer

myRow = worksheet.cells(5000,2).end(xlup).offset(1,0).row

so it goes to row 5000 in column b, moves up to the 1st populated
cell, and then offsets one row back downward to find the first blank
row.

you know your range always goes from column b to column ag. therefore
you can tell excel to make your range as follows:

set myRange = worksheet.range("b:ag" & myRow)

once you have the variable declared & set, you can do things with it,
without selecting it:

myRange.CopyDestination:=DestinationWB.Destination WS.Range("A2")

this range you could also qualify with another variable & set above.
you've got the right idea with dim'ing the workbooks by variable
names. you need a SourceWS though, too..... then a rDest (destination
range) & a rSource (source range - which would be myRange above).
hope i'm not turning you around in circles!
:)
susan




On Apr 24, 6:10 am, Karen McKenzie
wrote:
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