David Trinco
New member
- Joined
- Jun 5, 2019
- Messages
- 5
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel 2010
Copy value in odd cells of closed workbook->paste into next blank row of master sheet
Hi guys,
Thank you for having me at Excel Guru.
I thought maybe you could assist as I have been having some issues with the following VBA coding.
I know I've got something wrong somewhere but I don't know where exactly and it's driving me insane.
It used to work with copy paste, but with so many lines it seems to create an error after a while with an exception.
I've changed the code so many times that I'm afraid I can't remember the exact error code.
Below is the current code I have, please can you advise if you can see why it's not working or what I need to change to make it work:
Essentially I'm accessing a closed workbook but getting the user to access it via a application.getopenfilename. This then opens the file in the background and allows to copy specific values as listed in the coding below and paste it in the next available row in the master spreadsheet. As I mentioned copy paste worked but only temporarily and then kept bugging which wouldn't work for the users. They want the information from a closed workbook to populate in a master sheet so that is effectively what I'm trying to achieve below.
I know I've gone wrong, but I can for the life of me figure it out. Possibly with the range or the last row info below.
Any help would be greatly appreciated. Thanks
Hi guys,
Thank you for having me at Excel Guru.
I thought maybe you could assist as I have been having some issues with the following VBA coding.
I know I've got something wrong somewhere but I don't know where exactly and it's driving me insane.
It used to work with copy paste, but with so many lines it seems to create an error after a while with an exception.
I've changed the code so many times that I'm afraid I can't remember the exact error code.
Below is the current code I have, please can you advise if you can see why it's not working or what I need to change to make it work:
Essentially I'm accessing a closed workbook but getting the user to access it via a application.getopenfilename. This then opens the file in the background and allows to copy specific values as listed in the coding below and paste it in the next available row in the master spreadsheet. As I mentioned copy paste worked but only temporarily and then kept bugging which wouldn't work for the users. They want the information from a closed workbook to populate in a master sheet so that is effectively what I'm trying to achieve below.
I know I've gone wrong, but I can for the life of me figure it out. Possibly with the range or the last row info below.
Any help would be greatly appreciated. Thanks
Code:
Sub GetInfo()
Application.ScreenUpdating = False
Dim excel As excel.Application
Dim xFileName As Variant
Dim wb As excel.Workbook
Dim sht As excel.Worksheet
Dim LastRow As Long
With Workbooks("Test.xlsm").Worksheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End With
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
.Range("H4").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "A").Value
.Range("E13").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "B").Value
.Range("G13").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "C").Value
.Range("E5").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "D").Value
.Range("B5").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "F").Value
.Range("B23").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "G").Value
.Range("F40").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "I").Value
.Range("F41").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "J").Value
.Range("F42").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "K").Value
.Range("F43").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "L").Value
.Range("F44").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "M").Value
.Range("F45").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "N").Value
.Range("F46").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "O").Value
.Range("F47").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "P").Value
.Range("F48").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "Q").Value
.Range("F49").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "R").Value
.Range("E15").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "T").Value
.Range("J53").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "U").Value
.Range("G11").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "V").Value
.Range("B31").Value = Workbooks("Test.xlsm").Worksheets("Sheet1").Cells(LastRow, "X").Value
End With
wb.Close
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: