VBA copying & pasting into a different workbook

[claudiaormond]

If someone could help that would be great!! I'm quite new to the whole VBA thing!

everything on here works up until the 'activesheet.paste' part (highlighted in red). I keep getting the error *runtime '1004' paste method of worksheet class failed*

I'm not sure why. Basically, I have a worksheet which I have formatted...I've copied a section of it and I want to paste that section in a different workbook, on a new sheet. Am I totally doing the wrong thing?


Public Sub FillEmpty()

Application.ScreenUpdating = False

Rows("1:6").Select
Selection.Delete Shift:=xlUp

Cells(1, 1).Select


Set topcell = Range(Selection, Selection.End(xlDown))
Set bottomcell = Range(Selection, Selection.End(xlToRight))

If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp)

Range(topcell, bottomcell).Select


Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
If Trim(cell) = "" And cell.Row > 1 Then
cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
cell.Value = "N/A" 'cell.Offset(-1, 0).Value
End If
Next cell
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Application.ScreenUpdating = False


Call TextToNumbers
Call TextToNumbers00
Call DeleteUnecessary
Call FormatDate

ThisWorkbook.Save

Range(topcell, bottomcell).Select
Selection.Copy

Workbooks.Open "L:\IP_Sales\Reports - Daily 2006\DailyRevDownloads\Daily Workings Jul.xls"

Sheets("StaticData").Select
Sheets.Add

Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False




End Sub


Claudia

[Jim Thomlinson]

Give this a whirl.. I have cleand up a number of the selects which really are
not necessary

Public Sub FillEmpty()
dim rngCopyArea as range

Application.ScreenUpdating = False

Rows("1:6").Delete Shift:=xlUp

Set topcell = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
Set bottomcell = Range(Cells(1, 1), Cells(1, 1).End(xlToRight))

If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp)

set rngCopyArea = Range(topcell, bottomcell)

Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range
For Each cell In Intersect(rngCopyArea , _
ActiveSheet.UsedRange)
If Trim(cell) = "" And cell.Row > 1 Then
cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
cell.Value = "N/A" 'cell.Offset(-1, 0).Value
End If
Next cell
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Application.ScreenUpdating = False

Call TextToNumbers
Call TextToNumbers00
Call DeleteUnecessary
Call FormatDate

ThisWorkbook.Save

Workbooks.Open "L:\IP_Sales\Reports - Daily
2006\DailyRevDownloads\Daily Workings Jul.xls"

Sheets("StaticData").Select
Sheets.Add

rngCopyArea.Copy Sheets("Sheet1").Range("A1")
Application.CutCopyMode = False
End Sub

--
HTH...

Jim Thomlinson


"claudiaormond" wrote:

>
> If someone could help that would be great!! I'm quite new to the whole
> VBA thing!
>
> everything on here works up until the 'activesheet.paste' part
> (highlighted in red). I keep getting the error *runtime '1004' paste
> method of worksheet class failed*
>
> I'm not sure why. Basically, I have a worksheet which I have
> formatted...I've copied a section of it and I want to paste that
> section in a different workbook, on a new sheet. Am I totally doing the
> wrong thing?
>
>
> Public Sub FillEmpty()
>
> Application.ScreenUpdating = False
>
> Rows("1:6").Select
> Selection.Delete Shift:=xlUp
>
> Cells(1, 1).Select
>
>
> Set topcell = Range(Selection, Selection.End(xlDown))
> Set bottomcell = Range(Selection, Selection.End(xlToRight))
>
> If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
> If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp)
>
> Range(topcell, bottomcell).Select
>
>
> Application.ScreenUpdating = False
> Application.Calculation = xlManual
> Dim cell As Range
> For Each cell In Intersect(Selection, _
> ActiveSheet.UsedRange)
> If Trim(cell) = "" And cell.Row > 1 Then
> cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
> cell.Value = "N/A" 'cell.Offset(-1, 0).Value
> End If
> Next cell
> Application.Calculation = xlAutomatic 'xlCalculationAutomatic
> Application.ScreenUpdating = False
>
>
> Call TextToNumbers
> Call TextToNumbers00
> Call DeleteUnecessary
> Call FormatDate
>
> ThisWorkbook.Save
>
> Range(topcell, bottomcell).Select
> Selection.Copy
>
> Workbooks.Open "L:\IP_Sales\Reports - Daily
> 2006\DailyRevDownloads\Daily Workings Jul.xls"
>
> Sheets("StaticData").Select
> Sheets.Add
>
> Sheets("Sheet1").Select
> Range("A1").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
>
>
>
>
> End Sub
>
>
> Claudia
>
>
> --
> claudiaormond
> ------------------------------------------------------------------------
> claudiaormond's Profile: http://www.excelforum.com/member.php...o&userid=34447
> View this thread: http://www.excelforum.com/showthread...hreadid=562512
>
>

[claudiaormond]

Perfect! Thank you so much.