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

[Cindy U]

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

001

TAB

*SL(2)

\1

ENT

*SL(5)

010

TAB

*SL(2)

Facturacion por Materiales

\{TAB 7}

01

\{TAB 5}

1

TAB

200000

\{TAB 4}

*SL(5)

\{ESC}

*SL(5)

030

TAB

*SL(2)

Anticipo 30% valor del proyecto

\{TAB 7}

01

\{TAB 5}

1

TAB

60000

\{TAB 4}

*SL(5)

\{ESC}

*SL(5)

030

TAB

*SL(2)

Descuento Anticipo 30%

\{TAB 7}

01

\{TAB 5}

-1

TAB

60000

\{TAB 4}

*SL(5)

\{ESC}

*SL(5)

\{ESC 3}

*SL(5)

100

TAB

*SL(2)

\1

ENT

SL(5)

105

TAB

*SL(2)

Materiales de construccion

\{TAB 13}

22000

\{TAB 2}

*SL(5)

\{ESC 4}

*SL(5)

<TBODY>
</TBODY>

ALTERNATIVE OUTPUT (Acceptable format - Does not ignore categories with blank activity lines):

001

TAB

*SL(2)

\1

ENT

*SL(5)

010

TAB

*SL(2)

Facturacion por Materiales

\{TAB 7}

01

\{TAB 5}

1

TAB

200000

\{TAB 4}

*SL(5)

\{ESC}

*SL(5)

030

TAB

*SL(2)

Anticipo 30% valor del proyecto

\{TAB 7}

01

\{TAB 5}

1

TAB

60000

\{TAB 4}

*SL(5)

\{ESC}

*SL(5)

030

TAB

*SL(2)

Descuento Anticipo 30%

\{TAB 7}

01

\{TAB 5}

-1

TAB

60000

\{TAB 4}

*SL(5)

\{ESC}

*SL(5)

\{ESC 3}

*SL(5)

100

TAB

*SL(2)

\1

ENT

SL(5)

105

TAB

*SL(2)

Materiales de construccion

\{TAB 13}

22000

\{TAB 2}

*SL(5)

\{ESC 4}

*SL(5)

200

TAB

*SL(2)

\1

ENT

SL(5)

\{ESC 4}

*SL(5)

<TBODY>
</TBODY>

PLANTILLA ESTIMADOS WORKSHEET:

FACTURACIÓN
Actividad	001 - BILLING	Aplicación de la Actividad	Descripción	Cod. Impuesto	Cant.	Valor Unit.	Valor Tot.
010	Billing VAT	Factura al CLIENTE con IVA	Facturacion por Materiales	01	1	200,000.00	200,000.00
030	Anticipos	Solicitud de Anticipo	Anticipo 30% valor del proyecto	01	1	60,000.00	60,000.00
030	Anticipos	Solicitud de Anticipo	Descuento Anticipo 30%	01	-1	60,000.00	(60,000.00)
							-
							-
							-
							-
							-
							-
							-
							-
							-
							-
TOTAL 001 - BILLING PEN - 0	200,000.00
		Bill Count	3	20	14	48
COSTOS (El Código de Impuesto puede ser cualquier valor)
Actividad	100 - MATERIAL	Aplicación de la Actividad	Descripción	Cant.	Valor Unit.	Valor Tot.
105	Construction Material	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).	Materiales de construccion	1	22,000.00	22,000.00
						-
						-
						-
						-
						-
						-
						-
						-
TOTAL 100 - MATERIAL PEN - 0	22,000.00
		Material Count	1	16	10	16
Actividad	200 - Mechanical Labour	Aplicación de la Actividad	Descripción	Cant.	Valor Unit.	Valor Tot.
						-
						-
						-
						-
						-
						-
						-
						-
						-
TOTAL 200 - Mechanical Labour PEN - 0	-

<TBODY>
</TBODY>

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

 

[Cindy U]

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!