• 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
0
Nirvana
Asked:
Nirvana
  • 2
  • 2
3 Solutions
 
pcelbaCommented:
"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
dst.Sheets("<sheetname>").Range("A1:Y5000").PasteSpecial

' Close source
src.Close

' Save and close destination
dst.Save
dst.Close

End Sub

Open in new window

Of course this is just an idea without any functionality testing.
0
 
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
0
 
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
DestWs.Columns.AutoFit

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

End Sub

Open in new window

0
 
pcelbaCommented:
Ah yes, Dim x and Dim y was my fault...

To Copy active sheet you could use
x.ActiveSheet.Range("A1:Y5000").Copy

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
0
 
NirvanamanagerAuthor Commented:
Thank you so much
0
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