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.

 

[Micron]

By definition, "updated" data would mean that if I change A5 cell from 25 to 30 you need to pull that change in. I'd say the only way to achieve that is to wipe the target sheet clean and re-populate it from all the rows in the source.
If you mean new rows that were not copied in a prior operation, IMO you need a column with a flag (anything that id's the row has having been copied already). Then your current modified code would find the last row with the flag value (e.g row 50) and the last row with data that you intend to copy (e.g. row 100). You'd then copy from 50+1 to 100.

 

[MoonLove]

By definition, "updated" data would mean that if I change A5 cell from 25 to 30 you need to pull that change in. I'd say the only way to achieve that is to wipe the target sheet clean and re-populate it from all the rows in the source.
If you mean new rows that were not copied in a prior operation, IMO you need a column with a flag (anything that id's the row has having been copied already). Then your current modified code would find the last row with the flag value (e.g row 50) and the last row with data that you intend to copy (e.g. row 100). You'd then copy from 50+1 to 100.

Hi Micron,

Thank you so much for you quick response:

From you response, this is basically what I mean:

"If you mean new rows that were not copied in a prior operation, IMO you need a column with a flag (anything that id's the row has having been copied already). Then your current modified code would find the last row with the flag value (e.g row 50) and the last row with data that you intend to copy (e.g. row 100). You'd then copy from 50+1 to 100."

Please find the current VBA script that Iam using and help me how to modify to achieve the above:

Option Explicit
Dim RunMacro As Date

Sub Copy_From_All_Workbooks()
RunMacro = Now + TimeValue("00:30:00")
Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Dim wb As String, i As Long, sh As Worksheet
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
sh.UsedRange.Offset(1).Copy '<---- Assumes 1 header row
ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Next sh
Workbooks(wb).Close False
End If
wb = Dir
Loop
Application.ScreenUpdating = True
End Sub
Sub stop_macro()

Application.OnTime Earliesttime:=RunMacro, Procedure:="Copy_From_All_Workbooks", Schedule:=False

End Sub

 

[Micron]

This is more or less how you should post code (use code tags - vba icon on posting toolbar) to maintain indentation and readability. Given that it's New Years Eve, I may not be looking at this tonight. We'll see.

Option Explicit
Dim RunMacro As Date

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

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
         sh.UsedRange.Offset(1).Copy '<---- Assumes 1 header row
         ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
         Application.CutCopyMode = False
      Next sh
      Workbooks(wb).Close False
   End If
   wb = Dir
Loop

Application.ScreenUpdating = True

End Sub

Sub stop_macro()
Application.OnTime Earliesttime:=RunMacro, Procedure:="Copy_From_All_Workbooks", Schedule:=False
End Sub

 

[MoonLove]

This is more or less how you should post code (use code tags - vba icon on posting toolbar) to maintain indentation and readability. Given that it's New Years Eve, I may not be looking at this tonight. We'll see.

Option Explicit
Dim RunMacro As Date

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

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
         sh.UsedRange.Offset(1).Copy '<---- Assumes 1 header row
         ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
         Application.CutCopyMode = False
      Next sh
      Workbooks(wb).Close False
   End If
   wb = Dir
Loop

Application.ScreenUpdating = True

End Sub

Sub stop_macro()
Application.OnTime Earliesttime:=RunMacro, Procedure:="Copy_From_All_Workbooks", Schedule:=False
End Sub

Happy New Year Micron ….. I really appreciate

Bear with me since Iam a new joiner..

 

[Micron]

NP! Another tip if you like - don't quote a whole post that contains lots of code. As you can see, it just repeats a lot of unnecessary stuff.
Options:
- just type in a new post window and use "Post reply" button to post your response, or
- copy what you want to quote, click quote button on posting toolbar and paste within the resulting quote tags, or
use +Quote like you did and strip out the code and any other unwanted stuff. That might not work well if you accidentally remove tags.

Last tip - make sure you answer the question as best you can. I still have no idea what "updated row data" means to you. Copying and pasting my musings about it without addressing it doesn't help. Nor does your code provide any clue as to that definition. Nor do I know if you agree and will create the flag column, or which column it would be, or if the cell value in each row would be the same or even if that would matter (that's 4 points, not 1). So for me, there are at least 5 unknowns.

Answering a bunch of unexpected questions is the price one pays for free help. Usually the cause is that the one with the issue doesn't know the depth of a particular problem or issue and its solution. Sometimes it's about wondering if there is a better approach.
Not trying to nitpick here, just passing on tips for a newcomer to posting (if that's what you meant). It will make your posts more inviting to responders.

Happy New Year to you as well!

 

[MoonLove]

Thank you for your response Micron:

I want my script to work as follows:

• It should go and copy only rows that were not previously copied and transfer them to consolidated mi report sheets.
• Since for each sheet in those four workbooks(1,2,3,4), the first column is a date, I would also like the code to take the row with the most current date.
• For the case of the creating a column with a flag (a column that will show that data has already been copied), I am still a learner in VBA, can you please assist.

 

[MoonLove]

Hi Micron,

Your assistance on the above issue please.

 

[Micron]

Looks like I can't send you a PM? Maybe you don't have enough posts to get pm's. Trying to work out something today.

 

[Micron]

Try the posted code below. I had to swap in/out paths and wb names since I don't have yours. As a result I only tested once. Since you didn't indicate which column would contain the flag, I chose H and chose to insert the date, which I thought would be the most useful indicator. I also assumed that column A would be the column that would suitable for choosing the the rows for which the data had not yet been copied. HTH.

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, "H").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("H" & lngStartCopy & ":H" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub