Copy code for rest of the check boxes

[laxmandilip]

Hi , i have created Excel vba code for checkbox , when i click the check box its going to get the data from current sheet to another sheet .......... i want to create check boxes for entire column , is there an easy way to copy the code for each box ???? i have 200 check boxes , for each check box the range changes

Here's the code, i wrote for one check box



Private Sub CheckBox1_Click()

Dim strPath2 As String
Dim WbWorkbook1 As Workbook
Dim WbWorkbook2 As Workbook

strPath2 = "U:\F.P.EM-13 Trial Workbook.xlsx"

Set WbkWorkbook1 = ThisWorkbook
Set WbkWorkbook2 = Workbooks.Open(strPath2)

WbkWorkbook1.Worksheets("Sheet1").Range("H$7").Copy
WbkWorkbook2.Worksheets("Action List").Range("H17:J17").PasteSpecial

WbkWorkbook1.Worksheets("Sheet1").Range("G$7").Copy
WbkWorkbook2.Worksheets("Action List").Range("E7").PasteSpecial xlPasteValues

WbkWorkbook1.Worksheets("Sheet1").Range("C$7").Copy
WbkWorkbook2.Worksheets("Action List").Range("E12").PasteSpecial xlPasteValues

WbkWorkbook1.Worksheets("Sheet1").Range("E$7").Copy
WbkWorkbook2.Worksheets("Action List").Range("E14").PasteSpecial xlPasteValues

WbkWorkbook1.Worksheets("Sheet1").Range("D$7").Copy
WbkWorkbook2.Worksheets("Action List").Range("E13").PasteSpecial xlPasteValues





End Sub

 

[CountTepes]

Put your code into it's own subroutine, but instead of using direct references"$H7", "H17:J17" use either cells(ronum,colnum), so Range("H7") will become Range(Cells(7,8)). Likewise Range("H17:J17") will become Range(Cells(17,8),cells(17,10))

Now instead of using fixed values, you can change the numbers for variables.

The checkbox, calls the subroutine using

Call MyRoutine(n)

Where MyRoutine is the name of whatever you have called the routine and "n" is different for each checkbox.

Start your subroutine, like this.

Sub MyRoutine(Ident as long)

Now when a checkbox calls the routine, Ident is whatever number the checkbox passed over to it.

You can now "n" to generate the cell row and column numbers, using whatever is the relationship between CheckBoxes and ranges.

 

[laxmandilip]

 

[laxmandilip]

 

[MARK858]

WbkWorkbook1.Worksheets("Sheet1").Cells(1, 2).Copy

P.S. when posting code in future please just paste the code in the thread, select it and click the <vba/> icon to wrap it in code tags. Please don't post code as an image.

 

[laxmandilip]

for different check boxes , im changing the number , but all the check boxes are copying the same values everytime ......

 

[MARK858]

The code that I posted was purely to correct your syntax error in post number 4.
You haven't posted enough info about the relationship between the Check box number and the ranges for me to make any suggestions about the code for the multiple checkboxes.

 

[laxmandilip]

hi im so sorry , so each check box needs to copy the data from H7 to H17:J17 in different sheet, so the next check box should copy H8 toH17:J17 in different sheet and so on , for each check box the row will change

 

[NoSparks]

so you copy to the same F.P.EM-13 Trial Workbook.xlsx but to 200 different sheets?
how do you know which sheet?

 

[laxmandilip]

My destination range is the same , to the same workbook............. i will manually save the sheet to a different name ...... All i need is a easy way to copy H7toH17:J17 ....... so the next box should copy H8, third check box should copy H9, fourth check box to Copy H10 and so on ....... like i have 200 check boxes .... .200th check box needs to copy H207