Hi

The Destrange statement doesn't work when the issue workbook is open or
closed, with or without the drivepath - I tried both.
On the LRow I only want the data that is currently there copying to the
issue workbook and choosing the correct worksheet name.

Wendy

"Dave Peterson" wrote in message
...
The first thing is that the issue.xls workbook has to be open for this to
run.

The second thing is that you refer to (the already open workbook) like:

Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" &
LRow)
(don't include the drive or path in the workbooks())

And it looks like you're overwriting the last cell.
LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1

This goes from the bottom of column A to the last used cell in column A.
Then
drops down (with the .offset(1,0), but then you subtract 1.

If you really wanted that, you could use:
LRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
But I would think you'd want:
LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
(the next available row)

=====
If you really wanted lRow to be the last used row, then you could change
this:

Set DestRange _
= Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow)

to

Set DestRange _
= Workbooks("Issue.xls").Worksheets(Destwsname).Rang e("A" & LRow + 1)

Wendy wrote:

Hi

I'm trying to copy a range from the issue worksheet which is in the open
workbook to the Issue workbook. The sheet name is from a dynamic formula
on
the lookuplists sheet which is open but hidden.

It fails on the set Destrange line.

Thanks

Wendy
Sub Movepost()

Dim DestWB As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Dim WS As Worksheet
Dim LRow As Integer
Dim DestRow As Integer
Dim DestWs As Worksheet
Dim Destwsname As String

Set WS = Sheets("LookupLists")
Destwsname = Range("C1").Value 'week number and day eg W1Mon
Set WS = Nothing

Set WS = Worksheets("Issue")
LRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1
Set SourceRange = ThisWorkbook.Worksheets("Issue").Range("A2:B" &
LRow)
SourceRange.Copy

Set DestRange =
Workbooks("d:\Issue.xls").Worksheets(Destwsname).R ange("A" & LRow)

DestRange.PasteSpecial xlPasteValues, , False, False

End Sub

--

Dave Peterson