Duplicate rows based on cell value

Hi,

Thank you so much for helping me out with the code it helped me a lot. I don't know so much about excel, I am a nurse working in senior's home. I am currently working on a spreadsheet. I have one more favor to ask you. I am working with data on a spreadsheet, if I enter the name of the client in a cell, I want to reference other data to table or database? wherein it will automatically enter other data on certain cells in the row like for example the postal code, room number etc. I don't know how to achieve it, i've searched a lot and can't find any answer.

Hi Fireburn,

It sounds to me the VLOOKUP formula will do the job.

But without details I can't help you more specifically.

Could you provide sample data like you did in your original post as it is now and how you foresee the result?
Also consider the possibility of posting your workbook (careful with personal data) using a filesharing site like www.speedyshare.com or ge.tt and then post back the download link.

Best regards,
Trowa

Hi,

It's me again, the code that you gave me is really helping me a lot, if you could spare your time again and add some modification in your code. Will it be possible that the last cell in "A" when I will execute your code, the first 2 character which I formatted to custom as 00000000 will increment by 1? Like for example, when I execute your code and assuming that A1 is 27022014 the last cell A3 for example , like if we input 3 in F1, it will give us 3 lines right? So the last cell, in A3 as a given exmple will be automatically increment to 28012014. Thank you for your time. Have a good one.

Hi Fireburn,

Great to read that the code is doing it's job.

The easiest way to do this is if the last cell is also the last used cell, meaning there is nothing below it. I'm assuming it is.
And why don't you format the dates as being a date? If we just add 1, then 31012014 would turn into 32012014, which can't be right.

If the last cell is actually the last cell and you formatted the dates as dates then the following will work:

Range("A" & Rows.Count).End(xlUp).Value = Range("A" & Rows.Count).End(xlUp).Value + 1

Place this line above the "End Sub" line.

Let me know how this works out for you.

Best regards,
Trowa

Hi, that's fast. Thank you. yes there's nothing below it, and it is really a date entry, but they want us to format the date with leading zeroes because it will be save in csv format and upload it to them for calculation of the services that we rendered.

Hi, I figured it out. Thanks for the code it worked indeed. Take care.

Hi, can you do me one more favor. I need help in counting all the non blank rows in my entire workbook, in a sheet there is a data in range C:C. The other thing is I want to copy all non blank rows in all of the worksheets to a target sheet in my workbook. The data are all in the range C:C - M:M in the worksheet.

Hi Fireburn,

The formula to count all non blank cells would look like:
=COUNTA(Sheet1!C:C)+COUNTA(Sheet2!C:C)
Add more COUNTA's for each sheet you have.

For your second question, I called your target sheet "Master". The range you want to copy is Column C and Column M (or did you mean C:M, meaning Column C till Column M?). Destination will be column A and B. In case this is correct use the following code:

Sub RunMe()
Dim ws As Worksheet

For Each ws In Worksheets
    If ws.Name <> "Master" Then
        ws.Activate
        For Each cell In Range("C:C")
            If cell.Value <> vbNullString Then
                Union(Cells(cell.Row, "C"), Cells(cell.Row, "M")).Copy _
                Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        Next cell
    End If
Next ws

Sheets("Master").Activate

End Sub

Let me know how this works out for you, I'm here for you.

Best regards,
Trowa

Hi,

I really appreciate your help. Thank you.

I mean it will be C:M. and destination will be A to J on the Master sheet.

Hi Fireburn,

For that use the following code:

Sub RunMe()
Dim ws As Worksheet

For Each ws In Worksheets
    If ws.Name <> "Master" Then
        ws.Activate
        For Each cell In Range("C:C")
            If cell.Value <> vbNullString Then
                Range(Cells(cell.Row, "C"), Cells(cell.Row, "M")).Copy _
                Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        Next cell
    End If
Next ws

Sheets("Master").Activate

End Sub

Best regards,
Trowa

HI,

Thank you for always there to help me out. I owe you a beer :-)
It worked.

@TrowaD--Your code is always so neatly formatted! Managers Dream! Way to go!

Thanks Fireburn, looking forward to it!

And thanks to you Mark.

I have great respect for you, so it means a lot to me!