exporting specific cell(s)/ range

[James]

right now this code exports to another sheet, and only exports the active
cell. I would like to export a given cell range in a row (A4:C4, F4,I4:Q4),
with a comfirmation based on F4 being "y" vs "n".

Sub ClickAdd()

Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean

Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40")
'Range Of Cells that needs to change'

For Each rngCell In rngAvailable
If rngCell.Value = vbNullString Then
rngCell.Value = ActiveCell.Value
bolSuccess = True
Exit For
End If
Next

If Not bolSuccess Then
MsgBox "Ran outta spaces...", 0, ""
End If


is there also a way to verify if a given name has already been exported?

 

[James]

I get a "Compile Error"; "Next without For" on

End If
Next cll <----
End If
End If
End Sub

 

[James]

tested and I no longer get an error, but the data is not exporting either...

 

[James]

Tested, no longer any errors but the data is not transfering. Tried
highlighting all the cells in the row, just F4 and just A4. No joy

 

[p45cal]

Right, tested this time (there were other problems)
[CODE
Sub ClickAdd(
Dim rngAvailable As Range, rngCell As Rang
If Range("F4") = "y" Then 'case sensitiv
Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40"
For Each cll In Range("A4:C4,I4:Q4").Cell
If Application.WorksheetFunction.CountIf(rngAvailable, cll.Value) = 0 The
If Application.WorksheetFunction.CountBlank(rngAvailable) > 0 The
rngAvailable.SpecialCells(xlCellTypeBlanks)(1) = cll.Valu
Els
MsgBox "Ran outta spaces... couldn't place " & cll.Value & " from " & cll.Address & vbLf & "Stopping.", 0, "
Exit Su
End I
End I
Next cl
End I
End Su
[/CODE
This is case sensitive for the 'y' in F4 but case insensitive for the strings being copied.

 

[James]

do i need to highlight the entire row? which cell needs to be avtive in order
for this to transfer?

 

[James]

I have tried to have cell A4 active, F4 active, the entire row highlighted,
and F4 in the same case as the code (whioch it was before). I have the "Call
ClickAdd" coded to a button on a page labeled JohnSmith, and i have a sheet
in the same workbook labeled Sheet1. Still not seeing the data transfering.
Am I missing something?

 

[James]

the sheet [John Smith] has a button at cell B35 lableed {exoprt January}
the code behind the buttons is

Private Sub CommandButton1_Click()
Call ClickAdd
End Sub

under module1 sits the code you offered that i have been testing to try to
get to work for what i am doing.

Sub ClickAdd()
Dim rngAvailable As Range, rngCell As Range
If Range("F4") = "y" Then 'case sensitive
Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("A4:A20")
For Each cll In Range("A4:C4,I4:Q4").Cells
If Application.WorksheetFunction.CountIf(rngAvailable, cll.Value) = 0 Then
If Application.WorksheetFunction.CountBlank(rngAvailable) > 0 Then
rngAvailable.SpecialCells(xlCellTypeBlanks)(1) = cll.Value
Else
MsgBox "Ran outta spaces... couldn't place " & cll.Value & " from "
& cll.Address & vbLf & "Stopping.", 0, ""
Exit Sub
End If
End If
Next cll
End If
End Sub

Fields Cells A4:Q34 have data in them, with Cell F4 as "y", where as A5:Q35
again has data, but F5 is a "n", and where as A6:Q36 is the exactly the same
as A4:Q34...

so when i click the button I do not get the data transfered. I wish there
was a way to post my sheet so i could show you what i mean, as far as what is
occuring.