copy to new sheet with the help of Macro

john5599

Board Regular
Joined
Mar 11, 2010
Messages
222
I have an excel sheet same like below. Column A1 to A6 is header so it sould copy to new sheet. Also copy If column I7 to last column in the sheet which contains X should copy all field to new sheet. I am using excel 2007. Can somebody give a solution.[TABLE="width: 866"]
<colgroup><col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" width="75"> <col style="width: 368pt; mso-width-source: userset; mso-width-alt: 17956;" width="491"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;" width="66"> <col style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" span="2" width="72"> <col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;" width="102"> <col style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;" width="100"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;" width="95"> <tbody>[TR]
[TD="class: xl64, width: 75, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 491, bgcolor: transparent"]M/S[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 72, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 102, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 100, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 81, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]M/S[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]CLEARING[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent, align: center"]S.N.[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]NAME OF ITEMS[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]MRP[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]PACKING SIZE[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]DISCOUNT (%)[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]VAT RATES (%)[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]ORDER QTY IN PCS[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]ORDER QTY IN BOXES[/TD]
[TD="class: xl71, bgcolor: transparent, align: center"]copy[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]1[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AAMLKI RASAYAN -100GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]65[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]200[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]2[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AAMVATARI RAS-20GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]35[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]400[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]3[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AAROGYA DALIA (PUSHTAHAR) -500GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]30[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]30[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]4[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AAROGYA VARDHANI VATI 20GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]40[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]400[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AAROGYA VARDHANI VATI 40GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]80[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]400[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]6[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]ABHAYARISHTA-450ML[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]60[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]7[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]ABHRAK BHASM -5GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]13[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]200[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]8[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AJMODADI CHURN-100 GRM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]30[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]100[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]9[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AKIK PISHTI - 5GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]15[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]200[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]10[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AMLA CANDY 500 GRM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]110[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]24[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]11[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AMLA CHATPATA -500GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]115[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]24[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: center"]12[/TD]
[TD="class: xl73, bgcolor: transparent, align: center"]AMLA CHURNA -100GM[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]100[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]20.63[/TD]
[TD="class: xl72, bgcolor: transparent, align: center"]5[/TD]
[TD="class: xl74, bgcolor: transparent, align: center"]1000[/TD]
[TD="class: xl75, bgcolor: transparent, align: center"] [/TD]
[TD="class: xl72, bgcolor: transparent, align: center"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"][/TD]
[TD="bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, bgcolor: transparent, align: center"][/TD]
[TD="class: xl63, bgcolor: transparent, align: center"][/TD]
[TD="bgcolor: transparent, align: center"][/TD]
[TD="bgcolor: transparent, align: center"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I hope this does what you want

1. Go into VBA development (Alt F11)
2. Create a new module Insert|Module
3. Copy this code into the module
4. Make sure the sheet names match what you want/have
5. Run the macro "NewSheet"


Code:
Sub NewSheet()
    Const DATA = "[COLOR=#0000ff][B]Data[/B][/COLOR]"             ' Name of the orginal data sheet
    Const SR = "[COLOR=#0000ff][B]SelectedRows[/B][/COLOR]"       ' Name of sheet to hold selected records
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    On Local Error Resume Next
    Sheets(SR).Delete
    Set nWS = ThisWorkbook.Sheets.Add(After:=Sheets(ThisWorkbook.Sheets.Count))
    nWS.Name = SR
    Sheets(DATA).Range("C6").AutoFilter Field:=9, Criteria1:="X"
    Sheets(DATA).Cells.Copy
    Sheets(SR).Cells(1, 1).PasteSpecial xlAll
    Sheets(SR).Cells(1, 1).Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    For i = 1 To 3
        Sheets(SR).Cells(i, 2).Value = Sheets(DATA).Cells(i, 2).Value
    Next i
    Sheets(DATA).Range("A6").AutoFilter = False
End Sub
 
Upvote 0
Name of orginal data sheet = "PO EXCEL SHEET"
const SR = "A1:I309"

here a1: a6 is header
header line also copy one time to new sheet
 
Upvote 0
Code:
Sub NewSheet()
    Const DATA = "PO EXCEL SHEET"             ' Name of the orginal data sheet
    Const SR = "SelectedRows"       ' Name of sheet to hold selected records
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    On Local Error Resume Next
    Sheets(SR).Delete
    Set nWS = ThisWorkbook.Sheets.Add(After:=Sheets(ThisWorkbook.Sheets.Count))
    nWS.Name = SR
    Sheets(DATA).Rows(6).Insert
    Sheets(DATA).Range("C7").AutoFilter Field:=9, Criteria1:="X"
    Sheets(DATA).Rows(6).Delete
    Sheets(DATA).Cells.Copy
    Sheets(SR).Cells(1, 1).PasteSpecial xlAll
    Sheets(DATA).Range("A6").AutoFilter = False
    Sheets(SR).Cells(1, 1).Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
Sir both of your macro is working. Sir 2 - 3 doubts. Can you explain me?. In your first macro you define For i = 1 To 3 what that command says. Second doubts in macro 2 you define Sheets(DATA).Range("C7").AutoFilter Field:=9, Criteria1:="X". Can we use Sheets(DATA).Range("A7").AutoFilter Field:=9, Criteria1:="X" instead of that. Any way thanks alot.

Sir can we search NAME OF ITEMS columns search with the help of macro?. At time search with a user input column just in database software search. Search with name of items columns that contains some particular words. Example if we type Amla in user input dialog box then search all the NAME OF ITEMS for the word contains Amla on time and show the result so we can select the particular item by mouse click or enter then x mark should come to the copy column of the worksheet. There should need a user input column or Dialog box for type contained word for the search. Sir if it is possible it will be very helpful to me. In access database I do such a search .
 
Last edited:
Upvote 0
1. In your initial description you had:

[TABLE="width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]M/S
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]M/S
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLEARING
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S.N.[/TD]
[TD]NAME OF ITEMS[/TD]
[TD]copy[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AAMLKI RASAYAN -100GM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AAMVATARI RAS-20GM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AAROGYA DALIA (PUSHTAHAR) -500G[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The 1 to 3 is the start of the loop that copies the three cells.

2. As long as there is something in A7

3. This is a new request and should be in a new tread
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top