CODEWITHSUNDEEP
vbaexcelexcel-2010
Dugaet
Dugaet

Reputation: 15

VBA loop to paste value in another sheet

I hope one of you could help me resolving this issue:

In Sheet1, I have a combobox with names from "Sem1" to "Sem52". These names also correspond to sheet names (so "Sem1", "Sem2",...). According to the text selected in the combobox, I would like to paste value from Sheet1 to the related sheet. I let you hereunder my drafted code and the idea would be to avoid repeating my IF conditions 52 times ! I guess I need to set up a new variable (e.g. Dim i as integer, for i = 1 to 52) but I don't really know how to make it.

Thanks for your help

Dim cmbx As ComboBox
Set cmbx = Sheets("Sheet1").ComboBox1

If cmbx.Text = "Sem1" Then

    Sheets("Sheet1").Select
    Range("c41:c42").Select
    Selection.Copy
    Sheets("Sem1").Select
    Range("p34").Select
    Selection.PasteSpecial Paste:=xlPasteValues

Upvotes: 0

Views: 1509

Answers (3)

Jason Faulkner
Jason Faulkner

Reputation: 6558

You can just drive the sheet you are working with based on the selected text:

Dim cmbx As ComboBox
Set cmbx = Sheets("Sheet1").ComboBox1

Sheets("Sheet1").Range("c41:c42").Copy
Sheets(cmbx.Text).Range("p34").PasteSpecial Paste:=xlPasteValues

Since the options available in your combobox all match the names of the respective sheet, the appropriate sheet is selected (which matches the name of the user selection) and then manipulated.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

You can PasteSpecial in many sheets without a loop. Here is an example for 2 destinations:

Sub dural()
    Sheets("Sheet1").Range("C40:C41").Copy
    Sheets(Array("Sem1", "Sem2")).Select
    Range("P34").Select
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Upvotes: 0

Steve Tauber
Steve Tauber

Reputation: 10159

You can use a for loop:

http://www.excel-easy.com/vba/loop.html

Dim i As Integer

For i = 1 To 6
    If cmbx.Text = ("Sem" && i) Then
       ...
Next i

Upvotes: 0

Related Questions