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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.