ShumsFaruk
Board Regular
- Joined
- Jul 24, 2009
- Messages
- 93
Good Day All Excel MVPs,
I know its sounds repeated subject, but I have been trying to get bits and pieces from different threads and post, but failing to get results as per my need. I am in a situation, where I am tired of searching further.
I need to print Invoice before that I would require Cells to be filled with a cell reference.
Cell reference is Worksheets("Invoice_Template").Range("K8").Value
Once VBA finds the cell reference value in Worksheets("WBEntryDetails") Column B then I would like to offset to Worksheets("Invoice_Template")
Offset range should be:
Worksheets("Invoice_Template").Range("D24").value = Worksheets("WBEntryDetails") Column G ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("D28")
Worksheets("Invoice_Template").Range("F24").value = Worksheets("WBEntryDetails") Column H ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("F28")
Worksheets("Invoice_Template").Range("D25").value = Worksheets("WBEntryDetails") Column I ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("D29")
Worksheets("Invoice_Template").Range("F25").value = Worksheets("WBEntryDetails") Column J ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("F29")
Worksheets("Invoice_Template").Range("H26").value = Worksheets("WBEntryDetails") Column M ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("H30")
Worksheets("Invoice_Template").Range("I24").value = Worksheets("WBEntryDetails") Column T ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("I28")
I tried to get one offset for Range("D24") as per below code, but I am failing to get desired result.
Please help.
I know its sounds repeated subject, but I have been trying to get bits and pieces from different threads and post, but failing to get results as per my need. I am in a situation, where I am tired of searching further.
I need to print Invoice before that I would require Cells to be filled with a cell reference.
Cell reference is Worksheets("Invoice_Template").Range("K8").Value
Once VBA finds the cell reference value in Worksheets("WBEntryDetails") Column B then I would like to offset to Worksheets("Invoice_Template")
Offset range should be:
Worksheets("Invoice_Template").Range("D24").value = Worksheets("WBEntryDetails") Column G ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("D28")
Worksheets("Invoice_Template").Range("F24").value = Worksheets("WBEntryDetails") Column H ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("F28")
Worksheets("Invoice_Template").Range("D25").value = Worksheets("WBEntryDetails") Column I ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("D29")
Worksheets("Invoice_Template").Range("F25").value = Worksheets("WBEntryDetails") Column J ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("F29")
Worksheets("Invoice_Template").Range("H26").value = Worksheets("WBEntryDetails") Column M ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("H30")
Worksheets("Invoice_Template").Range("I24").value = Worksheets("WBEntryDetails") Column T ( cell reference value would possibly be repeated few times, so we need to loop), but I would need to leave 3 rows for next i which means next offset should start .Range("I28")
I tried to get one offset for Range("D24") as per below code, but I am failing to get desired result.
Code:
Option Explicit
Sub Update_InvoiceTemplate()
Dim ws As Worksheet, ws1 As Worksheet
Dim row As Integer
Dim LR As Long
Set ws = Worksheets("WBEntryDetails")
Set ws1 = Worksheets("Invoice_Template")
LR = WorksheetFunction.Max(24, ws1.Range("D" & Rows.Count).End(xlUp).row + 1)
row = 3
Do While (ws.Range("B" & row).Value <> "")
If ws.Range("B" & row).Value = ws1.Range("K8").Value Then
ws1.Cells(LR, row).Value = ws.Range("G" & row).Value
End If
row = row + 1
Loop
End SubPlease help.
