Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping a whole row to another sheet
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping a whole row to another sheet
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping a whole row to another sheet
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping a whole row to another sheet
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping a whole row to another sheet
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. he 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coping a whole row to another sheet
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. he 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for coping a cell from a diffent sheet | Excel Discussion (Misc queries) | |||
WHY DOES COPING FROM SHEET TO SHEET INCREASE SIZE DRAMATICALLY? | Excel Discussion (Misc queries) | |||
Coping Contents of one Sheet into Another Workbook | Excel Programming | |||
Questions on copying from one sheet to the other and coping every other cell. | Excel Discussion (Misc queries) | |||
Coping a sheet | Excel Programming |