Solved Excel Macro for conditional Copy/Paste for whole rows

October 22, 2015 at 02:40:35
Specs: Windows 7
Hi everyone!
So I was wondering if there was a way to do the following.

Saying I want to make monthly reports for a country (Italy) and two product categories (Juice & Water) but the worksheet containing the data is massive and I have to do that every week. The rows look something as following:

Country - Product - Otherstuff

Would there be a macro in the way "If Italy and Juice" cut paste whole row in a different workbook? Cheers!

message edited by Odysseus


See More: Excel Macro for conditional Copy/Paste for whole rows

Report •


✔ Best Answer
October 27, 2015 at 07:15:04
Since you still haven't told me how you want to pass the country names to the macro (even though I've asked twice) I once again have make an assumption.

This code will work for the criteria that you posted in Response #8 since I assume that you want the country names hard coded into the macro.

The code creates an Array with the county names that you provided and then loops through the array searching for each element.

(Note: This code still copies the rows to Sheet2 of the same workbook. I'll leave it up to you to modify it so that it copies the rows to your other workbook. Practice is good.)

Option Explicit
Sub CopyData()
Dim c As Range
Dim firstAddress As String
Dim nxtRw As Long
Dim cNum As Integer
Dim Country_Arr() As Variant

'Build array of Country names
  Country_Arr = Array("Italy", "Slovakia", "Switzerland")

'Loop through Country names Array
 For cNum = 0 To 2
 
'Search Sheet 1 Column A for array elements.
  With Sheets(1).Columns(1)
   Set c = .Find(Country_Arr(cNum), LookIn:=xlValues)
   
'If found, Check Sheet 1 Column B for Juice or Water
    If Not c Is Nothing Then
      firstAddress = c.Address
       Do
         If c.Offset(0, 1) = "Juice" Or _
            c.Offset(0, 1) = "Water" Then
            
'If True, determine next empty Row in Sheet2, copy Row Values from Sheet1
              nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
              c.EntireRow.Copy
              Sheets(2).Range("A" & nxtRw).PasteSpecial Paste:=xlValues
         End If
         
'Search for next occurrence of array element
             Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 22, 2015 at 10:00:00
Yes, it's possible to do what you want, but I assume that there is more to your requirements than just moving rows containing "Italy and Juice" to a new worksheet.

What exactly are you trying to do?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 22, 2015 at 23:32:10
Hi DerbyDad and thanks for your willingness to help!

So the things to keep in mind are: I want to report the Juice and Water results of 10 countries on a separate workbook, while the workbook I take the data from has 70 countries with 7 different products each (have to do this every week so automating it would be quite convenient).

So I basically only want to cut paste rows containing "country" and "juice" or "water" - no other assumptions.

That means ill have to do around 10 macros 1 for every country if im not wrong. Just need the first one to start with :D


Report •

#3
October 23, 2015 at 07:05:11
The problem with creating code based on the limited details that you have provided is that we have no idea what your data layout looks like. The layout of your data will determine how the code is written.

As an example, here are 2 different data layouts. The code to find rows containing your requested data ("country" and "juice" or "water") would be completely different because the data layouts are completely different.

In this example, all of the "drinks" are in one column, so the code would only have to look in Column B to find the specified drinks.

       A                B                C
1   Country          Product 1        Product 2
2    Italy            Milk             Beef
3    France           Juice            Ham
4    Germany          Water            Ham
5    Italy            Juice	       Ham
6    France           Milk             Beef

In this example, the drinks can be found in both Column B and Column C, so the code has to search more than one column to find the specified drinks.

       A                B                C
1   Country          Product 1        Product 2
2    Italy           Milk              Beef
3    France          Ham               Juice
4    Germany         Water             Ham
5    Italy           Ham 	       Juice
6    France          Milk              Beef

It would be a waste of our time to write code without more detail. At best, the code would be more complicated than it needs to be as we try to address every possible data layout we could imagine. At worst, we wouldn't even be close because we never imagined the actual layout that you are using.

We also have no idea how you will specify the criteria that the code will search for. Will be entered in cells? Will be entered via InputBoxes? Will it be hardcoded into the actual macros?

If you want our help, you are going to have to more specific in your description of your requirements and data layout.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 23, 2015 at 07:21:07
It looks something like this if that helps (sorry for the table importing skills), let me know if it doesnt make sense.


Italy Juice 4 2
Italy Beef 4 4
Italy Water 3 5
Italy Milk 2 1
Slovakia Juice 3 2
Slovakia Beef 4 5
Slovakia Water 1 2
Slovakia Milk 2 4
Hungary Juice 3 4
Hungary Beef 5 3
Hungary Water 3 3
Hungary Milk 1 2
Switzerland Juice 2 1
Switzerland Beef 3 4
Switzerland Water 4 5
Switzerland Milk 2 5

message edited by Odysseus


Report •

#5
October 23, 2015 at 09:20:49
There is a link at the bottom of my posts that will take you to the instructions for posting example data in this forum. In this case, the example data that was supplied is incomplete.

Without Column letters and Row numbers, we have to make assumptions related to where the data resides. When we make assumptions, we spend time writing code that may not work when applied to the actual data.

Since you mentioned that you "Just need the first one to start with", I am assuming that means that you know how to write VBA code and can make the changes required. Based on that, I am willing to make the following simple assumption and offer some code:

Assumption: Your data resides in Sheet1, Columns A:D.

Based on that assumption, this code should copy the first and third row of your data from Sheet1 to Sheet2.

Option Explicit
Sub CopyData()
Dim c As Range
Dim firstAddress As String
Dim nxtRw As Long
'Search Sheet 1 Column A for Italy.
  With Sheets(1).Columns(1)
   Set c = .Find("Italy")
'If found, Check Sheet 1 Column B for Juice or Water
    If Not c Is Nothing Then
      firstAddress = c.Address
       Do
         If c.Offset(0, 1) = "Juice" Or _
            c.Offset(0, 1) = "Water" Then
'If True, determine next empty Row in Sheet2, copy Row from Sheet1
              nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
              c.EntireRow.Copy Sheets(2).Range("A" & nxtRw)
         End If
'Search for next occurrence of Italy
             Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 26, 2015 at 08:21:03
Hi DerbyDad!

I tried your code on raw data and it perfectly works, thanks a lot for that! There are however a few adjustments that I am not sure how to employ since Im quite new to VBA.

1. The code doesn't dedect the name of the country "Italy", if it is written as a formula-copied from another cell.(Saying the cell I am working on is (=A56) where (A56= Italy) Is there a way to go around that?

2. After writing the country names in raw the code worked again, but they are pasted as 0. How could I change it in order for the numbers to be pasted in values?

3. Saying I would like to add more countries than just "Italy", could you show me an example of how to do that?

Thank you in advance, here is the code I am using at the moment.

Sub CopyData()
Dim c As Range
Dim firstAddress As String
Dim nxtRw As Long

'Search Sheet 1 Column A for Italy.
  With Workbooks("Test1").Sheets(1).Columns(1)
   Set c = .Find("Italy")

'If found, Check Sheet 1 Column B for Juice or Water
    If Not c Is Nothing Then
      firstAddress = c.Address
       Do
         If c.Offset(0, 1) = "Water" Or _
            c.Offset(0, 1) = "Juice" Then

'If True, determine next empty Row in Sheet2, copy Row from Sheet1
              nxtRw = Workbooks("TESTIN").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1
              c.EntireRow.PasteSpecial.vACopy Sheets(1).Range("A" & nxtRw)
         End If

'Search for next occurrence of Italy
             Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
End Sub


Report •

#7
October 26, 2015 at 10:01:55
Please take the following in the spirit it is intended:

Do you now see why I kept asking for more details related to your requirements?

VBA code can't "guess" at anything. It has to be very specific to the actual layout of the worksheet as well as specific to the process that the user has set up.

Earlier you said:

"So I basically only want to cut paste rows containing "country" and "juice" or "water" - no other assumptions."

We now know that there were assumptions that had to be made and that those assumptions were wrong.

For example, you posted example data that led us to assume that you had country names in a column. You don't. You have formulas in a column. As far as VBA is concerned, those are 2 very different things. Had you mentioned that earlier, I would have offered different code.

You also said:

"Would there be a macro in the way "If Italy and Juice" cut paste whole row in a different workbook? "

In reality, that's not what you want. What you want is the values from the row to be pasted. Had you mentioned that earlier, I would have offered different code.

I asked:

"We also have no idea how you will specify the criteria that the code will search for. Will be entered in cells? Will be entered via InputBoxes? Will it be hard coded into the actual macros?"

You never answered that question, however you are now asking "3. Saying I would like to add more countries than just "Italy", could you show me an example of how to do that?"

No, I cannot offer an example until you answer my original question:

How do you plan to tell the code what countries you want to search for? InputBoxes? Values in cells? Hardcoded in the macro? Something else?

Do you mean more countries need to be copied to the same sheet, e.g. Italy and Slovakia should both be copied to Workbooks("TESTIN").Sheets(1) or are they to be copied to different sheets? It's all about the details, my friend, the details.

I don't mean to sound harsh - although I'm sure that I do - but I didn't ask for more details way back in my very first response for no reason.

Without the exact details related to your layout and process, I'm just wasting my time - and yours - writing code that doesn't fit your needs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 27, 2015 at 01:49:49

I understand and sorry for the hassle! As I said im quite new to VBA and I didn't see some things coming, thanks for the clarifications.

1.To answer your original question: Search the countries according to their cell values - assuming that works if the country names are entered into the cell as formulas - and also cut paste whole rows as values.

2. Yes, I mean more countries to be copied from Workbooks("Test1").Sheets(1) to Workbooks("TESTIN").Sheets(1) - same criteria. From the dataset I have sent you that means I want only Water and Juice - saying for Italy, Slovakia and Switzerland.

Once more thank you for your patience and sorry for not seeing that coming! You have already taught me a lot and cheers for that!


Report •

#9
October 27, 2015 at 05:04:38
I'll work on something. In the meantime, it might help if you reviewed this tutorial:

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
October 27, 2015 at 07:15:04
✔ Best Answer
Since you still haven't told me how you want to pass the country names to the macro (even though I've asked twice) I once again have make an assumption.

