CONSOLIDATED MI REPORT

MoonLove

New Member
Joined
Dec 31, 2022
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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.
 
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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:

VBA 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
 
Upvote 0
Dear Micron,

Good day to you..

You did not come back to me on the above ask.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top