• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort data & Copy to New sheet Macro Guide

C

csrahul

New Member
Hello Friends,

I need your help in sorting data and copy result to new sheet.
I have attached sample sheet.
Here is code that i found on google for same. code working fine but i want specify column on which this code run.

1. I want code to run A-I Column. Copy Result to newsheet.
2. I want Lock Cell that contain Macro Button. Can i do so.
3. I want add Instruction after column (I) That should not be copy to new sheet on which result of code pasted. and i need to lock Instruction cells.
4. Is it good code for Big Data like 3000-6000 cells.
5. Any other code that work fast and smooth than guide me on same.

Can Some one explain below mentioned code. so i can alter thing in future.

Note:- I dnt have Much Knowledge About Macro coding.


Code: 
Sub/ parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 4
Set ws = Sheets("Master")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:G1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

Thanks & Regards,
 

Attachments

  • Sample.xlsx
    10.1 KB · Views: 8
1. Change title line in the code to
Code: 
title = "A1:I1"

2. Yes, see attached for example

3. Yes, protect/lock all cell except for data entry range. Then change "...EntireRow.Copy..." line to
Code: 
ws.Range("A" & titlerow & ":I" & lr).Copy Sheets(myarr(i) & "").Range("A1")
Note change inside .Range() and removal of EntireRow.
See attached.

4. Yes. But it really depends on your actual data.
If you have too many unique resolution number, it's going to cause issue no matter how well the code is written.

5. This is probably one of more efficient way to do this. Though there's almost always room for improvement. One thing I noticed is that this code didn't declare vcol correctly and it's declared as variant type instead of integer. It's common mistake and not a big deal though in this case.

Recommendation:

You have cell formatting on very large area. Avoid it as it bloats workbook size and memory usage. Instead, go to View tab in ribbon tool and remove "Grid". Then add formatting to only areas that you need it.
See attached.

FYI - Sheet is protected, but without password (added lines in code to unprotect and protect the sheet. Add password between "" as needed).
 

Attachments

  • Sample.xlsb
    38.3 KB · Views: 12
Thanks For Providing improved code.
Now to run fast this code i need to add screenupdating on off.

there will max 1-15 Resolutions.

One more thing why you saved it .xlsb extension.

What is reason that you putted code in Module1.

I have putted code to show message on opening and closing of file on Thisworkbook.
 
.xlsb is my default save as.
For macro enabled file, you can either save it as .xlsb or .xlsm.

Few advantages to .xlsb:
1. Reduced size (benefit is more pronounced as data size increases)
2. Faster load time and save time (since it does not need to parse xml)
3. Supports formula above 8192 character limit

Some disadvantages:
1. You will need to save as xlsx, xlsm or other format before you can customize ribbon
2. Some older version doesn't fully support it
3. Many 3rd party software won't recognize this file format

In most cases, disadvantages #1 & 2 is non issue for me (all company pc has compatible version) and I use csv to import data into 3rd party software.

As for why the code is in Module1. That's where standard code should go. Unless you are triggering the code with Workbook/Worksheet or other events.
 
Hello Chihiro,

I have One more error in above Macro.

Result on new sheet 1 show copy of master sheet.
but I want data of resolution 1 only on sheet 1.

Where is error i also dont know.
 
Can you upload the workbook where you observed this error? Code should not be copying master sheet as whole, and I can't replicate your error.
 
Here is file on which am running code.
if it ask for password 1234.

check result of sheet 1.
 

Attachments

  • Sample1.xlsm
    27.9 KB · Views: 7
One more question.
I have disallow selection of protected cell while locking cells.
also inserted in macro code to while re-protecting. below mention code.
Code: 
ws.protect ""
EnableSelection = xlUnlockedCells

after running code protected cells are not allow to select. but when i close excel and save same file and reopen it protected cell can selected. that time i run code than in result protected cell selection is disable.

I want disable protected while opening excel sheet.
I have deselected cell selection box while inserting password.
 
Set up protection option in regular menu. Leave out any option argument out of the code.

This is probably easiest to manage.
 
You must log in or register to reply here.
Back
Top