• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 46
  • Last Modified:

copy from one spreadsheet and paste to another

copy from one spreadsheet to another

I download a report from Oracle,  data from "sheet 1" needs to be copied (Range A1: Y5000) without opening the file and needs to be pasted in 2nd sheet of destination file

Source File can be in any of the excel formats ( XLSX, xls, XLSM)

Destination file is excel 2007 ( paste in the same format of source file)

Please help

Thank you
  • 2
  • 2
3 Solutions
"without opening the file" ?  Hmmm...  David Copperfield could do it. Maybe.

Even when you use some application for this purpose the application must open both spreadsheet files.

You may use Excel Automation or write a short VBA code to achieve this requirement.

The VBA code would look like this one:
Sub CopySheet()
Dim x As Workbook
Dim y As Workbook

' Open both workbooks
Set src = Workbooks.Open("<path to source XLS>")
Set dst = Workbooks.Open("<path to destination XLS>")

' Copy range of cells to clipboard
src.Sheets("<name of the 1st sheet>").Range("A1:Y5000").Copy

' Paste to destination worksheet

' Close source

' Save and close destination

End Sub

Open in new window

Of course this is just an idea without any functionality testing.
NirvanamanagerAuthor Commented:
Thanks a lot works perfect I have just changed the variable names to src and dst.

if I can ask one change, when pasting can it paste current active workbook/sheet
ShumsDistinguished Expert - 2017Commented:
Hi Nirvana,

Please do the necessary changes (path, filename & sheet names) and paste below code in your destination workbook and run macro UpdateDestFile:
Sub UpdateDestFile()
Dim DestWB As Workbook, SrcWB As Workbook
Dim DestWs As Worksheet, SrcWs As Worksheet
Dim SrcLR As Long, DestLR As Long
Dim MyPath As String, MyExtn As String, SrcFName As String

'Define Variables
Set DestWB = ThisWorkbook
Set DestWs = DestWB.Worksheets("Sheet2") 'Change Sheet Name here
DestLR = DestWs.Range("A" & Rows.Count).End(xlUp).Row
MyPath = "C:\Test\" 'Change your path here
MyExtn = "Sample.xl*" 'Change your file name here
ChDir MyPath
SrcFName = Dir(MyPath & MyExtn)
Set SrcWB = Workbooks.Open(SrcFName)
Set SrcWs = SrcWB.Sheets("Sheet1") 'Change Sheet Name here
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Uploading Data !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Clear Old Data
DestWs.Range("A1:Y" & DestLR).Clear

'Copy Data
SrcWs.Range("A1:Y" & SrcLR).Copy
DestWs.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False

'Close Source Workbook
Application.DisplayAlerts = False
SrcWB.Close SaveChanges:=False
Application.DisplayAlerts = True

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With

End Sub

Open in new window

Ah yes, Dim x and Dim y was my fault...

To Copy active sheet you could use

and paste to active sheet should work the same way.

You have plenty of methods and properties available in Excel. Look e.g. here: https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/workbook-object-excel
NirvanamanagerAuthor Commented:
Thank you so much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now