hi.. i need a VBA code that can paste data column by column.. i have the code as below:
If CheckBox1.Value = True Then
Sheet7.Range("Time").Copy Destination:=Sheet4.Range("A2")
End If
If CheckBox2.Value = True Then
Sheet7.Range("Input").Copy Destination:=Sheet4.Range("B2")
End If
If CheckBox3.Value = True Then
Sheet7.Range("Output").Copy Destination:=Sheet4.Range("C2")
End If
If CheckBox4.Value = True Then
Sheet7.Range("Cycle").Copy Destination:=Sheet4.Range("D2")
End If
this is an ideal case if i click all the check boxes and as a result the values will be pasted in sheet4 side by side. but what if i just check check boxes 1 and 3, how should i paste them next to each other instead has a blank column between them
thanks!!
paste into next blank column
Hot Topic
- Combo Box Syntax Error
- For Next stops at 16
- VBA communicating with .Net without registering COM Object
- command button issue
- Data from multiple listbox(s) to one array
- Future of VBA (and VBScript)?
- Data Access Pages - FAO Chas
- cutting down this code? help?
- Office XP VBA can't find reference after saving by Office 2003
- vlookup
Microsoft ISV
- How to change part of an array value in Excel
- Runtime Error 1004, Worksheets method error
- Need help with VBA project, need it to take dates input by user
- Trying to create a global variable
- MS Office document and image writer print driver
- a big thank you!
- Trying to write a VERY VERY simple routine BUT GOT STUCK !! ::) = Brain Ache !!
- automatically saving word doc versions every 5mins
- Office XP VBA can't find reference after saving by Office 2003
- Efficent Way to manage multiple textboxes (maybe a loop?)
paste into next blank column
ThunderRock
Practically speaking, a Cell is identical to a Range consisting of one Cell.
Jassim Rahma
Here's how I'd recommend doing this, just to make it as easy as possible to maintain and debug. Assume each of your CheckBox controls has its Caption property set to be equivalent to the corresponding named range on Sheet1. Also assume the active form is named "MyForm", that the routine that processes all of this is "ProcessCheckBoxes", that you're passing the source and target Worksheet objects (in your case, Sheet7 and Sheet4), as parameters to ProcessCheckBoxes. All you need to do is this:
Private Sub ProcessCheckBoxes(srcsheet As Excel.Worksheet, trgsheet As Excel.Worksheet)
Dim tctl As MSForms.Control, tcb As MSForms.CheckBox, jj As Integer
For Each tctl In MyForm.Controls
Set tcb = tctl
If Left(tctl.Name,8) = "CheckBox" And tctl.Object Then
targsheet.Range(tcb.Caption).Copy Destination:=trgsheet.Cells(2,jj)
End If
Next tctl
End Sub
http://www.ilkon.com
Thanks ADG!!! your code works very good!! thanks!!!
FannwongCindy
kewlbuddy
Thanks! duck thing.. thanks for your time for replying me.. Thanks again!! :)
mcdvrd
Hi Pippen
Try using a variable to record the next column letter. I have put an example below which then uses a function to return the column number as a letter (there is probably an esier way to do this but the below should work
Sub mysub()
Dim x As Long
x = 0
If CheckBox1.Value = True Then
x = x + 1
Sheet7.Range("Time").Copy Destination:=Sheet4.Range(ColumnLetter(x) & "2")
End If
If CheckBox2.Value = True Then
x = x + 1
Sheet7.Range("Input").Copy Destination:=Sheet4.Range((ColumnLetter(x) & "2"))
End If
If CheckBox3.Value = True Then
x = x + 1
Sheet7.Range("Output").Copy Destination:=Sheet4.Range((ColumnLetter(x) & "2"))
End If
If CheckBox4.Value = True Then
x = x + 1
Sheet7.Range("Cycle").Copy Destination:=Sheet4.Range((ColumnLetter(x) & "2"))
End If
End Sub
Public Function ColumnLetter(ByVal ColumnNo As Long) As String
Dim x As Long
Dim y As Double
If ColumnNo > 26 Then
y = Int((ColumnNo - 1) / 26)
ColumnLetter = Chr$(64 + y)
x = ColumnNo - (Int(y) * 26)
If x = 0 Then
ColumnLetter = ColumnLetter & "Z"
Else
ColumnLetter = ColumnLetter & Chr$(64 + x)
End If
Else
ColumnLetter = Chr$(64 + ColumnNo)
End If
End Function