VBA copying & pasting into a different workbook
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 -- claudiaormon ----------------------------------------------------------------------- claudiaormond's Profile: http://www.excelforum.com/member.php...fo&userid=3444 View this thread: http://www.excelforum.com/showthread.php?threadid=56251 |
VBA copying & pasting into a different workbook
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 |
VBA copying & pasting into a different workbook
Perfect! Thank you so much -- claudiaormon ----------------------------------------------------------------------- claudiaormond's Profile: http://www.excelforum.com/member.php...fo&userid=3444 View this thread: http://www.excelforum.com/showthread.php?threadid=56251 |
All times are GMT +1. The time now is 04:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com