Need help coping a range of data from sheet 1 to the next empty cell in sheet2

[alpine7411]

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

 

[Tom Ogilvy]

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.

 

[alpine7411]

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

 

[Tom Ogilvy]

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.

(e-mail address removed)