Solved

Create sheet and copy column data from existing sheet, then sort

Posted on 2013-12-24
11
306 Views
Last Modified: 2013-12-24
Hello experts,

I have a list of people with various certificate expiry dates (sheet Personnel_Data).

I would like to create a new sheet (Sorted_Data) within the same workbook at the press of a hot-key combo (ctrl-k?).
Rows 1-5 contain header information so the data begins at row 6.
Copy the relevant columns (A (names), G, I, K, M and O (dates).
Column A from Personnel_Data to Column A Soted_Data, G to B, I to C, K to D, M to E, O to F
Then sort data by date columns, to show earliest (closest to today's date) expiry date.

If possible, deleting sheet (Sorted_data) at workbook close.

Hope this makes sense!

Thanks
Andy
0
Comment
Question by:Negativ3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39737787
Should the Sorted Data worksheet have headings in row 1?  What row in in Sheet Personnel Data has those headings?  I am assuming row 5.

Tom
0
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 500 total points
ID: 39737826
Public Sub SortedData()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet

' Create Worksheet - Delete if necessary
  CreateWorksheet "Sorted_Data"
  
' Assign Worksheet wariables
  Set wsSource = Sheets("sheet Personnel_Data")
  Set wsTarget = Sheets("Sorted_Data")
  
' Header Data
  wsTarget.Range("A1") = wsSource.Range("A5")
  wsTarget.Range("B1") = wsSource.Range("C5")
  wsTarget.Range("C1") = wsSource.Range("D5")
  
' Loop Through Peronnel Data
  wsSource.Activate
  wsSource.Range("A6").Activate
  
  Do Until IsEmpty(ActiveCell)
    wsTarget.Range("A2").EntireRow.Insert
    wsTarget.Range("A2") = ActiveCell.Range("A1")
    wsTarget.Range("B2") = ActiveCell.Range("C1")
    wsTarget.Range("C2") = ActiveCell.Range("D1")
    ActiveCell.Offset(1, 0).Activate
  Loop
  
' Format Data and Sort
  wsTarget.Activate
  Columns("C:C").NumberFormat = "m/d/yyyy"
  Rows("1:1").Font.Bold = True
  Columns("A:C").AutoFit
  
  ActiveCell.AutoFilter

End Sub

Private Sub CreateWorksheet(sName As String)
Dim WS As Worksheet

Application.DisplayAlerts = False
On Error Resume Next
  Set WS = Sheets(sName)
  WS.Delete
  Set WS = Sheets.Add
  WS.Name = sName
Application.DisplayAlerts = True
End Sub

Open in new window


The above code should get you moving in the right direction.  I developed using a much smaller data set with the same principle.

Here's the logic:
Create the new worksheet (Target)
Copy the data with mappings to the target sheet using the loop construct.  I use the insert row option when add data so I don't have to be concerned with how many rows I will be moving.
Format the columns as necessary and activate the auto filter.

If this is moving you in the right directions -- let me know and I will look at the other parts.

Tom
0
 

Author Comment

by:Negativ3
ID: 39737973
Tom,

That looks fine so far. I'm not sure how to assign the code to work from a hot-key?

Thanks
Andy
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 6

Assisted Solution

by:ButlerTechnology
ButlerTechnology earned 500 total points
ID: 39737989
Hot Key are very straight forward.  You will need to have the developer ribbon available turn on.  In the code section of the Developer Ribbon, select macros.  Locate the macro in the list and select options.  You will see the area to assign a key board short-cut.

Tom
0
 

Author Comment

by:Negativ3
ID: 39738003
Very good Tom, got that working after amending the line:

Set wsSource = Sheets("sheet Personnel_Data")
to
Set wsSource = Sheets("Personnel_Data")

Thanks
Andy
0
 
LVL 6

Assisted Solution

by:ButlerTechnology
ButlerTechnology earned 500 total points
ID: 39738035
You could put some code in to remove the SortData sheet using the Open or BeforeClose options.  The down side is that your workbook will need to be saved as an XLSM as it will be storing macro.  This may or may not be important.

Are you the only one who will be running this code or do other team members need to run the code?

Tom
0
 

Author Comment

by:Negativ3
ID: 39738060
The workbook will be shared to one other team member currently.

Sheet removal is not necessary as a re-run of the macro over writes the existing, which is good enough.

I edited the code you provided as follows, and it seems to work so far...

' Header Data
  wsTarget.Range("A1") = wsSource.Range("A5")
  wsTarget.Range("B1") = wsSource.Range("G5")
  wsTarget.Range("C1") = wsSource.Range("I5")
  wsTarget.Range("D1") = wsSource.Range("K5")
  wsTarget.Range("E1") = wsSource.Range("M5")
  wsTarget.Range("F1") = wsSource.Range("O5")
  
' Loop Through Peronnel Data
  wsSource.Activate
  wsSource.Range("A6").Activate
  
  Do Until IsEmpty(ActiveCell)
    wsTarget.Range("A2").EntireRow.Insert
    wsTarget.Range("A2") = ActiveCell.Range("A1")
    wsTarget.Range("B2") = ActiveCell.Range("G1")
    wsTarget.Range("C2") = ActiveCell.Range("I1")
    wsTarget.Range("D2") = ActiveCell.Range("K1")
    wsTarget.Range("E2") = ActiveCell.Range("M1")
    wsTarget.Range("F2") = ActiveCell.Range("O1")
    ActiveCell.Offset(1, 0).Activate
  Loop
  
' Format Data and Sort
  wsTarget.Activate
  Columns("B:B").NumberFormat = "dd/mm/yyyy"
  Columns("C:C").NumberFormat = "dd/mm/yyyy"
  Columns("D:D").NumberFormat = "dd/mm/yyyy"
  Columns("E:E").NumberFormat = "dd/mm/yyyy"
  Columns("F:F").NumberFormat = "dd/mm/yyyy"
  Rows("1:1").Font.Bold = True
  Columns("A:F").AutoFit

Open in new window


Andy
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39738072
It should be simple enough to just share the code with the team member and have them put the code in their Personal Marco Workbook.  The sharing was a large number of team members, I would recommend embedding or creating a new sheet to hold the code.

Tom
0
 

Author Comment

by:Negativ3
ID: 39738073
Understood.

So how do I now sort all the date columns so the nearest date to today is sorted at the top?

Andy
0
 
LVL 6

Assisted Solution

by:ButlerTechnology
ButlerTechnology earned 500 total points
ID: 39738325
Sheets("Sorted_Data").Range("a1:C" & Range("a1").End(xlDown).Row).Sort key1:=Sheets("Sorted_Data").Range("C:C"), order1:=xlAscending, Header:=xlYes

Open in new window


Here's the last piece of the puzzle.  You will need to provide the last column -- in my case it was column C and you will need to provide the column that has the date ii once again it was column C for me.  You can change the order with xlAscending or xldescending.

Tom
0
 

Author Closing Comment

by:Negativ3
ID: 39738792
Thanks Tom, that seems to work perfectly.

I will close the question and open another one regarding formatting the cells in the date column depending on how close expiry is to today.

Andy
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question