exporting specific cell(s)/ range

  • Thread starter Thread starter James
  • Start date Start date
J

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?
 
I get a "Compile Error"; "Next without For" on

End If
Next cll <----
End If
End If
End Sub
 
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
 
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.
 
do i need to highlight the entire row? which cell needs to be avtive in order
for this to transfer?
 
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?
 
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.
 
Back
Top