I am trying to copy a selected Range from the Main Database and Paste
it in a different Workbook,

I am using Excel 2007 but it doesn't seem to see the workbook that I
want to paste it to.

Here is the code.


Public Sub PrepareLabels()
Dim lrow As Range
Dim rc As Integer
Dim ar As Integer


Sheets(1).Activate

Set lrow = Sheet1.Range("A" & Rows.Count).End(xlUp)

lrow.Activate
ar = ActiveCell.row

Range(Cells(4, "a"), Cells(ar, "d")).Copy

Workbooks.Open Filename:="C:\Documents and Settings\Dennis\My Documents
\Excel\Labels.xls"
' Workbooks.Clear
' ActiveSheet.Delete
Range("A1").Select
ActiveSheet.Paste
Range("A2").Select
ActiveWorkbook.Save
End Sub


When the code gets to the line:- Workbooks.Open Filename:="C:
\Documents and Settings\Dennis\My Documents\Excel\Labels.xls" it
returns an error saying that the file can not be found. The filename
and everything is correct and the file does exist in the location
pointed to.

Can't seem to get around this.

Thankis for any suggestions.

Dooley

Open the file manually.

Then go into the vbe and create a macro that displays the full name of that
workbook:

msgbox thisworkbook.fullname
or
debug.print thisworkbook.fullname

And you'll see that there's a typo in your code (I betting!).

Dooley wrote:
>
> I am trying to copy a selected Range from the Main Database and Paste
> it in a different Workbook,
>
> I am using Excel 2007 but it doesn't seem to see the workbook that I
> want to paste it to.
>
> Here is the code.
>
> Public Sub PrepareLabels()
> Dim lrow As Range
> Dim rc As Integer
> Dim ar As Integer
>
> Sheets(1).Activate
>
> Set lrow = Sheet1.Range("A" & Rows.Count).End(xlUp)
>
> lrow.Activate
> ar = ActiveCell.row
>
> Range(Cells(4, "a"), Cells(ar, "d")).Copy
>
> Workbooks.Open Filename:="C:\Documents and Settings\Dennis\My Documents
> \Excel\Labels.xls"
> ' Workbooks.Clear
> ' ActiveSheet.Delete
> Range("A1").Select
> ActiveSheet.Paste
> Range("A2").Select
> ActiveWorkbook.Save
> End Sub
>
> When the code gets to the line:- Workbooks.Open Filename:="C:
> \Documents and Settings\Dennis\My Documents\Excel\Labels.xls" it
> returns an error saying that the file can not be found. The filename
> and everything is correct and the file does exist in the location
> pointed to.
>
> Can't seem to get around this.
>
> Thankis for any suggestions.
>
> Dooley

--

Dave Peterson

On Jun 23, 12:51 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Open the file manually.
>
> Then go into the vbe and create a macro that displays the full name of that
> workbook:
>
> msgbox thisworkbook.fullname
> or
> debug.print thisworkbook.fullname
>
> And you'll see that there's a typo in your code (I betting!).
>
(Original snipped)

Thanks Dave. That was the answer. I had been using Excel97 and
upgraded to 2007 as I mentioned. 2007 needs the exact name of the
file and of course the file is no longer just xls but is now xlsx and
that was causing the error.

As usual I came to the right place for expert advice.

Thanks.

Dooley

Glad you found the difference.

Dooley wrote:
>
> On Jun 23, 12:51 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Open the file manually.
> >
> > Then go into the vbe and create a macro that displays the full name of that
> > workbook:
> >
> > msgbox thisworkbook.fullname
> > or
> > debug.print thisworkbook.fullname
> >
> > And you'll see that there's a typo in your code (I betting!).
> >
> (Original snipped)
>
> Thanks Dave. That was the answer. I had been using Excel97 and
> upgraded to 2007 as I mentioned. 2007 needs the exact name of the
> file and of course the file is no longer just xls but is now xlsx and
> that was causing the error.
>
> As usual I came to the right place for expert advice.
>
> Thanks.
>
> Dooley

--

Dave Peterson