Sheet automatically condenses data??
 

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in between.
Is this possible??

Sheet automatically condenses data??
 

Without knowing the layout of your two sheets you may be able to adapt the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X" or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Off set(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub
"erikeve" wrote:

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in between.
Is this possible??

Sheet automatically condenses data??
 

go to the destination sheet,

select column A

Do Edit=Goto=Special and select blank Cells

Hit delete and select entire row.

--
Regards,
Tom Ogilvy


"erikeve" wrote in message
...
I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in
between.
Is this possible??

Sheet automatically condenses data??
 

Thanks
This might be a stupid question, but how do I create a button on a sheet??


"gocush" wrote:

Without knowing the layout of your two sheets you may be able to adapt the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X" or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Off set(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub
"erikeve" wrote:

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in between.
Is this possible??

Sheet automatically condenses data??
 

view = toolbars, select the forms toolbar. Then click on the button icon
on the forms toolbar and

drag a button to your sheet.

assign the macro to it.

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
Without knowing the layout of your two sheets you may be able to adapt the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X"
or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after
completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Off set(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub
"erikeve" wrote:

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in
between.
Is this possible??

Sheet automatically condenses data??
 

On the desired worksheet click: ViewToolbarsForms
This will display a toolbar menu with several icons.
Click on the one that looks like a small grey button and drag it to
someplace on your worksheet. Select a corner to resize it . Start typing and
the Caption word will be replaced with your caption. Right click and select
Assign macro to assign your (previously made macro) to this button, or click
on New to make a new macro which will be assigned to this button and will
fire off each time this button is clicked.

"erikeve" wrote:

Thanks
This might be a stupid question, but how do I create a button on a sheet??


"gocush" wrote:

Without knowing the layout of your two sheets you may be able to adapt the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X" or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Off set(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub
"erikeve" wrote:

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in between.
Is this possible??

Sheet automatically condenses data??
 

Nevermind,
It was a stupid question.
Same place as I got the checkboxes.

"erikeve" wrote:

Thanks
This might be a stupid question, but how do I create a button on a sheet??


"gocush" wrote:

Without knowing the layout of your two sheets you may be able to adapt the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X" or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Off set(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub
"erikeve" wrote:

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in between.
Is this possible??

Sheet automatically condenses data??
 

This doesn't seem to be working.
Doesn't this just move the item to the same spot as the check box??
I'm already doing that.
I need to get the sheet to condense all of the checked items so that there
are no blank rows.

"gocush" wrote:

Without knowing the layout of your two sheets you may be able to adapt the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X" or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Off set(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub
"erikeve" wrote:

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in between.
Is this possible??

Sheet automatically condenses data??
 

It should do what you ask.

--
Regards,
Tom Ogilvy

"erikeve" wrote in message
...
This doesn't seem to be working.
Doesn't this just move the item to the same spot as the check box??
I'm already doing that.
I need to get the sheet to condense all of the checked items so that there
are no blank rows.

"gocush" wrote:

Without knowing the layout of your two sheets you may be able to adapt
the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X"
or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after
completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Off set(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub
"erikeve" wrote:

I want items that are brought over from a checklist on another sheet
to
automatically condense togethor so that there are no blank rows in
between.
Is this possible??