Transfer data from one spreadsheet to another. HELP

D

David Trinco

New member
Joined
Jun 5, 2019
Messages
5
Reaction score
0
Points
0
Excel Version(s)
Excel 2010
Hi Excel Guru VBA Programming Masters,

I wonder if you could help, I have coding which I am trying to work out, but it doesn't seem to work for me. Please can you assist as I am struggling to find out what I'm doing wrong here :-( ???

Code: 
Sub TransferInfo()
Application.ScreenUpdating = False
Dim excel As excel.Application
Dim xFileName As Variant
Dim wb As excel.Workbook
Dim sht As excel.Worksheet, DestnSht
Set DestnSht = Workbooks("Service Report Sheet (Master) Customer Copy.xlsm").Worksheets("Quotation")
Set excel = CreateObject("excel.Application")
excel.Visible = False
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Set wb = excel.Workbooks.Open(xFileName)
Set sht = wb.Worksheets("Quotation")
With sht
  DestnSht.Cells("E4").Value = .Range("E4").Value
  DestnSht.Cells("E5").Value = .Range("E5").Value
  DestnSht.Cells("E6").Value = .Range("E6").Value
  DestnSht.Cells("E7").Value = .Range("E7").Value
  DestnSht.Cells("E8").Value = .Range("E8").Value
  DestnSht.Cells("E10").Value = .Range("E11").Value
  DestnSht.Cells("G10").Value = .Range("G11").Value
  DestnSht.Cells("E13").Value = .Range("E12").Value
  DestnSht.Cells("E14").Value = .Range("E13").Value
  DestnSht.Cells("E15").Value = .Range("E15").Value
  DestnSht.Cells("G14").Value = .Range("G13").Value
  DestnSht.Cells("B23").Value = .Range("B23").Value
  DestnSht.Cells("B33").Value = .Range("B31").Value
  DestnSht.Cells("B42").Value = .Range("B40").Value
  DestnSht.Cells("B43").Value = .Range("B41").Value
  DestnSht.Cells("B44").Value = .Range("B42").Value
  DestnSht.Cells("B45").Value = .Range("B43").Value
  DestnSht.Cells("B46").Value = .Range("B44").Value
  DestnSht.Cells("B47").Value = .Range("B45").Value
  DestnSht.Cells("B48").Value = .Range("B46").Value
  DestnSht.Cells("B49").Value = .Range("B47").Value
  DestnSht.Cells("B50").Value = .Range("B48").Value
  DestnSht.Cells("B51").Value = .Range("B49").Value
  DestnSht.Cells("D42").Value = .Range("D40").Value
  DestnSht.Cells("D43").Value = .Range("D41").Value
  DestnSht.Cells("D44").Value = .Range("D42").Value
  DestnSht.Cells("D45").Value = .Range("D43").Value
  DestnSht.Cells("D46").Value = .Range("D44").Value
  DestnSht.Cells("D47").Value = .Range("D45").Value
  DestnSht.Cells("D48").Value = .Range("D46").Value
  DestnSht.Cells("D49").Value = .Range("D47").Value
  DestnSht.Cells("D50").Value = .Range("D48").Value
  DestnSht.Cells("D51").Value = .Range("D49").Value
  DestnSht.Cells("F42").Value = .Range("F40").Value
  DestnSht.Cells("F43").Value = .Range("F41").Value
  DestnSht.Cells("F44").Value = .Range("F42").Value
  DestnSht.Cells("F45").Value = .Range("F43").Value
  DestnSht.Cells("F46").Value = .Range("F44").Value
  DestnSht.Cells("F47").Value = .Range("F45").Value
  DestnSht.Cells("F48").Value = .Range("F46").Value
  DestnSht.Cells("F49").Value = .Range("F47").Value
  DestnSht.Cells("F50").Value = .Range("F48").Value
  DestnSht.Cells("F51").Value = .Range("F49").Value
End With
wb.Close
Application.ScreenUpdating = True
End Sub
Any help would be greatly appreciated :).

Thanks
 
What, exactly, "doesn't seem to work"?
 
Hi p45cal,

Thank you for getting in touch. Apologies for any confusion caused and not clarifying exactly what wasn't working.

Every time I run the code, I get a "Run-time error '5': Invalid procedure call or argument".

I hope this makes more sense, but if you need anything else, please let me know.

Thanks,
 
I think I see. Change all instances of things like:
Cells("F44")
to:
Range("F44")

