Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help coping a range of data from sheet 1 to the next empty cell in sheet2
I am working on a program for excel that when finished will copy data
(with formulas) from sheet1 (I have it labled Items) to the next avaliable blank cell in sheet 2 (I have it labeled Estimate). I am using a checkbox on the items i have listed on sheet1, so that when checked, the assigned info (on sheet1) is copied into designated areas on sheet2. An example: When I select copper piping 1/2" on sheet1. I want it to go in the first empty cell under the heading "plumbing" on sheet2. When I select copper piping 3/4" next on sheet1. I want it to go under copper piping 1/2" now on sheet two. And so on. Also when I select lights on sheet1. I want it to go in the first empty cell under the heading "electrical" on sheet2. My code so far is: Sub Test3() Application.ScreenUpdating = False If Range("B7") = True Then Range("C7:G7").Select Selection.Copy Sheets("Estimate").Select Range("B16:F16").Select ActiveSheet.Paste Range("A1").Select Sheets("Items").Select Range("A2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "" Range("A3").Select End Sub I have it now where each item on sheet1 is assigned a range on sheet2. I will not use all items on sheet1 at the sametime so I want the items to paste onto sheet2 in the order I select them under there designated headings. I was thinking of using an "If" statement: If cell B15 is occupied then look in cell B16. If that is occupied look in cell B17. If not occupied then paste. I'm not sure how the code would look. I tried several things but kept getting errors. Is there a simplier way to do this? Or am I way off base? If you need more information or clarification let me know. Thanks, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help coping a range of data from sheet 1 to the next empty c
You need to loop through the checkboxes and see if they are checked.
when you hit a checked checkbox, you determine what row it is associated with, then copy that "row" of data/formulas and paste it in the next available row on your estimate sheet. At least that is what your code would indicate. You description obfuscates the problem. If rows of lighting are going to one area of the estimate and rows of pipe are going to another area of the estimate sheet, then the problem is more complex. In that case, you could have the click event of the checkbox copy the data as soon as you check the box. You could have a defined range as the destination for each checkbox. for example each pipe checkbox would go to Estimate!M10:M20 so within that range, the code would do Private Sub CheckBox1_Click() Dim rng as Range if Checkbox1.Value = True then set rng = worksheets("Estimate").Range("M10:M20") cnt = application.CountA() worksheets("Items").Range("B16:F16").copy _ Destination:=rng(cnt+1) end if Of couse there are checkboxes from the forms toolbar and checkboxes from the control toolbox toolbar. The above is for the latter. Without more knowledge of your workbook, it would be hard to give you a really good answer. -- Regards, Tom Ogilvy " wrote: I am working on a program for excel that when finished will copy data (with formulas) from sheet1 (I have it labled Items) to the next avaliable blank cell in sheet 2 (I have it labeled Estimate). I am using a checkbox on the items i have listed on sheet1, so that when checked, the assigned info (on sheet1) is copied into designated areas on sheet2. An example: When I select copper piping 1/2" on sheet1. I want it to go in the first empty cell under the heading "plumbing" on sheet2. When I select copper piping 3/4" next on sheet1. I want it to go under copper piping 1/2" now on sheet two. And so on. Also when I select lights on sheet1. I want it to go in the first empty cell under the heading "electrical" on sheet2. My code so far is: Sub Test3() Application.ScreenUpdating = False If Range("B7") = True Then Range("C7:G7").Select Selection.Copy Sheets("Estimate").Select Range("B16:F16").Select ActiveSheet.Paste Range("A1").Select Sheets("Items").Select Range("A2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "" Range("A3").Select End Sub I have it now where each item on sheet1 is assigned a range on sheet2. I will not use all items on sheet1 at the sametime so I want the items to paste onto sheet2 in the order I select them under there designated headings. I was thinking of using an "If" statement: If cell B15 is occupied then look in cell B16. If that is occupied look in cell B17. If not occupied then paste. I'm not sure how the code would look. I tried several things but kept getting errors. Is there a simplier way to do this? Or am I way off base? If you need more information or clarification let me know. Thanks, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help coping a range of data from sheet 1 to the next empty c
You were right in assuming that the rows of lighting are going to one
area of the estimate and rows of pipe are going to another area of the estimate sheet. I like the idea of defining a range in the Material sheet. I'm still a novice in VBA, so I'm probibly going to sound ignorant here. Does the code you gave me incorporate into my code or does it replace it? If it replaces it then I'm getting an "Ambiguous" error in the first line "Private Sub CheckBox1_Click() . How do I correct this? If I have to incorporate it where would it go? What am I missing here? I'm using checkboxes from the control toolbox toolbar. Thanks for your help, Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help coping a range of data from sheet 1 to the next empt
If you have an activeX/control toolbox toolbar checkbox named Checkbox1 on
your sheet, then put your sheet in design mode (with the control toolbox toolbar) and double click on it. It should take you to Private Sub CheckBox1_Click() End Sub You would replace that with my code (altered to really work with the right ranges). Mine is really just a model to give you ideas - I would need to know much more information to actually write code that you would use. If you want to send me a sample workbook with an explanation of what you want to go where if it is not obvious, perhaps I can give you an example that works. -- Regards, Tom Ogilvy " wrote: You were right in assuming that the rows of lighting are going to one area of the estimate and rows of pipe are going to another area of the estimate sheet. I like the idea of defining a range in the Material sheet. I'm still a novice in VBA, so I'm probibly going to sound ignorant here. Does the code you gave me incorporate into my code or does it replace it? If it replaces it then I'm getting an "Ambiguous" error in the first line "Private Sub CheckBox1_Click() . How do I correct this? If I have to incorporate it where would it go? What am I missing here? I'm using checkboxes from the control toolbox toolbar. Thanks for your help, Keith |
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) | |||
Pasting data top 1st empty cell in range | Excel Discussion (Misc queries) | |||
Questions on copying from one sheet to the other and coping every other cell. | Excel Discussion (Misc queries) | |||
can I transfer data from sheet 1 to sheet2? | Excel Discussion (Misc queries) | |||
Coping values to the next empty row | Excel Programming |