Copy range to closed workbook

[Wendy]

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).Range("A" & LRow)


DestRange.PasteSpecial xlPasteValues, , False, False

End Sub

 

[Gary''s Student]

Why not open the destination workbook first and then do the copy?

 

[Dave Peterson]

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).Range("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).Range("A" & LRow)

to

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

 

[Wendy]

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]

First, don't include the drive/path.

Set DestRange = Workbooks("Issue.xls").Worksheets(Destwsname).Range("A" & LRow)

So there's only a few things that can go wrong with this statement.

One, issue.xls isn't open.
Second, issue.xls doesn't have a worksheet that's named destwsname.

Maybe it doesn't exist--or maybe there's a typo (leading/trailing space or 0 or
o mixup???).

 

[Wendy]

Hi Dave

Destwsname is a value, as shown in my original sub.

Set WS = Sheets("LookupLists")
It fails if my issue.xls is open or closed it still doesn't work, even when
I give the actual sheetname rather than the variable name.
This is the highlighted line Set DestRange =
Workbooks("Issue.xls").Worksheets("W2Fri")
Run time error 13 type mismatch.

I need the value from destwsname to become the worksheet name as it changes
daily.

Wendy

 

[Dave Peterson]

You dropped the range portion:

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

To test:

Set DestRange _
= Workbooks("Issue.xls").Worksheets("W2Fri").Range("A1")

And after you add the range portion and if issue.xls is open, then there is no
worksheet with that name. I'd still look for spelling differences.

Hi Dave

Destwsname is a value, as shown in my original sub.

Set WS = Sheets("LookupLists")
It fails if my issue.xls is open or closed it still doesn't work, even when
I give the actual sheetname rather than the variable name.
This is the highlighted line Set DestRange =
Workbooks("Issue.xls").Worksheets("W2Fri")
Run time error 13 type mismatch.

I need the value from destwsname to become the worksheet name as it changes
daily.

Wendy