I did not know that was "illegal". But I just go on with the discussion on Mr.Excel.
Here is the attached workbooks. My english is not that good, but i tried to tailor a solution as good as I could.
Hmm yeah, but the macro was from another forum and it also need to search the row in the paste workbook.
Is there a way i can copy paste specific rows to the excel ark. The things i need:
- copy paste e3, e4, e9, e13 and e15 from mappe2 (book) to mappe 1 (book)
- has to copy the value in the given row if the cell next to it is empty
- the macro has to be applied from mappe1 (book)
- next month the may values (which is coming, later at the month) and run it again so it is the may values which copies to mappe1 (book)
- The workbooks has to be open
So the point is to always copy paste the lastest value from mappe2 (book) to mappe 1 - month by month.
In mappe1 there are maybe more variables to come to maybe in two months there has to e3,e4,e9,e13,e15 and add e5 and e14.
So if I can put more rows in the macro for future months copy paste it would be perfect.
I really can figure out to make the macro for choosing a value if the next cell is empty and the same when pasting it in mappe1 (book)
Thank you for the help.
Thx for the help
Because i need to import data from these 5 rows every months. Like this month mar2021, next month apr2021 etc.
Cool - and which lines do i have to remove to decreasing the chance of error.
But i have to define wbA and wbB.. Which is which? if i want to import from data Workbook to month KPI?
I want to run the macro from Month KPI workbook and importing data from the Data Workbook
Its okay using macro to open the two workbooks and no im not using a macro to open. My thought was to run the macro within MonthKPI workbook.
Haven't tried the macro it was a something I found in on MrExcel.com
Something like this maybe..Code
- Sub Test()
- Dim erngCol As Range, nxEmptyCol As Range
- Dim Fname As Variant
- Dim ws1 As Worksheet, ws2 As Worksheet
- Dim wbA As Workbook, wbB As Workbook
- Dim rngTotal As Range
- Application.ScreenUpdating = False
- ' Define this Workbook as wbA
- Set wbA = ActiveWorkbook
- ' Define working sheet in wbA. Change sheet name accordingly
- Set ws1 = wbA.Sheets("Sheet1")
- ' Search destination Workbook
- Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
- If Fname = False Then Exit Sub 'CANCEL is clicked
- ' Define opened Workbook as wbB while opening it.
- Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
- ' Define working sheet in wbB. Change sheet name accordingly
- Set ws2 = wbB.Sheets("Sheet1")
- ' Search for last column range in wbB
- Set erngCol = ws2.Range("A2").End(xlToRight)
- ' Find next empty column in ws1
- Set nxEmptyCol = ws1.Cells(2, ws1.Columns.Count).End(xlToLeft).Offset(0, 1)
- ' Copy end column range to ws1.Range("B2")
- ws2.Range(erngCol, erngCol.End(xlDown)).Copy nxEmptyCol
- wbB.Close False
- End Sub
This is just a simulation of the real problem. I need to do this from workbook to workbook because there are over 10 people inserting KPIs (shared workbooks).
I the original workbook are there probably 200-300 row with KPIs where 20 of them is mine.
Here you go. I need to copy the data from mar2021 (in data.xlsx) into the MonthKPI workbook and next month apr2021 into MonthKPI. So activate the macro every month, too transfer the newest data.
I need to copy the last value in the column every month and copy paste it into another document which shows the KPI for the current month. So if you look up the picture i need all the values from mar2021 to be copied this month and apr2021 next month.
Is it possible to make some kind of VBA code for copy and paste last column in row if the value beside it is empty (like in apr2021).
Hmm thats not right.
I have two documents with data called KPI which i need to transfer data from (transfer data1 and 2 and put it in a "total KPI" document (destination).
Then it need to look up the last value of the given row and copy it to the destination workbook if the next is empty.
I have 36 different KPI I need to put in the destination worksheet every month and the KPI values is in two seperate workbooks.
It's always the same files im transfering the data from and to.
The last comlumn is empty so it has to transfer the value from the column before the empty cell. Fx copy paste value from K8 because L8 is empty.
I would prefer option 1. It not so important but i think it would be easier to write the script (for me).
Closed excel files. Only the file with the macro in is open. But can you just open the excel files in the script and close them again?