Excel VBA - Creating Variable Cell References in a Loop and Ignoring blank cells

C

Cindy U

New Member
Joined
Nov 20, 2012
Messages
2
Hello - I am new to writing Excel Macros/VBA code and would appreciate some help. I am trying to copy certain cells from one Excel worksheet (Plantilla Estimados) and paste them into another (Estimates) and also insert some text in between the copied information.

On the Plantilla Estimados worksheet, there are 13 categories (001 - Billing, 100-Material, 200 - Mechanical Labor, etc) with the activity details (actividad lines) underneath. Some categories may have multiple activities, while others may not have any. The categories that have activitity details could change from day to day. My output needs to be in a single row and contain all Category and associated activity details if it is populated on the worksheet. Any categories without activity details could be ignored. An example of the the desired output, the worksheet, and my code is below.

Here are my questions:
1) How can I create variables for the cell references that I need to copy and paste so as my macro goes through the loop it would copy the information from row 3 in the first loop, row 4 in the second loop, row 5 in the third loop, etc? Please note that I have a formula on the Plantilla Estimados worksheet that does a "CountA" of the lines in each category, and I use this to determine how many time to repeat the loop for a Category.

2) In between each activity line, the last keystrokes should be "\{ESC}" and "*SL(5)", but for the last activity line the keystrokes should be "\{ESC 4}" and "*SL(5)". How can I accomplish this?

3) How can I write the macro to skip/ignore categories that don't have any activity lines on a given day? This is not a critical requirement, but would definitely streamline my output, if it is possible to do.

Also, I would appreciate feedback on the overall design of this macro. As mentioned, I am new to writing code, and I'm sure there is a much more efficient way to achieve the results I am after. I am using Windows 7, Excel 2007.

Thanks in advance for the feedback!


DESIRED OUTPUT (Ideal format - ignores categories with blank activity lines):
[TABLE="width: 3234"]
<TBODY>[TR]
[TD="class: xl189, width: 65, bgcolor: transparent"]001
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\1
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]ENT
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]010
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]Facturacion por Materiales
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{TAB 7}
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]01
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{TAB 5}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]200000
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{TAB 4}
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{ESC}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]030
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]Anticipo 30% valor del proyecto
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 7}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]01
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 5}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]60000
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 4}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{ESC}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]030
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]Descuento Anticipo 30%
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 7}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]01
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 5}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]-1
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]60000
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 4}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{ESC}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{ESC 3}
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\1
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]ENT
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]Materiales de construccion
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 13}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]22000
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 2}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{ESC 4}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[/TR]
</TBODY>[/TABLE]


ALTERNATIVE OUTPUT (Acceptable format - Does not ignore categories with blank activity lines):
[TABLE="width: 3626"]
<TBODY>[TR]
[TD="class: xl189, width: 65, bgcolor: transparent"]001
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\1
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]ENT
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]010
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]Facturacion por Materiales
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{TAB 7}
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]01
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{TAB 5}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]200000
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{TAB 4}
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{ESC}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]030
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]Anticipo 30% valor del proyecto
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 7}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]01
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 5}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]60000
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 4}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{ESC}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]030
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]Descuento Anticipo 30%
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 7}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]01
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 5}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]-1
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]60000
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 4}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{ESC}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]\{ESC 3}
[/TD]
[TD="class: xl190, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\1
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]ENT
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]Materiales de construccion
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 13}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]22000
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{TAB 2}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{ESC 4}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]TAB
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(2)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\1
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]ENT
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]SL(5)
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]\{ESC 4}
[/TD]
[TD="class: xl191, width: 65, bgcolor: transparent"]*SL(5)
[/TD]
[/TR]
</TBODY>[/TABLE]


