How to copy information from non consecutive lines into consecutive lines

[bye013]

Dear all,
I have a real challenge for you:
I have huge and complex Workbook, but in order to simplify my question I’ve created a simple table and attached it to this question.

I have a table of data in Worksheet #1. In some line I have summaries of the lines above.
I need to have in a second worksheet only the data itself, i.e. I need to copy only the data lines, but not the entire lines and not the summary lines.
Actually I need to do some more manipulations on the data that will be retrieved, but I believe I will be able to get it from there.
I just need to know how can I copy specific information (cell by cell, not the entire line), only from specific lines, and I need the copied information to be continuous, i.e. without empty lines in between.
Worksheet #1 contains the base table, in which the information should be retrieved from.
Worksheet #2 contains the way the data retrieved should be looks like. You can see that the information is copied from non continuous lines (it skips the summary lines)

Any kind of a help will be very much appreciated.

Yaron.

[abousetta]

Hello and welcome to the forum,

I am a bit confused. Do you mean you want to get what is column D? Currently you have "=Sheet1!C4" which would make two identical columns. Also are you up for a vba solution to this problem?

abousetta

[dilipandey]

Hi Yaron,

You want data something like if Customer A has three row of data, hence it is repeated thrice and since customer D has two row of data hence it is repeated twice in your worksheet 2.

Please confirm if this is correct so that we can help you. Thanks.

[bye013]

Hello,
Thank you for the greetings, and sorry that it was unclear. Now I see that I had a mistake in my example. The “Requested values” should have consists values from column D in Worksheet1 (i.e. “=Sheet1!D4”, “=Sheet1!D5”, etc.) and not from column C.

Anyway, by writing "=Sheet1!D4" at cell C4 of Worksheet2 I meant to say that I would like to have D4 value from Sheet1 at that place. In normal condition I would just simply write that formula (without the inverted commas) and get the value I need.
The problem in this case is that I cannot copy this simple formula to the next rows (and I have hundreds) because I need to copy values only from specific rows in worksheet1 (only those rows that are without a sum function in column D).

I know there are better ways to express what exactly I need, but this is my language gap, apologize.
Appreciate if you will let me know if I need to explain further.

As for the VBA, I know how to add modules at the visual basic page, but I don’t know how to write the code itself, just to copy it in.

Thanks,
Yaron.

[dilipandey]

Hi Yaron,

Try below code which I have quickly developed as per your criteria:-

Sub copydata()

a = ActiveSheet.Name
Sheets.Add
b = ActiveSheet.Name
m = InputBox("Enter month / qtr to be copied", "Enter Details")

Sheets(a).Select
Sheets(a).Range("C:C").Copy
Sheets(b).PasteSpecial

Sheets(a).Select
Range("3:3").Select
Selection.Find(What:=m, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireColumn.Copy
Sheets(b).Select
Range("b1").PasteSpecial

Range("A65536").End(xlUp).Select
While ActiveCell.Value <> "" Or ActiveCell.Offset(-1, 0).Value <> ""
Selection.UnMerge
Selection.Offset(-1, 0).Select
Wend

Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Range("B1").End(xlDown).EntireRow.Clear
Range("B65536").End(xlUp).EntireRow.Clear

End Sub

Note:- Insert above in any module and run the macro. Remember you need to input the exact month or Qtr name in the inpur box when asked. I have checked it for "Jan" - without quotes.

Enjoy

Regards,
DILIPandey

[bye013]

Hi Dilipandey,
Unfortunately I couldn't run this Macro.
I've entered exactly "Jan" (without qoutes) but It gives me all the time "Run-time Error 1004" (the copy and the paste area are not the same sizeand shape).
What did I did wrong?
Thanks,
Yaron.

[dilipandey]

Hi Yaron,

Ooopss .. I missed this.
Now I have simplified the code a little bit and attached the sample workbook for you to test out.

Note: Now you need to just enter the column which you want to copy, see the instructions in the input box

Let me know if this helps and feel free to get back in case of any questions.

Regards,
DILIPandey

[bye013]

Sorry DILIPandey but where is the Workbook?
I don't see mine's either, maybe there is an issue with the web site (or maybe I'm just still not familiar with this forum).
Anyway, could you simply paste the code again with the message text?
Thanks,
Yaron.

[dilipandey]

Yes Yaron...

You are right.. there is some issue with the attachments.
Don't worry... I will try uploading once again, else will post the code for you.

Regards,
DILIPandey

[dilipandey]

Hi Yaron,

As confirmed earlier, attached is the sample workbook where I have enhanced the code a little bit:-

Note: Now you need to just enter the column which you want to copy, see the instructions in the input box

Let me know if this helps and feel free to get back in case of any questions.

Below is the code used for your quick reference and in case the attachment again gets missed out

Please Login or Register  to view this content.

Regards,
DILIPandey

[shg]

Happy to have you among us, DILIPandey.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

[abousetta]

Here's a shorten version of the code provided by @dilipandey but I can't seem to find the attachments in any of the posts to test it on. In general there is almost never a reason to use selections (e.g. .Select) and its generally better use Rows.Count.End(xlUp).Row rather than 65536 since the sheets have gotten much larger in post 2003 versions of Excel. Finally, it not advisable to use a whole row or column but rather limit the data manipulation to the size of your data (I haven't done this because I can't see the workbook structure).

Hope this helps.

abousetta

Please Login or Register  to view this content.

[bye013]

Hi dilipandey,
Thank you for the code, it seems that it is in the right direction.

Hi Abousetta,
The code you have sent has a kind of a bug but I guess this is because it has not been tested on the workbook as you said, so I have attached it again. Anyway, if the result is the same as dilipandey’s code then, as mentioned, it is in the right direction.

I need three improvements for it:
1. Copy multiple columns on the same worksheet, and not only one.
2. Skip the lines in which all information is zero (or not filled), same as it skips the summary lines.
3. All work should be done on the same worksheet. It means that we can refresh the data any time, i.e. if the data is changed in Worksheet #1, or even if entire lines are added or deleted in worksheet #1, activating the macro will refresh the data in worksheet #2 accordingly.
I need this to be on the same worksheet (and not just creating another worksheet and deleting the previous one) because some more data will be added manually on worksheet #2.
Do you think it is possible?

I appreciate so much your help.

Regards,
Yaron.

[dilipandey]

Thanks Yaron.

Yes this is possible.
I will try and accomodate your changes in the next release.


< Click on below star if this helps >

Regards,
DILIPandey

[bye013]

Hi Dilipandey,
I couldn't find the star to click on.
Yaron.

[dilipandey]

Hey Yaron,

It is there on the bottom left side.

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

Hi Yaron,

'star' is located at the bottom left side of the reply window.
I am into the process of releasing the next version of code and will post in a while. Thanks.

Regards,
DILIPandey

< click on below 'star' if this helps >

[dilipandey]

Hi Yaron,

Attached file is revised as per requirements.
Regarding deleting rows if any column have zeros in it, will affect another columns data and the alignment of the fields in column A and also to totals in extreme right column, hence have not included that portion.

Go through the file and let me know if this works well.


Regards,
DILIPandey

< click on below 'star' if this helps >