CONSOLIDATED MI REPORT

[MoonLove]

Hi team, Iam a new joiner and I have a very urgent project to deliver.

I have 4 workbooks with the names ( Workbook 1,2,3,4). With each workbook, there are 3 common worksheets named sales, channels & products. . I managed to get VBA macro here to pull data that are in workbooks 1, 2, 3, & 4 and consolidate them into another workbook called "CONSOLIDATED MI REPORT" that have the same worksheet name as to those four workbooks I mentioned earlier.

My problem now is that, whenever I run the macro, data are pulled starting from the old top rows up to the new rows leading to duplications of information's. I want a macro that will only pull updated row data from workbook 1,2,3, & 4 then transfer the same to my consolidate MI report sheets respectively.

Please assist.

 

[MoonLove]

Dear Micron,

Remember I have consolidated workbook of which it pulls data from individual workbooks GK,RJ,SK and TB.

The code I run them on my consolidated report.

Each sheet on Individual workbooks i.e GK, RJ , SK and TB has a flag column (column F).

Please assist on solution of why the code still pull all the data even those that were previously copied workbooks from GK,RK,SK and TB.

 

[Micron]

I can't see what you have to work with nor can I keep this up after some 30 posts.
Maybe if you post 2 source workbook copies and one target wb copy somewhere I can take a look.

 

[MoonLove]

Dear friend Micron,

I really appreciate all our journey up to here but, I think you didnot understand my problem statement and that's why we are still here today.

Allow me elaborated it again:

- I have 4 source workbooks called GK, SK,RJ and TB(my source workbooks).
- Each of the above source worksheets have three worksheets called channels, products and sales.
- Each sheet in all the four workbooks(GK,SK,RJ and TB) have the same table format as below:

Date	Lead Code	Lead Name	YTD	Sales Code
08-01-2023	102	Serengeti	2023	201
08-01-2023	103	Manyara	2023	202
08-01-2023	104	Mikumi	2023	203

- My destination workbook is called CONSOLIDATED MI REPORT, it has 3 sheet with the same name as of those 4 workbooks(channels, products and sales).
- I ran the macro on the CONSOLIDATED MI REPORT standard module with the expectation that it has to pull/copy data from each sheet(channels, products, and sales) on each individual workbooks(GK,SK,RJ and TB) and paste it to CONSOLIDATED MI REPORT.
- My challenge is when the macro runs, it copies everything even the data that was previously copied from each sheet on individual workbooks GK, SK, RJ, and TB.
- I requested you assistance to have a code which just pull only those data that were not previously copied from each sheets on individual workbooks (GK,SK,RJ ant TB).
- You suggested the idea of FLAG COLUMN, where by from my understanding, each sheet on each source workbook(GK,SKRJ and TB) should have a column where if data have already been copied to CONSOLIDATED MI REPORT, then it will keep a memory of the last copied row.
- I ran the code that you have shared on my destination workbook(CONSOLIDATED MI REPORT standard module) but it just behave the same, the code pulls everything from all the four workbooks.
- My flag column is column "F", please see the below code:

Sub Copy_From_All_Workbooks()
Dim wb As String
Dim sh As Worksheet
Dim lngStartCopy As Long, Lrow As Long

'RunMacro = Now + TimeValue("00:30:00")
'Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
     If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
          For Each sh In Workbooks(wb).Worksheets
               lngStartCopy = sh.Cells(Rows.Count, "F").End(xlUp).Row + 1 'where to start copied range
               Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row 'where last row is with data in column A
              
               'if sheet is blank or flag/data is wrong, start row can be greater than end row
               If Not lngStartCopy > Lrow Then '
                    sh.Range("A" & lngStartCopy & ":A" & Lrow).EntireRow.Copy
                    ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                    sh.Range("F" & lngStartCopy & ":F" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub

 

[MoonLove]

Dear Micron,

Good day to you..

You did not come back to me on the above ask.

 

[Micron]

I asked for files and instead you blame me for this taking so long. I can tell you that others don't think that is the case. Sorry, but I am done here and will not be following any more. Good luck.

 

[MoonLove]

Hi Micron,

I have restrictions to install XL2BB as per the organization policy, so I can not upload the excel file and that's why I decided to explain my issue as per above.

Indeed I really need your support.