Excel VBA conditional copy/paste

Microsoft Excel 2003 (full product)
September 30, 2009 at 17:19:41
Specs: Windows XP
Hi there!

I'm just beginning the long road in learning VBA and already have an immediate need for some code that is quite beyond me.

I have an excel worksheet that contains thousands of rows of data in columns A thru U, and another workbook with multiple worksheets that are blank and waiting to receive data. I need to search the value in column A of the first worksheet (let's say it's valued with the words "Bird", "Cat", "Dog", etc.) - I will search for all "Birds" - and copy/paste those rows into the worksheet of the other workbook that's named "Birds" and so on, until I've copied all of the records on the first worksheet to their proper worksheets in the other workbook (that workbook may have up to a hundred or so worksheets). The one good thing is that the first spreadsheet is sorted by column A, so it might be easy to get chunks of records at a time, if I knew how.

What is the best way to do this in VBA? I have started some code, but did not post it yet because it's very sloppy right now. I tried doing the copy/paste one row at a time, then ran into trouble with the subsequent rows. Besides, doing it one row at a time seemed inefficient.

This is something that I will need to do monthly so I really appreciate any help that you can provide.

Thanks!


See More: Excel VBA conditional copy/paste

Report •


#1
September 30, 2009 at 19:22:50
Hi,

Please provide some hard examples of your data.

All the vague examples don't give sufficient to information to offer any solution.

When you say:
I tried doing the copy/paste one row at a time, then ran into trouble with the subsequent rows.
what trouble did you run into.

Why did subsequent copy/pastes not work - what error did you get. Was this a VBA copy paste or a manual paste. If it was a VBA copy paste, what code did you use.

Regards


Report •

#2
October 1, 2009 at 05:23:53
If I understand your setup correctly, you have a list of data in Sheet1 that looks something like this:

     A      B
1  Bird     1
2  Bird     2
3  Dog      1
4  Dog      2

You also have sheets named Bird and Dog

You want to copy each row that has Bird in Column A to the sheet named Bird, etc.

If that's correct, just to simplify things I would first copy the sheet with the list to the workbook with the Bird and Dog sheets and place it as Sheet1.

Then you could try this code:

Sub MoveDataToSheetName()
'Determine how many rows from Sheet1 to copy
  lastListRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
    For myItems = 1 To lastListRow
'Set Sheet name based on value in Sheet1 Column A for each list item
     shtName = Sheets(1).Range("A" & myItems)
'Determine next empty row in Sheet with the same name 
'as the value in Sheet1 Column A
     destRow = Sheets(shtName).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy current list item to proper sheet
      Rows(myItems).EntireRow.Copy _
        Destination:=Sheets(shtName).Range("A" & destRow)
'Loop
    Next
End Sub


Report •

#3
October 1, 2009 at 11:02:27
I originally asked this in his original topic on the Programming forum, but seeing as that topic was deleted, I should probably repost here.

VBA Beginner: (let's say it's valued with the words "Bird", "Cat", "Dog", etc.) - I will search for all "Birds" - and copy/paste those rows into the worksheet of the other workbook that's named "Birds"
Search for "Bird" for search for "Birds"?
Also, what links the value "Bird" to the workbook name "Birds"? Do you just tack on an "s"? What do you do with the value of "Mouse"?


Report •

Related Solutions

#4
October 1, 2009 at 17:17:37
Thank you all so much for pointing me in the right direction! Each response helped to guide me closer to finding a solution.

The response from DerbyDad03 provides a good foundation for me to build on. I think I should be able to complete it from here.

Your time and expertise are much appreciated!


Report •


Ask Question