What would be the best way to creat a command button (on sheet "Report") that
will pull (from sheet "Report Data") only the month (colum AI) in which is
stated on sheet "Report" ActivCell F12. Insert the (whole row) results in a
new sheet at the end of the workbook or existing sheet "Monthly Report". Then
Hide colums D thru AE and AI, totaling Colums AF, AG and AH. Creating a
formual in a cell that will take the total in AH to see if it is over the
budget amount by dividing the annual budget number in sheet "Report" cell F10
by 12 then stating how much money is left for the year?

Something like this (very generic code) should work for you
Sub newone()
Dim RngColF As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngColF = Range("B1", Range("B" & Rows.count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A1")
End With
For Each i In RngColF
If i.Value = "Family" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

Obviously, change 'family' to the month you need, change the sheet names, etc.

You can experiment with this too...
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("Copy Data3").Range("B1:B10")
rw = 1
For Each cell In rng
If LCase(cell.Value) = "x" Then
Worksheets("Copy Data4").Cells(rw, "A") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

Regards,
Ryan---

--
RyGuy


"daisy2008" wrote:

> What would be the best way to creat a command button (on sheet "Report") that
> will pull (from sheet "Report Data") only the month (colum AI) in which is
> stated on sheet "Report" ActivCell F12. Insert the (whole row) results in a
> new sheet at the end of the workbook or existing sheet "Monthly Report". Then
> Hide colums D thru AE and AI, totaling Colums AF, AG and AH. Creating a
> formual in a cell that will take the total in AH to see if it is over the
> budget amount by dividing the annual budget number in sheet "Report" cell F10
> by 12 then stating how much money is left for the year?

Ryan

Thank you for trying to help me but I'm very new to code and I can not make
either one of these work. Is sheet 1 where the data is and if it finds family
in column b is it going to place it in sheet2 A1? The 2nd one i'm just
getting a subscript out of range error.

I was hoping if I asked in a clear question you would have all the info to
help me but as I'm new maybe I didn't ask right.

Maybe if you just help me move the rows I need to a sheet I can figure the
rest out?

Daisy



"ryguy7272" wrote:

> Something like this (very generic code) should work for you
> Sub newone()
> Dim RngColF As Range
> Dim i As Range
> Dim Dest As Range
> Sheets("Sheet1").Select
> Set RngColF = Range("B1", Range("B" & Rows.count).End(xlUp))
> With Sheets("Sheet2")
> Set Dest = .Range("A1")
> End With
> For Each i In RngColF
> If i.Value = "Family" Then
> i.EntireRow.Copy Dest
> Set Dest = Dest.Offset(1)
> End If
> Next i
> End Sub
>
> Obviously, change 'family' to the month you need, change the sheet names, etc.
>
> You can experiment with this too...
> Sub CopyData10()
> Dim rng As Range, cell As Range
> Dim rw As Long
> Set rng = Worksheets("Copy Data3").Range("B1:B10")
> rw = 1
> For Each cell In rng
> If LCase(cell.Value) = "x" Then
> Worksheets("Copy Data4").Cells(rw, "A") = cell.Offset(0, -1)
> rw = rw + 1
> End If
> Next
> End Sub
>
> Regards,
> Ryan---
>
> --
> RyGuy
>
>
> "daisy2008" wrote:
>
> > What would be the best way to creat a command button (on sheet "Report") that
> > will pull (from sheet "Report Data") only the month (colum AI) in which is
> > stated on sheet "Report" ActivCell F12. Insert the (whole row) results in a
> > new sheet at the end of the workbook or existing sheet "Monthly Report". Then
> > Hide colums D thru AE and AI, totaling Colums AF, AG and AH. Creating a
> > formual in a cell that will take the total in AH to see if it is over the
> > budget amount by dividing the annual budget number in sheet "Report" cell F10
> > by 12 then stating how much money is left for the year?

I changed the sheet names in this version (maybe a little easier to deal with):
Sub CopyData10()
Dim rng As Range, cell As Range
Dim rw As Long
Set rng = Worksheets("Sheet1").Range("B1:B10")
rw = 1
For Each cell In rng
If LCase(cell.Value) = "x" Then
Worksheets("Sheet2").Cells(rw, "A") = cell.Offset(0, -1)
rw = rw + 1
End If
Next
End Sub

Hit Alt+F11 and place this code into the VB Editor (this is the window that
opens). Create a command button and link this to the macro in the VBE. Then
go to sheet1 and put some stuff in Column A and put a few 'x' characters in
Column B. Any element in Column that has an 'x' in Column B will be copied
and pasted to sheet2. Does that make sense? When you get it working, change
the 'Sheet1' to 'Report Data', because this is a reference to your specific
sheet.

Look at this link:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro

Experiment a little bit; you may be amazed at what you discover.

Regards,
Ryan---

--
RyGuy


"daisy2008" wrote:

> Ryan
>
> Thank you for trying to help me but I'm very new to code and I can not make
> either one of these work. Is sheet 1 where the data is and if it finds family
> in column b is it going to place it in sheet2 A1? The 2nd one i'm just
> getting a subscript out of range error.
>
> I was hoping if I asked in a clear question you would have all the info to
> help me but as I'm new maybe I didn't ask right.
>
> Maybe if you just help me move the rows I need to a sheet I can figure the
> rest out?
>
> Daisy
>
>
>
> "ryguy7272" wrote:
>
> > Something like this (very generic code) should work for you
> > Sub newone()
> > Dim RngColF As Range
> > Dim i As Range
> > Dim Dest As Range
> > Sheets("Sheet1").Select
> > Set RngColF = Range("B1", Range("B" & Rows.count).End(xlUp))
> > With Sheets("Sheet2")
> > Set Dest = .Range("A1")
> > End With
> > For Each i In RngColF
> > If i.Value = "Family" Then
> > i.EntireRow.Copy Dest
> > Set Dest = Dest.Offset(1)
> > End If
> > Next i
> > End Sub
> >
> > Obviously, change 'family' to the month you need, change the sheet names, etc.
> >
> > You can experiment with this too...
> > Sub CopyData10()
> > Dim rng As Range, cell As Range
> > Dim rw As Long
> > Set rng = Worksheets("Copy Data3").Range("B1:B10")
> > rw = 1
> > For Each cell In rng
> > If LCase(cell.Value) = "x" Then
> > Worksheets("Copy Data4").Cells(rw, "A") = cell.Offset(0, -1)
> > rw = rw + 1
> > End If
> > Next
> > End Sub
> >
> > Regards,
> > Ryan---
> >
> > --
> > RyGuy
> >
> >
> > "daisy2008" wrote:
> >
> > > What would be the best way to creat a command button (on sheet "Report") that
> > > will pull (from sheet "Report Data") only the month (colum AI) in which is
> > > stated on sheet "Report" ActivCell F12. Insert the (whole row) results in a
> > > new sheet at the end of the workbook or existing sheet "Monthly Report". Then
> > > Hide colums D thru AE and AI, totaling Colums AF, AG and AH. Creating a
> > > formual in a cell that will take the total in AH to see if it is over the
> > > budget amount by dividing the annual budget number in sheet "Report" cell F10
> > > by 12 then stating how much money is left for the year?

I know I posted a response here last night; not seeing it now so i am
re-posting. I was not paying attention when I posted before, I think this is
what you need...

Sub CopyToNewSheet()
Sheets("Report").Select 'Change to suit
Cells.Select
Selection.ClearContents
Dim myrange, copyrange As Range
Sheets("Report Data").Select

Set myrange = Range("F1:F300")
For Each C In myrange
If C.Value <> "" Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
copyrange.Copy
Sheets("Report").Select 'Change to suit
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

With your data on the sheet named "Report Data", place a character, such as
an "x" the rows, in column F, where you want to designate that Excel needs to
look, then if not blank, copy/paste to a sheet named "Report". Hope that
works for you.

Again, if you get stuck, review the info. here:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro


Regards,
Ryan---
--
RyGuy


"ryguy7272" wrote:

> I changed the sheet names in this version (maybe a little easier to deal with):
> Sub CopyData10()
> Dim rng As Range, cell As Range
> Dim rw As Long
> Set rng = Worksheets("Sheet1").Range("B1:B10")
> rw = 1
> For Each cell In rng
> If LCase(cell.Value) = "x" Then
> Worksheets("Sheet2").Cells(rw, "A") = cell.Offset(0, -1)
> rw = rw + 1
> End If
> Next
> End Sub
>
> Hit Alt+F11 and place this code into the VB Editor (this is the window that
> opens). Create a command button and link this to the macro in the VBE. Then
> go to sheet1 and put some stuff in Column A and put a few 'x' characters in
> Column B. Any element in Column that has an 'x' in Column B will be copied
> and pasted to sheet2. Does that make sense? When you get it working, change
> the 'Sheet1' to 'Report Data', because this is a reference to your specific
> sheet.
>
> Look at this link:
> http://www.anthony-vba.kefra.com/vba...ur_First_Macro
>
> Experiment a little bit; you may be amazed at what you discover.
>
> Regards,
> Ryan---
>
> --
> RyGuy
>
>
> "daisy2008" wrote:
>
> > Ryan
> >
> > Thank you for trying to help me but I'm very new to code and I can not make
> > either one of these work. Is sheet 1 where the data is and if it finds family
> > in column b is it going to place it in sheet2 A1? The 2nd one i'm just
> > getting a subscript out of range error.
> >
> > I was hoping if I asked in a clear question you would have all the info to
> > help me but as I'm new maybe I didn't ask right.
> >
> > Maybe if you just help me move the rows I need to a sheet I can figure the
> > rest out?
> >
> > Daisy
> >
> >
> >
> > "ryguy7272" wrote:
> >
> > > Something like this (very generic code) should work for you
> > > Sub newone()
> > > Dim RngColF As Range
> > > Dim i As Range
> > > Dim Dest As Range
> > > Sheets("Sheet1").Select
> > > Set RngColF = Range("B1", Range("B" & Rows.count).End(xlUp))
> > > With Sheets("Sheet2")
> > > Set Dest = .Range("A1")
> > > End With
> > > For Each i In RngColF
> > > If i.Value = "Family" Then
> > > i.EntireRow.Copy Dest
> > > Set Dest = Dest.Offset(1)
> > > End If
> > > Next i
> > > End Sub
> > >
> > > Obviously, change 'family' to the month you need, change the sheet names, etc.
> > >
> > > You can experiment with this too...
> > > Sub CopyData10()
> > > Dim rng As Range, cell As Range
> > > Dim rw As Long
> > > Set rng = Worksheets("Copy Data3").Range("B1:B10")
> > > rw = 1
> > > For Each cell In rng
> > > If LCase(cell.Value) = "x" Then
> > > Worksheets("Copy Data4").Cells(rw, "A") = cell.Offset(0, -1)
> > > rw = rw + 1
> > > End If
> > > Next
> > > End Sub
> > >
> > > Regards,
> > > Ryan---
> > >
> > > --
> > > RyGuy
> > >
> > >
> > > "daisy2008" wrote:
> > >
> > > > What would be the best way to creat a command button (on sheet "Report") that
> > > > will pull (from sheet "Report Data") only the month (colum AI) in which is
> > > > stated on sheet "Report" ActivCell F12. Insert the (whole row) results in a
> > > > new sheet at the end of the workbook or existing sheet "Monthly Report". Then
> > > > Hide colums D thru AE and AI, totaling Colums AF, AG and AH. Creating a
> > > > formual in a cell that will take the total in AH to see if it is over the
> > > > budget amount by dividing the annual budget number in sheet "Report" cell F10
> > > > by 12 then stating how much money is left for the year?

I have worked on this for a while now and I can't it it to work what am I
doing wrong? Lets just say I have 3 workseets. sheet1 has the value to
search in sheet2 starting in row 2 column A and I want to entire row copied
to sheet3 starting in row 2. Help please

"ryguy7272" wrote:

> I know I posted a response here last night; not seeing it now so i am
> re-posting. I was not paying attention when I posted before, I think this is
> what you need...
>
> Sub CopyToNewSheet()
> Sheets("Report").Select 'Change to suit
> Cells.Select
> Selection.ClearContents
> Dim myrange, copyrange As Range
> Sheets("Report Data").Select
>
> Set myrange = Range("F1:F300")
> For Each C In myrange
> If C.Value <> "" Then
> If copyrange Is Nothing Then
> Set copyrange = C.EntireRow
> Else
> Set copyrange = Union(copyrange, C.EntireRow)
> End If
> End If
> Next
> copyrange.Copy
> Sheets("Report").Select 'Change to suit
> Cells(1, 1).Select
> Selection.PasteSpecial Paste:=xlPasteValues
> End Sub
>
> With your data on the sheet named "Report Data", place a character, such as
> an "x" the rows, in column F, where you want to designate that Excel needs to
> look, then if not blank, copy/paste to a sheet named "Report". Hope that
> works for you.
>
> Again, if you get stuck, review the info. here:
> http://www.anthony-vba.kefra.com/vba...ur_First_Macro
>
>
> Regards,
> Ryan---
> --
> RyGuy
>
>
> "ryguy7272" wrote:
>
> > I changed the sheet names in this version (maybe a little easier to deal with):
> > Sub CopyData10()
> > Dim rng As Range, cell As Range
> > Dim rw As Long
> > Set rng = Worksheets("Sheet1").Range("B1:B10")
> > rw = 1
> > For Each cell In rng
> > If LCase(cell.Value) = "x" Then
> > Worksheets("Sheet2").Cells(rw, "A") = cell.Offset(0, -1)
> > rw = rw + 1
> > End If
> > Next
> > End Sub
> >
> > Hit Alt+F11 and place this code into the VB Editor (this is the window that
> > opens). Create a command button and link this to the macro in the VBE. Then
> > go to sheet1 and put some stuff in Column A and put a few 'x' characters in
> > Column B. Any element in Column that has an 'x' in Column B will be copied
> > and pasted to sheet2. Does that make sense? When you get it working, change
> > the 'Sheet1' to 'Report Data', because this is a reference to your specific
> > sheet.
> >
> > Look at this link:
> > http://www.anthony-vba.kefra.com/vba...ur_First_Macro
> >
> > Experiment a little bit; you may be amazed at what you discover.
> >
> > Regards,
> > Ryan---
> >
> > --
> > RyGuy
> >
> >
> > "daisy2008" wrote:
> >
> > > Ryan
> > >
> > > Thank you for trying to help me but I'm very new to code and I can not make
> > > either one of these work. Is sheet 1 where the data is and if it finds family
> > > in column b is it going to place it in sheet2 A1? The 2nd one i'm just
> > > getting a subscript out of range error.
> > >
> > > I was hoping if I asked in a clear question you would have all the info to
> > > help me but as I'm new maybe I didn't ask right.
> > >
> > > Maybe if you just help me move the rows I need to a sheet I can figure the
> > > rest out?
> > >
> > > Daisy
> > >
> > >
> > >
> > > "ryguy7272" wrote:
> > >
> > > > Something like this (very generic code) should work for you
> > > > Sub newone()
> > > > Dim RngColF As Range
> > > > Dim i As Range
> > > > Dim Dest As Range
> > > > Sheets("Sheet1").Select
> > > > Set RngColF = Range("B1", Range("B" & Rows.count).End(xlUp))
> > > > With Sheets("Sheet2")
> > > > Set Dest = .Range("A1")
> > > > End With
> > > > For Each i In RngColF
> > > > If i.Value = "Family" Then
> > > > i.EntireRow.Copy Dest
> > > > Set Dest = Dest.Offset(1)
> > > > End If
> > > > Next i
> > > > End Sub
> > > >
> > > > Obviously, change 'family' to the month you need, change the sheet names, etc.
> > > >
> > > > You can experiment with this too...
> > > > Sub CopyData10()
> > > > Dim rng As Range, cell As Range
> > > > Dim rw As Long
> > > > Set rng = Worksheets("Copy Data3").Range("B1:B10")
> > > > rw = 1
> > > > For Each cell In rng
> > > > If LCase(cell.Value) = "x" Then
> > > > Worksheets("Copy Data4").Cells(rw, "A") = cell.Offset(0, -1)
> > > > rw = rw + 1
> > > > End If
> > > > Next
> > > > End Sub
> > > >
> > > > Regards,
> > > > Ryan---
> > > >
> > > > --
> > > > RyGuy
> > > >
> > > >
> > > > "daisy2008" wrote:
> > > >
> > > > > What would be the best way to creat a command button (on sheet "Report") that
> > > > > will pull (from sheet "Report Data") only the month (colum AI) in which is
> > > > > stated on sheet "Report" ActivCell F12. Insert the (whole row) results in a
> > > > > new sheet at the end of the workbook or existing sheet "Monthly Report". Then
> > > > > Hide colums D thru AE and AI, totaling Colums AF, AG and AH. Creating a
> > > > > formual in a cell that will take the total in AH to see if it is over the
> > > > > budget amount by dividing the annual budget number in sheet "Report" cell F10
> > > > > by 12 then stating how much money is left for the year?