Copy paste entire row in another sheet having xyz name

**watermasa** · 05-16-2015, 01:35 PM

Hi,

I get daily fresh excel sheet containing data in sheet called "WorkArea", from my client. The data are in columns A to P . But sometimes this data are in 20 rows and sometimes are in 50 rows, sometimes more than that, but the column are fixed. In column "I" I have my clients name. Total clients are four, john, joe, masa and pie. In column "M" have other data which I need to copy and paste in sheet 2. Now I want vba which search name "masa" from column "I". Here the name "masa" sometimes are in 20 cells or sometimes are in 50 cells one below other. After searching name "masa" in column "I", I want to copy data which are in column "M" and paste it in sheet 2 in cell say A1. While pasting, the name "masa" should be pasted in A1 and the copied data from column "M" should be pasted in B1 one below other.

Again same process, search another name say "pie" from column "I" copy data from column "M" and paste in sheet 2. The "pie" should be pasted in C1 and its data which is taken from column "M" should be pasted D1.

Ignore all other names. I want only two name from column "I".

Thanks and best regards.

**YasserKhalil** · 05-16-2015, 01:57 PM

Hi watermasa
Try this code

Please Login or Register  to view this content.

Regards

**watermasa** · 05-19-2015, 06:00 AM

Thanks Yasser
I used above vba sent by you. But after running, its not copying at all in sheet 2. Sheet 2 remains blank, no copy paste at all.

Regards

**JOHN H. DAVIS** · 05-19-2015, 07:54 AM

Maybe:

Please Login or Register  to view this content.

**watermasa** · 05-19-2015, 08:41 AM

Hi John
Thank you for your prompt reply.

Just one changes made in your code is that instead of "Sheet2", I did "Sheet1", Its copying data but not I and M column, copying columns A,B,C and D. And copying upto 80 rows only.

Please make changes.

Thanks

**JOHN H. DAVIS** · 05-19-2015, 08:51 AM

Can you provide a sample with some dummy data in which it doesn't work as you would like? On my test of some data I made up it seemed to work.

**watermasa** · 05-19-2015, 11:10 AM

I am interested to show my original excel sheet, but could not find attachment option here. Search most but could not find. How do I attach file?

**JOHN H. DAVIS** · 05-19-2015, 11:26 AM

Scroll down to Go Advanced in the Quick Reply Window click it then scroll down to the Manage Attachments button, browse to the required file, and then push the Upload button Click Done in the lower left-hand corner..

**watermasa** · 05-19-2015, 12:38 PM

This is original file from my broker update daily in live market. This excel workbook get updated in streaming sheet. We can make changes in WorkArea sheet. Column I, J and M data need in next sheet. In next sheet in column "A" I want CE data from column "M" from WorkArea. In column "B" I want CE data from column "J" from WorkArea. In column "C" I want PE data from column "M" from WorkArea. In column "D" I want PE data from column "J" from WorkArea.

I also need further requirement.

Thank you very much John and best regards.

**JOHN H. DAVIS** · 05-19-2015, 01:34 PM

Try:

Please Login or Register  to view this content.

**watermasa** · 05-19-2015, 08:59 PM

Thanks John,
The data get copied and pasted, runtime error 9, shown as in attach picture. On workarea sheet all CE rows get deleted or sayfilter option remain ON. No data should be delete from workarea sheet.

See attachments.

**watermasa** · 05-19-2015, 09:11 PM

John,
Made small correction. In second last line, instead of "Sheet2", I made "Sheet1" and after this, there is no runtime error.

**JOHN H. DAVIS** · 05-21-2015, 11:10 AM

Is it working as you needed?

**watermasa** · 05-21-2015, 11:31 AM

Yes John, Thank you very much.
Its working fine for me. Just one small quire remains. The data get copy and pasted as per requirement. When I again run code for next day, the data goes below previous day's data. Means it has to overlap or it has to start from A2 cell.

**watermasa** · 05-24-2015, 03:22 AM

Hi John, Thank You.
One more help need from you.
Your above code is working fine. I made some changes and the code became as follows:-

Sub watermasa()
Dim x As String, y As String
x = InputBox("Please Enter the first name")
y = InputBox("Please Enter the second name")

With Sheets("ADANIENT")
.Range("C1:C" & .Range("C" & Rows.Count).End(3).Row).AutoFilter 1, x
.Range("D2:D" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIENT").Range("F" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.Range("B2:B" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIENT").Range("G" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.AutoFilterMode = False
.Range("C1:C" & .Range("C" & Rows.Count).End(3).Row).AutoFilter 1, y
.Range("D2:D" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIENT").Range("H" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.Range("B2:B" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIENT").Range("I" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.AutoFilterMode = False
End With

With Sheets("ADANIPORTS")
.Range("C1:C" & .Range("C" & Rows.Count).End(3).Row).AutoFilter 1, x
.Range("D2:D" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIPORTS").Range("F" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.Range("B2:B" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIPORTS").Range("G" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.AutoFilterMode = False
.Range("C1:C" & .Range("C" & Rows.Count).End(3).Row).AutoFilter 1, y
.Range("D2:D" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIPORTS").Range("H" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.Range("B2:B" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("ADANIPORTS").Range("I" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.AutoFilterMode = False
End With

With Sheets("APOLLOTYRE")
.Range("C1:C" & .Range("C" & Rows.Count).End(3).Row).AutoFilter 1, x
.Range("D2:D" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("APOLLOTYRE").Range("F" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.Range("B2:B" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("APOLLOTYRE").Range("G" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.AutoFilterMode = False
.Range("C1:C" & .Range("C" & Rows.Count).End(3).Row).AutoFilter 1, y
.Range("D2:D" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("APOLLOTYRE").Range("H" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.Range("B2:B" & .Range("C" & Rows.Count).End(3).Row).SpecialCells(12).Copy
Sheets("APOLLOTYRE").Range("I" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
.AutoFilterMode = False

End With
End Sub

This new above code is for example, showing only three sheets. I have almost 25 more sheets which has data and need to transfer their respective columns in the same sheets.

How I make this code short.

Copy paste entire row in another sheet having xyz name

LinkBack

Thread Tools

Rate This Thread

Display

Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Re: Copy paste entire row in another sheet having xyz name

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA code to copy and paste an entire row from one sheet to another

to copy paste the entire row to the new sheet

Help with Copy entire row and paste to new sheet

Copy paste entire row different sheet column C

Copy Entire Row & Paste in First Empty Row On The Same Sheet

Bookmarks

Bookmarks

Posting Permissions