Sheet automatically condenses data??

[erikeve]

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

[gocush]

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).Offset(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??

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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??

[erikeve]

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).Offset(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??

[gocush]

On the desired worksheet click: View>Toolbars>Forms
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).Offset(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??

[erikeve]

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).Offset(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??

[Tom Ogilvy]

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" <[email protected]/delete> wrote in message
news:[email protected]...
> 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).Offset(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??

[erikeve]

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).Offset(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??

[Tom Ogilvy]

It should do what you ask.

--
Regards,
Tom Ogilvy

"erikeve" <[email protected]> wrote in message
news:[email protected]...
> 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).Offset(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??