Copy range to closed workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wendy - 10 Apr 2008 15:39 GMT

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

Reply to this Message

Gary''s Student - 10 Apr 2008 15:51 GMT

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

Signature

Gary''s Student - gsnu200778

> Hi
>
[quoted text clipped - 33 lines]
>
> End Sub

Reply to this Message

Dave Peterson - 10 Apr 2008 15:56 GMT

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)

> Hi
>
[quoted text clipped - 33 lines]
>
> End Sub

Signature

Dave Peterson

Reply to this Message

Wendy - 10 Apr 2008 16:36 GMT

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

> The first thing is that the issue.xls workbook has to be open for this to
> run.
[quoted text clipped - 69 lines]
>>
>> End Sub

Reply to this Message

Dave Peterson - 10 Apr 2008 17:44 GMT

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???).

> Hi
>
[quoted text clipped - 82 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Wendy - 11 Apr 2008 15:16 GMT

Hi Dave

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

Set WS = Sheets("LookupLists")

>> >> Destwsname = Range("C1").Value 'week number and day eg W1Mon

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

> First, don't include the drive/path.
>
[quoted text clipped - 102 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 11 Apr 2008 16:58 GMT

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
>
[quoted text clipped - 124 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message