Need Help

[da_phat]

Hi,
is there anyone can guide me on how can i extract data from one sheet which contains data into another worksheet with a template in it.Hereby i attached the sample data.The worksheet "Data" contains the data and worksheet "Invoice" is the template. Thanks

[lucas]

Try this thread, it might be just what you need...

http://vbaexpress.com/forum/showthread.php?t=24148

[da_phat]

thanks.i really help.

[da_phat]

Hi there,
i've just created a userform where i can use it as a data entry purposes.My problem is, i dont know how to isnert the data into specific cell in the template in the worksheet.Most of the example given here using lastrow.offset which i think can be use if you use for data entry line by line.Can anyone guide me here.I attached the sample form and template.The exact output will be in the worksheet named "output"

[JimmyTheHand]

Hi

I would create such an invoice as you uploaded in the following way.
1. Create an invoice template with header only.
2. Store the invoice footer in a different (possibly hidden) worksheet.
3. In the template, have the name, address, etc. fields as named ranges. E.g. I want the 'Bill To' information go into cell C10, so I give cell C10 the name "bill_to".
When coding, I can refer to them by name:
[vba]Private Sub cmdOK_Click()
Range("bill_to") = txtName.Text
'etc.[/vba] 4. Fill the invoice entries by the last row algorithm you are using now.
5. May want to count the added rows, and when their number reaches a maximum, start a new invoice page.
6. Copy the invoice footer from its storing place to a predefined range, or just below the last row of the invoice, as you like.

HTH

Jimmy

[da_phat]

thanks for the advice.Really appreciate it. I have several question regarding the work which are:
1)How can i seperate the footer as been told by you?
2)How can i make that everytime i enter the data, it will automatically create a new worksheet containing the data and the template.

Thanks

[da_phat]

hi,
can any1 guide me on how can i delete the empty rows between filled rows. For example, i have filled rows until A20 then empty rows until A30 then from A31 is filled again.How can i do that.thanks

[lucas]

[vba]
Sub DeleteEmptyRows()
Dim lastrow As Long
Dim r As Long
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = lastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
End Sub

[/vba]

Edit: Variables defined

[Ischyros]

I am not sure what criteria or data you have in these rows, but here is a simple macro that checks to see if a row contains data and if not it deletes it

[VBA]
Option Explicit

Sub Row_Check()

Dim intLastRow As Double, i As Integer

intLastRow = Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlFormulas, _
SearchDirection:=xlPrevious).EntireRow.Row


i = 1
While i <= intLastRow
intLastRow = Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlFormulas, _
SearchDirection:=xlPrevious).EntireRow.Row

If Worksheets(1).Cells(i, 1) = "" Then
Worksheets(1).Rows(i).Select
Selection.Delete Shift:=xlUp
Else
i = i + 1
End If

Wend
End Sub
[/VBA]

Hope this works! Let me know

[Ischyros]

Whoops.....use the code from lucas above because it checks all the cells in a row....thats why he is the Guru, lol!

[lucas]

Ischyros, if you select your code when you post and hit the VBA button it will be formatted as it is in the visual basic editor. I have edited your post...

[da_phat]

i have tried both code and it work but not as according to my requirement. I have a data for example in row 1 up to row 20. Then from row 20 to row 30 is blank and again from row 31 dowwards i have more data.What im looking for is to delete the blank rows between 20 to row 30.Is it possible to do that

[lucas]

Ischyros's code works fine if you only need to check one column before deciding to delete rows.....

[lucas]

So you only need to delete the first span of blank rows that you encounter?

Or you just need to check down so many rows?

Or is the code provided not deleting rows all the way down?

[da_phat]

The code does delete a rows.Ok, let me explain a bit details. I have a excel template and i created a userform in order to enter the data in it. From row 13 to row 30 i will be entering a product details but not always i have that many product to enter. I might be entering the product using th form up to row 25.Therefore, from row 26 to row 30, i would like to delete it.i attached the sample workbook.

[lucas]

da phat, this is the exact same file you uploaded for two other threads which I just merged into one. This seems to be directly related to the same questions you were having on the other two threads.

I am merging this thread with those other two so you can keep your project in one thread. Asking questions over several thread about the same project just confuses people so stick to one thread on this project please.

[da_phat]

sorry for that.I thought that i wont be entertained if i ask the whole project thing in here.So can i continue on this thread to ask for question or should i start a new thread?

[lucas]

Please keep the project together. We don't mind a little project creep but spreading the same project over several threads is just confusing....

Thanks for understanding.

[da_phat]

The code does delete a rows.Ok, let me explain a bit details. I have a excel template and i created a userform in order to enter the data in it. From row 13 to row 30 i will be entering a product details but not always i have that many product to enter. I might be entering the product using th form up to row 25.Therefore, from row 26 to row 30, i would like to delete it.i attached the sample workbook.

so sorry for that.Is it possible you could guide me on the deleting part?