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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
Solution
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
 
Upvote 0
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 :)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top