Relatively new to working with Macro's, but hoping someone would be able to give a hand with this one.
I've been stuck on this one for some time, and hoping someone might be able to help. Currently, I have a number of if statements that validate the selection of a dropdown box to copy contents from one sheet to another in a unique location depending on the selection. Here are examples of 2 statements:
Code
Static cnt As Integer
cnt = 0
If ComboBox1 = "C1" Then
Sheets("My Team").Select
If IsEmpty(Range("D5").Cells) = True Then
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 5).Select
ActiveCell.Resize(, 2).Copy
Sheets("My Team").Select
Range("D5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 8).Select
ActiveCell.Resize(, 6).Copy
Sheets("My Team").Select
Range("H5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 24).Copy
Sheets("My Team").Select
Range("Z5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
cnt = Range("C28").Value
cnt = cnt + 1
Range("C5").Value = cnt
Range("C28").Value = cnt
If ComboBox1 = "C2" Then
Sheets("My Team").Select
If IsEmpty(Range("D6").Cells) = True Then
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 5).Select
ActiveCell.Resize(, 2).Select
Selection.Copy
Sheets("My Team").Select
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 8).Select
ActiveCell.Resize(, 6).Select
Selection.Copy
Sheets("My Team").Select
Range("H6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 24).Select
Selection.Copy
Sheets("My Team").Select
Range("Z6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
cnt = Range("C28").Value
cnt = cnt + 1
Range("C7").Value = cnt
Range("C28").Value = cnt
There are a number of statements like this (inefficient), however I have not had luck trying to loop through the dropdown and successfully paste to the correct unique locations. Here's where i've gotten to:
Code
Static cnt As Integer
Dim rdata As Range
Dim c As Range
Dim i As Integer
i = 5
cnt = 0
Set rdata = Sheets("My Team").Range("$F$5:$F$27")
For Each c In rdata.Cells
If ComboBox1 = c Then
Sheets("My Team").Select
If IsEmpty(Range("D" & i).Cells) = True Then
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 5).Select
ActiveCell.Resize(, 2).Copy
Sheets("My Team").Select
Range("D" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 8).Select
ActiveCell.Resize(, 6).Copy
Sheets("My Team").Select
Range("H" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Draft Guide").Select
Cells(Application.ActiveCell.Row, 24).Copy
Sheets("My Team").Select
Range("Z" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
cnt = Range("C28").Value
cnt = cnt + 1
Range("C" & i).Value = cnt
Range("C28").Value = cnt
End If
End If
i = i + 1
Next