2

Can anyone recommend a good video for me about this Excel VBA problem

I have a Master workbook with a few thousand rows. Based on the value in Column 2, I need to copy each row to one of 10 different workbooks.

So far I have found only this video: http://www.familycomputerclub.com/copy-data-to-another-workbook-based-on-sales-date.html

It has been helpful but it has not enabled me to get all the way there.

Can anyone recommend a good video that will walk through this with me. I am looking for a video so I understand what I am doing, rather than just copying and pasting code.

Thank you!

Here is what I have so far. The issue is that with the below code, for every row that needs to be copy and pasted... the new copy-to workbook opens, saves, and closes. So if there are 30 rows in the Master workbook that meet the criteria to be copied into the copy-to workbook... with each row it is

  1. copy row from master workbook
  2. open copy-to workbook
  3. paste in copy-to workbook
  4. save copy-to workbook
  5. close copy-to workbook

Code:

Sub updateAllWorkbooks()

a = Worksheets("All").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

    If Worksheets("All").Cells(i, 2).Value = "DENVER" Then

        Worksheets("All").Rows(i).Copy
        Workbooks.Open Filename:="FILE NAME IS PASTED HERE"
        Worksheets("DENVER").Activate
        b = Worksheets("DENVER").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("DENVER").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        Application.CutCopyMode = False
        Worksheets("All").Activate

    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("All").Cells(1, 1).Select

End Sub
Vladimir Vagaytsev
  • 2,871
  • 9
  • 33
  • 36
Elisa Nicole
  • 21
  • 2
  • 1
    Will this be a recurring task or something you need to do only once? – cybernetic.nomad Oct 29 '18 at 20:53
  • This will be re-occuring. The Master workbook gets updated throughout the day. And at the end of every day the 10 different workbooks will need to be updated based on those master workbook changes. Thank you! – Elisa Nicole Oct 29 '18 at 21:01
  • 1
    I commend your desire to find a video that *teaches* you, rather than just giving you code to copy/paste - a great first step! Unfortunately, this question is a little off-topic for SO. This site is for programming specific questions ("help me with my code"), where you post some code and what error/issue you're having. That being said, you will want to search for things like "VBA copy data to separate workbooks", "vba find full row based on match" will get you started. – BruceWayne Oct 29 '18 at 21:09
  • 1
    Thanks for clarifying. I see. I do have the below code in Excel. This is just to copy rows with one condition into one workbook. (Thought I would start small). – Elisa Nicole Oct 29 '18 at 21:16
  • 1
    @ElisaNicole - Oh, great! Please edit your original post to include the code you do have, and any comments (do you get errors, does it work, but unexpected results, etc.)? (Format the code with the code tags, `{}`). – BruceWayne Oct 29 '18 at 21:18
  • In that case this should help https://stackoverflow.com/questions/21074874/vba-copy-rows-that-meet-criteria-to-another-sheet – Marcucciboy2 Oct 29 '18 at 21:19
  • 1
    @BruceWayne I just did thank you. Sorry I didn't realize I couldn't add it all in the comment. First time posting a question on here! – Elisa Nicole Oct 29 '18 at 21:23
  • Thanks @Marcucciboy2 ! I was actually able to execute code for copying and pasting between sheets just fine. I am now trying to rework it (slowly) to copy and paste between workbooks and this is where I am running into challenges. – Elisa Nicole Oct 29 '18 at 21:28
  • Oh okay you’ll possibly want to create workbook objects for each of the 10 that you want to paste to/from and then set those objects using workbooks.open. Then you can just say like `workbookA.sheets("sheet").Rows(...).value = workbookB.sheets("sheet").Rows(...).value` – Marcucciboy2 Oct 29 '18 at 21:46
  • I will look into this thank you @Marcucciboy2 – Elisa Nicole Oct 29 '18 at 21:58
  • 1
    @Marcucciboy2 This worked, thank you! – Elisa Nicole Oct 29 '18 at 22:54

0 Answers0