But why are you creating a new instance of Excel (and not quitting from it - I realise you are closing the workbook in that instance, but you're not quitting the Excel application and so it remains live but invisible)?

You can try:
Code: 
Sub TransferInfo()
Application.ScreenUpdating = False
Dim excel As excel.Application
Dim xFileName As Variant
Dim wb As excel.Workbook
Dim sht As excel.Worksheet, DestnSht
Set DestnSht = Workbooks("Service Report Sheet (Master) Customer Copy.xlsm").Worksheets("Quotation")
Set excel = CreateObject("excel.Application")
excel.Visible = True    'False
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Set wb = excel.Workbooks.Open(xFileName)
Set sht = wb.Worksheets("Quotation")
With sht
  DestnSht.Range("E4").Value = .Range("E4").Value
  DestnSht.Range("E5").Value = .Range("E5").Value
  DestnSht.Range("E6").Value = .Range("E6").Value
  DestnSht.Range("E7").Value = .Range("E7").Value
  DestnSht.Range("E8").Value = .Range("E8").Value
  DestnSht.Range("E10").Value = .Range("E11").Value
  DestnSht.Range("G10").Value = .Range("G11").Value
  DestnSht.Range("E13").Value = .Range("E12").Value
  DestnSht.Range("E14").Value = .Range("E13").Value
  DestnSht.Range("E15").Value = .Range("E15").Value
  DestnSht.Range("G14").Value = .Range("G13").Value
  DestnSht.Range("B23").Value = .Range("B23").Value
  DestnSht.Range("B33").Value = .Range("B31").Value
  DestnSht.Range("B42").Value = .Range("B40").Value
  DestnSht.Range("B43").Value = .Range("B41").Value
  DestnSht.Range("B44").Value = .Range("B42").Value
  DestnSht.Range("B45").Value = .Range("B43").Value
  DestnSht.Range("B46").Value = .Range("B44").Value
  DestnSht.Range("B47").Value = .Range("B45").Value
  DestnSht.Range("B48").Value = .Range("B46").Value
  DestnSht.Range("B49").Value = .Range("B47").Value
  DestnSht.Range("B50").Value = .Range("B48").Value
  DestnSht.Range("B51").Value = .Range("B49").Value
  DestnSht.Range("D42").Value = .Range("D40").Value
  DestnSht.Range("D43").Value = .Range("D41").Value
  DestnSht.Range("D44").Value = .Range("D42").Value
  DestnSht.Range("D45").Value = .Range("D43").Value
  DestnSht.Range("D46").Value = .Range("D44").Value
  DestnSht.Range("D47").Value = .Range("D45").Value
  DestnSht.Range("D48").Value = .Range("D46").Value
  DestnSht.Range("D49").Value = .Range("D47").Value
  DestnSht.Range("D50").Value = .Range("D48").Value
  DestnSht.Range("D51").Value = .Range("D49").Value
  DestnSht.Range("F42").Value = .Range("F40").Value
  DestnSht.Range("F43").Value = .Range("F41").Value
  DestnSht.Range("F44").Value = .Range("F42").Value
  DestnSht.Range("F45").Value = .Range("F43").Value
  DestnSht.Range("F46").Value = .Range("F44").Value
  DestnSht.Range("F47").Value = .Range("F45").Value
  DestnSht.Range("F48").Value = .Range("F46").Value
  DestnSht.Range("F49").Value = .Range("F47").Value
  DestnSht.Range("F50").Value = .Range("F48").Value
  DestnSht.Range("F51").Value = .Range("F49").Value
End With
wb.Close
excel.Quit
Application.ScreenUpdating = True
End Sub
or a bit shorter and without initiating a new instance of the Excel application:
Code: 
Sub TransferInfo2()
Application.ScreenUpdating = False
Dim xFileName As Variant
Dim wb As excel.Workbook
Dim sht As excel.Worksheet, DestnSht

Set DestnSht = Workbooks("Service Report Sheet (Master) Customer Copy.xlsm").Worksheets("Quotation")
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Set wb = Workbooks.Open(xFileName)
Set sht = wb.Worksheets("Quotation")
With sht
  DestnSht.Range("E4:E8").Value = .Range("E4:E8").Value
  DestnSht.Range("E10").Value = .Range("E11").Value
  DestnSht.Range("G10").Value = .Range("G11").Value
  DestnSht.Range("E13:E14").Value = .Range("E12:E13").Value
  DestnSht.Range("E15").Value = .Range("E15").Value
  DestnSht.Range("G14").Value = .Range("G13").Value
  DestnSht.Range("B23").Value = .Range("B23").Value
  DestnSht.Range("B33").Value = .Range("B31").Value
  DestnSht.Range("B42:B51").Value = .Range("B40:B49").Value
  DestnSht.Range("D42:D51").Value = .Range("D40:D49").Value
  DestnSht.Range("F42:F51").Value = .Range("F40:F49").Value
End With
wb.Close
Application.ScreenUpdating = True
End Sub
 
Hi p45cal,

Thank you so much for your assistance. Both versions work absolutely fine :).

Apologies for not confirming the exact line this issue was occurring on.

Your help was very much appreciated.

Thanks,
 
You must log in or register to reply here.
Back
Top