PDA

View Full Version : Import data from different files and sheets into file with corresponding data cells



black_salami
08-10-2017, 01:24 AM
Hello,

05_08_17 (attached, file name is date of report) this file is a report that i make daily and it looks the same every day.
I would like to import data form this file to summary file also attached.
I want to import data to summary file based on date and data from summary and daily report file.
Also it will be used by people who have no idea about excel so best if it imports data up on button click

I have absolutely no idea how to do this or is it even possible.

Any help will be greatly appreciated.

offthelip
08-10-2017, 04:48 AM
This should do it for you:
I am making the assumption that the macro is run from the Summary file, so the first thing it asks the user to select which of the open workbooks to copy the data from.
To activate the macro from a button just add a button to the sheet using the form controls and select this macro.



Sub copyd()
Dim wb As Workbook
Awbname = ActiveWorkbook.Name


For Each wb In Workbooks
wbname = wb.Name
wb.Activate
valid = MsgBox("Is this the correct workbook to copy from?", vbYesNo)
If valid = 6 Then
wbname = wb.Name

Exit For
End If
Next wb


With Worksheets("Summary")
lastcol2 = 14
lastrow2 = 20
Sumar = Range(.Cells(1, 1), .Cells(lastrow2, lastcol2))
End With
With Worksheets("Rooms")
lastcol2 = 22
lastrow2 = 34
Roomsarr = Range(.Cells(1, 1), .Cells(lastrow2, lastcol2))
End With
With Worksheets("Food & Beverage")
lastcol2 = 22
lastrow2 = 163
FBarr = Range(.Cells(1, 1), .Cells(lastrow2, lastcol2))
End With
Workbooks(Awbname).Activate


outarr = Range(Cells(1, 2), Cells(12, 2))
outarr(1, 1) = FBarr(15, 4) + FBarr(41, 4) + FBarr(60, 4) + FBarr(78, 4) + FBarr(96, 4) + FBarr(112, 4) + FBarr(135, 4) + FBarr(153, 4)
outarr(2, 1) = FBarr(163, 4)
outarr(3, 1) = FBarr(15, 4) + FBarr(24, 4)
outarr(4, 1) = Roomsarr(27, 4)
outarr(5, 1) = Roomsarr(27, 4)
outarr(6, 1) = Sumar(10, 4)
outarr(7, 1) = Sumar(10, 4)
outarr(8, 1) = Sumar(10, 4)
outarr(9, 1) = Sumar(10, 4)
outarr(10, 1) = Roomsarr(27, 4) - Roomsarr(21, 4)
outarr(11, 1) = FBarr(27, 4) + Roomsarr(21, 4)
outarr(12, 1) = Sumar(10, 4)


Range(Cells(1, 2), Cells(12, 2)) = outarr

End Sub