Coping a whole row to another sheet

[daisy2008]

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?

 

[ryguy7272]

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

 

[daisy2008]

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]

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/vbabasic1.htm#Creating_Your_First_Macro

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

Regards,
Ryan---

 

[ryguy7272]

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/vbabasic1.htm#Creating_Your_First_Macro


Regards,
Ryan---

 

[daisy2008]

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