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?

 

[p45cal]

Does this do it? (untested):

Code
-------------------

Sub ClickAdd()
Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean
If Range("F4") = "y" Then
Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40")
For Each cll In Range("A4:C4,I4:Q4").Cells
If Application.WorksheetFunction.CountIf(rngAvailable, cll.Value) = 0 Then
For Each rngCell In rngAvailable
If rngCell.Value = vbNullString Then
rngCell.Value = cll.Value
bolSuccess = True
Exit For
End If
Next
If Not bolSuccess Then
MsgBox "Ran outta spaces... couln't place " & cll.Value & " from " & cll.Address, 0, ""
End If
Next cll
End If
End If
End Sub

 

[James]

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

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

 

[p45cal]

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

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

Try:

Code:
--------------------

Sub ClickAddpd()
Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean
If Range("F4") = "y" Then
Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40")
For Each cll In Range("A4:C4,I4:Q4").Cells
If Application.WorksheetFunction.CountIf(rngAvailable, cll.Value) = 0 Then
For Each rngCell In rngAvailable
If rngCell.Value = vbNullString Then
rngCell.Value = cll.Value
bolSuccess = True
Exit For
End If
Next rngCell
End If
If Not bolSuccess Then
MsgBox "Ran outta spaces... couldn't place " & cll.Value & " from " & cll.Address, 0, ""
End If
Next cll
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]

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

Did you tweak/correct the name of the sub:
Sub ClickAddpd()
to
Sub ClickAdd()

 

[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?

 

[p45cal]

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

The only thing which needs to be active is the sheet which has the
range A4:C4,I4:Q4 on. It doesn't matter what cells are selected.

It looks as if the code isn't even running. To test this, temporarily
add a line:
Stop
as the first line after 'Sub ClickAdd()'
Then try again.
If it's running, it should stop at that line with a yellow highlight in
the VBE.
You can then step through the code with F8 or let it continue running
unfettered with F5. If you step through the code you can make sure that
the code is following the path you'd expect it to.
Once confirmed that it's at least being called, you should be able to
remove the Stop line again.
If this doesn't happen, then you're not calling the routine at all and
you need to adjust how you're calling it.
In any event, you should be able to call it from the sheet with
Tools|Macros etc.

Of course, if the button is on a sheet called John Smith (or is it the
button labelled John Smith?), and that sheet doesn't have the data on it
in that range A4:C4,I4:Q4, then when you press the button, John Smith is
the active sheet at that point, so nothing is going to be transferred.
Naturally, if you want data to be transferred from or to inactive sheet
that can be tweaked in the code.

Also, what is the name of the code module that you've put this code in?

 

[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.

 

[p45cal]

Well, that seems in order but re:

temporarily add a line:
Stop
as the first line after 'Sub ClickAdd()'
Then try again. What happened?
I wish there was a way to post my sheet so i could show you what i mean
as far as what is occuring.

Thecodecage.com, where I am posting, will allow attachments, but yo
have to register (and you may have to post a few messages before i
allows you to attach anything). You could PM me there (Private Message
if you register. Otherwise if you leave an email address that you don'
mind showing to the world, I'll use it to contact you directly. I won'
be around today, but will look at things tomorrow.
'Thread here.' (http://tinyurl.com/yaamczd