MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VB copying & pasting entire Excel worksheets


Posted by tom on January 29, 2002 9:15 AM

A VB program is successfully creating workbooks and modifying numerous worksheets. Problem is when we copy an entire worksheet, the paste will not paste starting in cell A1. How can we force the copy to start in cell A1?
Our code:
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(gstrTemplateFile)
objWB.Sheets("instructions").Activate
objExcel.DisplayAlerts = False
' Activate worksheet
objExcel.ActiveSheet.Cells.Select
objExcel.Selection.Copy objWB.Close
Set objWB = Nothing
' open new workbook
Set objWB = objExcel.Workbooks.Open(gstrCurrentFile)
Set objWS2 = objWB.Sheets("instructions")
objWB.Sheets"instructions").Activate
ojWB.ActiveSheet.Paste
' Above does NOT paste into cell A1
objWB.Save ' Save the Workbook
objWB.Close


Posted by faster on January 29, 2002 9:25 AM


Should this
objWB.Sheets"instructions").Activate

be this?
objWB.Sheets("instructions").Activate


you might try
objWB.Sheets("instructions").Range("A1").select
ojWB.ActiveSheet.Paste