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