VbA - copying different colum ranges depending on which condition is met & pasting into different sheet

ambquinn

New Member
Joined
Jun 12, 2011
Messages
4
Ok, so I would really appreciate help as this has been driving me mad for over a week. Basically I have a raw data table where a customer can choose up to 3 different courses, data is in 3 columns. But I need to be able to have a complete table where if a customer selects 3 courses, her details appear in 3 different rows. The paste destination is always the same, but the copy range is slightly different. I have got the code to duplicate rows for more than 1 selection, but I can't get it to copy the selected columns as opposed to the whole row. I have tried defining selection ranges with each if statement and arrays but I am at a complete loss so any advice would save me sanity. Code so far (such that it is, :( is below) - I had to use paste special values as there are formulae in the original table picking up from another spreadsheet, which I dont want in the final version.


VBA Code:
Private Sub CommandButton1_Click()
ScreenUpdating = False

Dim modules As Range
   Dim wb As Range
   Dim arom As Range
   Dim med As Range

    Dim Source As Worksheet
    Dim Target As Worksheet
    

  Set Source = ActiveWorkbook.Worksheets("Raw Table")
Set Target = ActiveWorkbook.Worksheets("Finished Table")

       ' Start copying to row 4 in target sheet
    j = 4
   
   For Each wb In Source.Range("K4:K1000")

        If wb = "Well being course" Then
       Source.Rows(cwb.Row).Copy
       ' to only copy A:E,K:L,S:U
               
         Target.Rows(j).PasteSpecial Paste:=xlPasteValues
           'to paste into Cols A:J
          
           j = j + 1
        End If
            Next wb
For Each arom In Source.Range("M4:M100")    'Do 1000 rows
   
      If arom = "Aromatherapy" Then

         Source.Rows(arom.Row).Copy
         ' to only copy A:E, M:N, S:U
      Target.Rows(j).PasteSpecial Paste:=xlPasteValues
           'to paste into Cols A:J
            j = j + 1
   End If
      Next arom
   
   
    For Each med In Source.Range("O4:O100")    'Do 1000 rows
   
      If med = "Meditation" Then

         Source.Rows(med.Row).Copy
           ' to only copy A:E, O:P, S:U
      Target.Rows(j).PasteSpecial Paste:=xlPasteValues
      'to paste into Cols A:J
          
            j = j + 1
   End If
      Next med
  
  
   ScreenUpdating = False


End Sub

Many thanks.
 
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub CommandButton1_Click()
   Dim Cl As Range
   Dim Source As Worksheet, Target As Worksheet
   Dim j As Long
   
   Application.ScreenUpdating = False
   Set Source = ActiveWorkbook.Worksheets("Raw Table")
   Set Target = ActiveWorkbook.Worksheets("Finished Table")
   
   ' Start copying to row 4 in target sheet
   j = 4
   
   For Each Cl In Source.Range("K4:K1000")
      If Cl.Value = "Well being course" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,K:L,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
   For Each Cl In Source.Range("M4:M1000")
      If arom = "Aromatherapy" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,M:N,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
   For Each Cl In Source.Range("O4:O1000")
      If arom = "Meditation" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,O:P,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
End Sub
 
Solution

ambquinn

New Member
Joined
Jun 12, 2011
Messages
4
Thank you Fluff - you've already got way further in a few minutes than I have in a week! It is only copying what I want for the 1st criteria and pasting into the right columns so thank you for that!!! But its finishing after the 1st run through, ie listing all the wellbeing courses but its not going onto the other 2
 

ambquinn

New Member
Joined
Jun 12, 2011
Messages
4
How about
VBA Code:
Private Sub CommandButton1_Click()
   Dim Cl As Range
   Dim Source As Worksheet, Target As Worksheet
   Dim j As Long
  
   Application.ScreenUpdating = False
   Set Source = ActiveWorkbook.Worksheets("Raw Table")
   Set Target = ActiveWorkbook.Worksheets("Finished Table")
  
   ' Start copying to row 4 in target sheet
   j = 4
  
   For Each Cl In Source.Range("K4:K1000")
      If Cl.Value = "Well being course" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,K:L,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
   For Each Cl In Source.Range("M4:M1000")
      If arom = "Aromatherapy" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,M:N,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
   For Each Cl In Source.Range("O4:O1000")
      If arom = "Meditation" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,O:P,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
End Sub
How about
VBA Code:
Private Sub CommandButton1_Click()
   Dim Cl As Range
   Dim Source As Worksheet, Target As Worksheet
   Dim j As Long
  
   Application.ScreenUpdating = False
   Set Source = ActiveWorkbook.Worksheets("Raw Table")
   Set Target = ActiveWorkbook.Worksheets("Finished Table")
  
   ' Start copying to row 4 in target sheet
   j = 4
  
   For Each Cl In Source.Range("K4:K1000")
      If Cl.Value = "Well being course" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,K:L,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
   For Each Cl In Source.Range("M4:M1000")
      If arom = "Aromatherapy" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,M:N,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
   For Each Cl In Source.Range("O4:O1000")
      If arom = "Meditation" Then
         Intersect(Cl.EntireRow, Source.Range("A:E,O:P,S:U")).Copy
         Target.Range("A" & j).PasteSpecial xlPasteValues
         j = j + 1
      End If
   Next Cl
End Sub
Sorted!!!!! It was just changing the arom reference in the other 2 to Cl - Can't thank you enough :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VbA - copying different column ranges depending on which condition is met & pasting into sheet - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

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
Top