paste into next blank column
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!!
[865 byte] By [
pippen] at [2007-12-23]
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
ADG at 2007-8-30 >
 pippen wrote: |
| 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!!
|
|
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
