copy and paste whole worksheet from one workbook to another

[Allen the Computer Guy]

I need to copy an entire worksheet from one workbook to another. Currently,
i am using the following code:

Set objExcel = CreateObject("Excel.Application")
set objWorkbook = objExcel.Workbooks.Open(Filename1)
set objWorksheet = objworkbook.worksheets(1)
objExcel.displayalerts=false
Set objRange = objWorksheet.UsedRange
objRange.copy

set objworkbook = objExcel.workbooks.open(filename2)
set objworksheet = objworkbook.worksheets("FS Data")
Set objRange2 = objexcel.Range("A1")
objrange2.activate
objworksheet.paste
objworkbook.save

My data gets there, but only if i never edit the destination sheet or even
type on it. I need my copy to always start in A1. It does not. the copy
will start from whereever the cursor was last on that sheet when the file was
saved. I have to start sharing the script and the file with other users who
will not be as careful as i will.

 

[Jacob Skaria]

Try the below

Set objExcel = CreateObject("Excel.Application")
set objWorkbook1 = objExcel.Workbooks.Open(Filename1)
set objworkbook2 = objExcel.workbooks.open(filename2)

objworkbook1.worksheets(1).UsedRange.Copy _
objworkbook2.worksheets("FS Data").Range("A1")

objworkbook2.save

 

[marcus]

Hi Allen

This will open a designated workbook paste the data from the sheet you
run it from, close the workbook saving the changes. You will have to
adjust the sheet name, the file path and the copy range.

Take care

Marcus

Option Explicit

Sub OpenXL()

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim twbk As Workbook
Dim lr As Integer
Dim lw As Integer
Dim strFullName As String

Set twbk = ActiveWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False

lw = Range("A" & Rows.Count).End(xlUp).Row
twbk.Sheets("Sheet1").Range("A2:A" & lw).Copy
sPath = "R:\" 'Cell B2 of Cal sheet, location of files
ChDir sPath
sFil = Dir("Test.xls") 'change or add formats
strFullName = sPath & sFil

Set oWbk = Workbooks.Open(strFullName)
lr = oWbk.Sheets("FS Data").Range("A" & Rows.Count).End(xlUp).Row + 1
oWbk.Sheets("FS Data").Range("A" & lr).PasteSpecial xlPasteValues
oWbk.Close True 'close the workbook, saving changes
sFil = Dir

End Sub

 

[Allen the Computer Guy]

This worked perfectly and was too easy. I am kicking myself for not figuring
this one out on my own.

Thanks.