VBA copying & pasting into a different workbook

[claudiaormond]

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

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

I'm not sure why. Basically, I have a worksheet which I hav
formatted...I've copied a section of it and I want to paste tha
section in a different workbook, on a new sheet. Am I totally doing th
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 - Dail
2006\DailyRevDownloads\Daily Workings Jul.xls"

Sheets("StaticData").Select
Sheets.Add

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




End Sub


Claudi

 

[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

 

[claudiaormond]

Perfect! Thank you so much