This code will work for the criteria that you posted in Response #8 since I assume that you want the country names hard coded into the macro.

The code creates an Array with the county names that you provided and then loops through the array searching for each element.

(Note: This code still copies the rows to Sheet2 of the same workbook. I'll leave it up to you to modify it so that it copies the rows to your other workbook. Practice is good.)

Option Explicit
Sub CopyData()
Dim c As Range
Dim firstAddress As String
Dim nxtRw As Long
Dim cNum As Integer
Dim Country_Arr() As Variant

'Build array of Country names
  Country_Arr = Array("Italy", "Slovakia", "Switzerland")

'Loop through Country names Array
 For cNum = 0 To 2
 
'Search Sheet 1 Column A for array elements.
  With Sheets(1).Columns(1)
   Set c = .Find(Country_Arr(cNum), LookIn:=xlValues)
   
'If found, Check Sheet 1 Column B for Juice or Water
    If Not c Is Nothing Then
      firstAddress = c.Address
       Do
         If c.Offset(0, 1) = "Juice" Or _
            c.Offset(0, 1) = "Water" Then
            
'If True, determine next empty Row in Sheet2, copy Row Values from Sheet1
              nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
              c.EntireRow.Copy
              Sheets(2).Range("A" & nxtRw).PasteSpecial Paste:=xlValues
         End If
         
'Search for next occurrence of array element
             Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
December 23, 2015 at 09:07:44
How hard is it to have the rows paste in the first blank cell below A1 which is not necessarily the last used cell in column A since I have some blank cells in between data (a Totals row.) Thank you!

Report •

#12
December 28, 2015 at 09:12:15
You could try replacing this line:

              nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1

With this:

              nxtRw = Sheets(2).Range("A2").End(xlDown).Row + 1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question