Tricky copy help please

[samitnair]

Hi

I was looking for a Vb which can copy the data from columns according to the serial numbers....
exanple

1. Enter customer name in Sheet 1 B5)
2. Macro Searches the customer name in sheet 2 (B2:B65536)
3. Copy the data of the particular customer mentioned in multiple rows.
4. Paste it in B19 sheet 1
I have attached the pic of sheet 2 for better understanding

Now the problem arises while copying....The data or the details like bill no, date and value are entered in 2 or more lines(refer pic) and....i need assistance in copying the data until the macro finds another serial number (1,2,3)...am taking serial number as a Unique value because there are other details mentioned in Column B (Sheet 2) which are not customer name like tds deducted etc....

Please provide me with relevant step.....any suggestion is appreciated.

Thanks in advance

 

[samitnair]

Hi Peter

Hmm ur quite right about downloading....but i assure u its pure xls sheet and virus free.

Error: run time error 9 "Subscript out of range"
and the colored line is ........."With Sheets("Sheet 2")........

I changed it by deleting the space between sheet and 2 but after that it is not responding.

I tried the same code in Office 2003 XP OS (currently using Win7 Office 10) but still there is no response when i enter the customer name

thanks

 

[Peter_SSs]

1. That error would indicate that the sheet name in the code and the actual sheet name do not match. You need to ensure they match exactly. Check that there are not any trailing (or leading) spaces in your actual sheet name as that is a common cause of this error.

2. Each time you get that error you are likely to end up with 'events' disabled again. You need to repeat the process I gave in post #8.

 

[samitnair]

Hi

Thanks for the support....the code worked perfectly...but can we modify the code to insert row in sheet1 B19 and paste the data copied from sheet 2

Regards
Samit Nair

 

[Peter_SSs]

I don't understand what you mean. Can you explain again using different wording?

The current code clears everything in Sheet 1 in columns B:D from row 19 down before it puts new data in. Do you still want that to happen? If not, what should happen to any existing data in Sheet 1, cols B:D, row 19 and below?

 

[samitnair]

Sorry pardon my Indian english ..............I have pasted the sheet for your understanding

This is the destination sheet (Sheet1).....and am trying to fill this sheet automatically...

The code u have given works fine.....but the command "clear content" also deletes the lines highlighted "red". I found a solution and entered the text in column A as the "clear content" command works only in column B:D......

I would need the Macro to be modified to insert rows in sheet 1 and then paste the data copied from sheet 2. (so that the data marked in "red" moves down and doesn't get deleted)

Hope u would be able to understand me

 

[Peter_SSs]

There seems to have been a bit of changing columns and rows so this may not quite fit your circumstances, but here is a slightly different approach. Again test in a copy of your file.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> CustName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CustFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> FirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>    <SPAN style="color:#00007F">Const</SPAN> NameCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B5"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(NameCell)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        CustName = Range(NameCell).Value<br><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsEmpty(Range("B19")) <SPAN style="color:#00007F">Then</SPAN><br>            Range("B19").CurrentRegion.EntireRow.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br>        <SPAN style="color:#00007F">If</SPAN> CustName <> vbNullString <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet 2")<br>                <SPAN style="color:#00007F">Set</SPAN> CustFound = .Columns("B").Find(What:=CustName, LookIn:=xlValues, _<br>                    LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>                <SPAN style="color:#00007F">If</SPAN> CustFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    MsgBox CustName & " not found"<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    FirstRow = CustFound.Row<br>                    r = FirstRow<br>                    LastRow = .Range("G" & .Rows.Count).End(xlUp).Row<br>                    <SPAN style="color:#00007F">Do</SPAN><br>                        r = r + 1<br>                    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> .Range("A" & r).Value = "" And r <= LastRow<br>                    rws = r - FirstRow<br>                    Rows(19).Resize(rws).Insert<br>                    Range("B19").Resize(rws, 3).Value = _<br>                        CustFound.Offset(, 3).Resize(rws, 3).Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>