variable in for next loop

[raptorius]

I m using checkboxes on a userform
If the checkbox is true
A target workbook must be opened and some data from a source workbook has to be copied and pasted into the target workbook

I used a for next loop to check how many checkboxes are true:

For Each Ctl In UserForm2.Controls
If TypeName(Ctl) = "CheckBox" Then
If Ctl.Value = True Then count = count + 1

End If
Next Ctl

The copy and paste options are all the same, but every target workbook uses the data from a specific row of the source workbook

So if a checkbook if true the specific workbook linked to that checkbox must be opened and the cells to copy from the source workbook must be defined too

I have this code to define the value of both the variables file to open and start of cells to copy.

If UserForm2.CheckBox1.Value = True Then
'target workbook to open
file = "name of target workbook"
‘start of cells to copy from the source workbook
rngstart = Range("specific cell e.g. G22&quot

then for every true checkbox the cut and paste actions should be taken
i can t figure this out

this is what i have now

‘how many times the loop must be run
‘count is used to get the amount of true values from the checkboxes

For i = 1 To count

‘activate the source workbook
Windows("source workbook&quot.Activate

‘data to copy from the source workbook
Range(rngstart, rngstart.End(xlToRight)).Select
Selection.Copy

‘target file to open
Workbooks.Open file

‘selecting target sheet to paste the data
Sheets("Calcul&quot.Select
Range("c1&quot.Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Application.CutCopyMode = False

‘make a graph
Application.Run "'" & file & "'!graph"
ActiveWorkbook.Save
Next i

If i select just one checkbox it works fine but if i select more i get an error
The second workbook isn t opened

I think that using this code the first target workbook is opened each time and not the next target workbook

How can i fix this?

 

[combo]

Probably excel takes wrong workbook to work with. Try to precise references:

Dim wbSource as Workbook, wbTarget as Workbook
Set wbSource=workbooks("source workbook.xls&quot

' in the loop use:
Set wbTarget=Workbooks.Open file

' copy without activating/selecting, point ranges with full path
wbSource.Range(rngstart, rngstart.End(xlToRight)).Selection.Copy
wbTarget.Sheets("Calcul&quot.Range("c1&quot.PasteSpecial Paste:=xlValues, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
wbTarget.Save

combo

 

[raptorius]

thx for your reply but that was not the solution

 

[Zathras]

I don't understand the logic. What good does it do to know that, for example, three out of 10 check boxes are checked, without recording which three they are? It looks like you are processing files 1, 2, and 3 regardless of which check boxes are checked. Your user could check 7, 8 and 9, or 1, 5 and 7 and still only files numbers 1, 2 and 3 would get processed.