PLANTILLA ESTIMADOS WORKSHEET: [TABLE="width: 1079"]
<TBODY>[TR]
[TD="class: xl204, width: 88, bgcolor: white"][/TD]
[TD="class: xl205, width: 208, bgcolor: white"][/TD]
[TD="class: xl199, width: 211, bgcolor: white"][/TD]
[TD="class: xl200, width: 419, bgcolor: white"][/TD]
[TD="class: xl200, width: 91, bgcolor: white"][/TD]
[TD="class: xl201, width: 132, bgcolor: white"][/TD]
[TD="class: xl202, width: 126, bgcolor: white"][/TD]
[TD="class: xl200, width: 164, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl261, bgcolor: white, colspan: 8"]FACTURACIÓN
[/TD]
[/TR]
[TR]
[TD="class: xl206, bgcolor: white"]Actividad
[/TD]
[TD="class: xl254, width: 208, bgcolor: #376091"]001 - BILLING
[/TD]
[TD="class: xl207, width: 211, bgcolor: white"]Aplicación de la Actividad
[/TD]
[TD="class: xl208, bgcolor: white"]Descripción
[/TD]
[TD="class: xl208, bgcolor: white"]Cod. Impuesto
[/TD]
[TD="class: xl220, bgcolor: white"]Cant.
[/TD]
[TD="class: xl221, width: 126, bgcolor: white"]Valor Unit.
[/TD]
[TD="class: xl222, width: 164, bgcolor: white"]Valor Tot.
[/TD]
[/TR]
[TR]
[TD="class: xl209, bgcolor: white"]010
[/TD]
[TD="class: xl210, width: 208, bgcolor: white"]Billing VAT
[/TD]
[TD="class: xl251, bgcolor: white"]Factura al CLIENTE con IVA
[/TD]
[TD="class: xl210, width: 419, bgcolor: white"]Facturacion por Materiales
[/TD]
[TD="class: xl245, width: 91, bgcolor: white"]01
[/TD]
[TD="class: xl211, width: 132, bgcolor: white"]1
[/TD]
[TD="class: xl212, bgcolor: white"]200,000.00
[/TD]
[TD="class: xl213, bgcolor: white"]200,000.00
[/TD]
[/TR]
[TR]
[TD="class: xl209, bgcolor: white"]030
[/TD]
[TD="class: xl210, width: 208, bgcolor: white"]Anticipos
[/TD]
[TD="class: xl251, bgcolor: white"]Solicitud de Anticipo
[/TD]
[TD="class: xl210, width: 419, bgcolor: white"]Anticipo 30% valor del proyecto
[/TD]
[TD="class: xl245, width: 91, bgcolor: white"]01
[/TD]
[TD="class: xl211, width: 132, bgcolor: white"]1
[/TD]
[TD="class: xl212, bgcolor: white"]60,000.00
[/TD]
[TD="class: xl213, bgcolor: white"]60,000.00
[/TD]
[/TR]
[TR]
[TD="class: xl209, bgcolor: white"]030
[/TD]
[TD="class: xl210, width: 208, bgcolor: white"]Anticipos
[/TD]
[TD="class: xl251, bgcolor: white"]Solicitud de Anticipo
[/TD]
[TD="class: xl210, width: 419, bgcolor: white"]Descuento Anticipo 30%
[/TD]
[TD="class: xl245, width: 91, bgcolor: white"]01
[/TD]
[TD="class: xl211, width: 132"]-1
[/TD]
[TD="class: xl212, bgcolor: white"]60,000.00
[/TD]
[TD="class: xl213, bgcolor: white"](60,000.00)
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl247, bgcolor: white"][/TD]
[TD="class: xl210, width: 208, bgcolor: white"][/TD]
[TD="class: xl251, bgcolor: white"][/TD]
[TD="class: xl210, width: 419, bgcolor: white"][/TD]
[TD="class: xl218, width: 91, bgcolor: white"][/TD]
[TD="class: xl211, width: 132, bgcolor: white"][/TD]
[TD="class: xl212, bgcolor: white"][/TD]
[TD="class: xl213, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl248, bgcolor: white"][/TD]
[TD="class: xl214, width: 208, bgcolor: white"][/TD]
[TD="class: xl252, bgcolor: white"][/TD]
[TD="class: xl214, width: 419, bgcolor: white"][/TD]
[TD="class: xl219, width: 91, bgcolor: white"][/TD]
[TD="class: xl215, width: 132"][/TD]
[TD="class: xl216, bgcolor: white"][/TD]
[TD="class: xl217, bgcolor: white"]-
[/TD]
[/TR]
[TR]
[TD="class: xl264, bgcolor: #376091, colspan: 7"]TOTAL 001 - BILLING PEN - 0
[/TD]
[TD="class: xl229, bgcolor: #376091"]200,000.00
[/TD]
[/TR]
[TR]
[TD="class: xl270, bgcolor: white"][/TD]
[TD="class: xl271, bgcolor: white"][/TD]
[TD="class: xl272, bgcolor: white"]Bill Count
[/TD]
[TD="class: xl272, bgcolor: white"]3
[/TD]
[TD="class: xl273, bgcolor: white"]20
[/TD]
[TD="class: xl273, bgcolor: white"]14
[/TD]
[TD="class: xl273, bgcolor: white"]48
[/TD]
[TD="class: xl274, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl227, bgcolor: white"][/TD]
[TD="class: xl227, bgcolor: white"][/TD]
[TD="class: xl227, bgcolor: white"][/TD]
[TD="class: xl227, bgcolor: white"][/TD]
[TD="class: xl227, bgcolor: white"][/TD]
[TD="class: xl227, bgcolor: white"][/TD]
[TD="class: xl228, bgcolor: white"][/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl255, bgcolor: transparent, colspan: 7"]COSTOS (El Código de Impuesto puede ser cualquier valor)
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl230, bgcolor: transparent"]Actividad
[/TD]
[TD="class: xl231, width: 208, bgcolor: #31849b"]100 - MATERIAL
[/TD]
[TD="class: xl232, width: 211, bgcolor: transparent"]Aplicación de la Actividad
[/TD]
[TD="class: xl233, bgcolor: transparent"]Descripción
[/TD]
[TD="class: xl234, bgcolor: transparent"]Cant.
[/TD]
[TD="class: xl235, width: 132, bgcolor: transparent"]Valor Unit.
[/TD]
[TD="class: xl236, width: 126, bgcolor: transparent"]Valor Tot.
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"]105
[/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"]Construction Material
[/TD]
[TD="class: xl250, bgcolor: transparent"]Conduit and carrier systems, wire, cable, tubing, fittings, hangers, straps, fasteners, brackets, etc. This material is usually furnished by trades' people and is usually Vendor Material (105V).
[/TD]
[TD="class: xl223, bgcolor: transparent"]Materiales de construccion
[/TD]
[TD="class: xl244, bgcolor: transparent"]1
[/TD]
[TD="class: xl196, bgcolor: transparent"]22,000.00
[/TD]
[TD="class: xl237, bgcolor: transparent"]22,000.00
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl203, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl195, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl249, bgcolor: transparent"][/TD]
[TD="class: xl197, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl253, bgcolor: transparent"][/TD]
[TD="class: xl224, bgcolor: transparent"][/TD]
[TD="class: xl246, bgcolor: transparent"][/TD]
[TD="class: xl198, bgcolor: transparent"][/TD]
[TD="class: xl239, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl258, bgcolor: #31849b, colspan: 6"]TOTAL 100 - MATERIAL PEN - 0
[/TD]
[TD="class: xl240, bgcolor: #31849b"]22,000.00
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl276, bgcolor: white"][/TD]
[TD="class: xl277, bgcolor: white"][/TD]
[TD="class: xl278, bgcolor: white"]Material Count
[/TD]
[TD="class: xl278, bgcolor: white"]1
[/TD]
[TD="class: xl273, bgcolor: white"]16
[/TD]
[TD="class: xl273, bgcolor: white"]10
[/TD]
[TD="class: xl273, bgcolor: white"]16
[/TD]
[TD="class: xl274, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl230, bgcolor: transparent"]Actividad
[/TD]
[TD="class: xl241, bgcolor: #fac090"]200 - Mechanical Labour
[/TD]
[TD="class: xl232, width: 211, bgcolor: transparent"]Aplicación de la Actividad
[/TD]
[TD="class: xl233, bgcolor: transparent"]Descripción
[/TD]
[TD="class: xl234, bgcolor: transparent"]Cant.
[/TD]
[TD="class: xl235, width: 132, bgcolor: transparent"]Valor Unit.
[/TD]
[TD="class: xl236, width: 126, bgcolor: transparent"]Valor Tot.
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 419, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl237, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl203, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl243, bgcolor: transparent"][/TD]
[TD="class: xl225, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl250, bgcolor: transparent"][/TD]
[TD="class: xl223, bgcolor: transparent"][/TD]
[TD="class: xl244, bgcolor: transparent"][/TD]
[TD="class: xl196, bgcolor: transparent"][/TD]
[TD="class: xl238, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl249, bgcolor: transparent"][/TD]
[TD="class: xl226, width: 208, bgcolor: transparent"][/TD]
[TD="class: xl253, bgcolor: transparent"][/TD]
[TD="class: xl224, bgcolor: transparent"][/TD]
[TD="class: xl246, bgcolor: transparent"][/TD]
[TD="class: xl198, bgcolor: transparent"][/TD]
[TD="class: xl239, bgcolor: transparent"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl267, bgcolor: #fac090, colspan: 6"]TOTAL 200 - Mechanical Labour PEN - 0
[/TD]
[TD="class: xl242, bgcolor: #fac090"]-
[/TD]
[TD="class: xl200, bgcolor: white"][/TD]
[/TR]
</TBODY>[/TABLE]

MY CODE:
Sub CreateEstimates()
'
' CreateEstimates Macro
' Copies, pastes, and formats the information in the Plantilla Estimados worksheet
'
' Keyboard Shortcut: Ctrl+Shift+E
'
'Format 001 Billing information
'
ActiveCell.FormulaR1C1 = "'001"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "TAB"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(2)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "\1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "ENT"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(5)"
ActiveCell.Offset(0, 1).Select
'Begin the loop for the Billing Activity Lines
Dim b As Integer
Dim BillLines As Range
'Set # of BillLines based on CountA() formula hidden on the Plantilla Estm worksheet
Set BillLines = Worksheets("2 - Plantilla Estimados").Range("D29")
For b = 1 To BillLines
'How can I make the cell references variables so I can copy paste and
'when the macro loops the cell reference updates to the next row of information?
'For example the Range("A15").Select would change to Range("A16").Select on the next pass?
Sheets("2 - Plantilla Estimados").Select
Range("A15").Select
Selection.Copy
Sheets("Estimates").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "TAB"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(2)"
ActiveCell.Offset(0, 1).Select
Sheets("2 - Plantilla Estimados").Select
Range("D15").Select
Selection.Copy
Sheets("Estimates").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "\{TAB 7}"
ActiveCell.Offset(0, 1).Select
Sheets("2 - Plantilla Estimados").Select
Range("E15").Select
Selection.Copy
Sheets("Estimates").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "\{TAB 5}"
ActiveCell.Offset(0, 1).Select
Sheets("2 - Plantilla Estimados").Select
Range("F15").Select
Selection.Copy
Sheets("Estimates").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "TAB"
ActiveCell.Offset(0, 1).Select
Sheets("2 - Plantilla Estimados").Select
Range("G15").Select
Selection.Copy
Sheets("Estimates").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "\{TAB 4}"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(5)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "\{ESC}"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(5)"
ActiveCell.Offset(0, 1).Select
'
'The next three commented lines are from previous tests - IGNORE THESE
'ActiveCell.FormulaR1C1 =Worksheets("2 - Plantilla Estimados").Range("A15")
'ActiveCell.FormulaR1C1 = "Bill Loop"
'ActiveCell.Offset(0, 1).Select
'
'Continue the Billing lines loop
Next b
'
'Hit the Escape button to return to the SubProjects Menu
'Is there a way to set the last iteration of the loop to "\{ESC 4}" and "*SL(5) so I don't have
'to decrement this to "\{ESC 3}" - need to consider what will happen for categories without activity lines
'If they are not included in the output it should be okay, if included, I will need the "\{ESC 4}" and "*SL(5)"
ActiveCell.FormulaR1C1 = "\{ESC 3}"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(5)"
ActiveCell.Offset(0, 1).Select
'
'Format 100 Material information
'
ActiveCell.FormulaR1C1 = "100"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "TAB"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(2)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "\1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "ENT"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(5)"
ActiveCell.Offset(0, 1).Select
'Begin the loop for the Material Activity Lines
Dim m As Integer
Dim MatlLines As Range
Set MatlLines = Worksheets("2 - Plantilla Estimados").Range("D43")
For m = 1 To MatlLines
'Need to insert the code to copy and paste data from Plantilla Estimados worksheet here
'The line below is just a placeholder for testing the loop functionality
ActiveCell.FormulaR1C1 = "Matl Test"
ActiveCell.Offset(0, 1).Select
'Continue the Material lines loop
Next m
'
ActiveCell.FormulaR1C1 = "\{ESC 4}"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(5)"
ActiveCell.Offset(0, 1).Select
'
'Format 200 Mechanical Labor information
'
ActiveCell.FormulaR1C1 = "200"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "TAB"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(2)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "\1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "ENT"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "*SL(5)"
ActiveCell.Offset(0, 1).Select
'Begin the loop for the Mechanical Labor Activity Lines
Dim h As Integer
Dim MechLines As Range
Set MechLines = Worksheets("2 - Plantilla Estimados").Range("D55")
For h = 1 To MechLines
'The code continues in the same fashion as seen above for all 13 categories on the Plantilla Estm worksheet
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
After additional manipulation of my code, I have been able to resolve questions 2 and 3 posted above by including some If Then statements. I am still baffled on how to create variable cell references so the cell(s) that are being copied and pasted increment by 1 row as the loop is executed - ie, in the first pass of the loop the value in Cell A15 is selected for the activity line, in the second pass of the loop the value in Cell A16 is selected, in the third pass of the loop the value in Cell A17 is selected, and so on. Any help/suggestions would be greatly appreciated!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
How can i extract the correct leave time on this code?
Replies
1
Views
545
rlv01
R
J
  • Question Question
Excel VBA to Sum to bottom of column of data
Replies
3
Views
880
Joe4
Joe4
S
  • Solved
Make row in VBA formula variable
Replies
6
Views
1K
Joe4
Joe4
B
  • Question Question
How to Create a Loop to ignore BLANK CELLS
Replies
3
Views
470
Alex Blakenburg
A
J
  • Question Question
VBA Help- Looping the Find Function
2
Replies
18
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top