Often I find myself working with a subset of the full dataset for experimentation, trials, and exemplification. For instance, my dataset may have 33,000 records but I want to work with about 100 records first in a separate sheet. Sure, I could (as most do) select the desired columns and continue to scroll down to the exact part of the dataset and ctrl-C but I’d like to spare my wrist and peripherals from unnecessary tasks like this whenever possible.
While there are thousands of formulas in Excel, this doesn’t exist. I’ve tried hacking with other functions to do the job, but it really wasn’t saving much time. The only way I’ve found is to write a short VBA program to do that.
However, I’m not fond of saving my Excel files as a macro-enabled XLXM files as it creates other security hoops and issues with multiple users who are non-developers. So, I created a VBA code snippet, saved it as a separate file, load it up for this automated task, then unload it and save the original Excel file as a normal XLSX file after it’s done its automation.
Here’s my code.
Sub CopyFromSheetXRows()
Dim New_Sheet As String
ActiveSheet.Select
Range(“A1:C100”).Select
Selection.Copy
New_Sheet = “_pastedData”
If (Sheet_Exists(New_Sheet) = False) And (New_Sheet <> “”) Then
Worksheets.Add().Name = New_Sheet
End If
Sheets(New_Sheet).Select
Range(“A1”).Select
ActiveSheet.Paste
End Sub
Function Sheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet
Sheet_Exists = False
For Each Work_sheet In ThisWorkbook.Worksheets
If Work_sheet.Name = WorkSheet_Name Then
Sheet_Exists = True
End If
Next
End Function
Explanation
When executed, the code will copy a range from A1 to C100 (100 rows and 3 columns) from the currently selected sheet. To suite your needs change the Range() argument accordingly.
Then it creates a new sheet called “_pastedData” (call it whatever you like by changing the New_Sheet variable assignment), but only if it doesn’t exist (if it exists, and we didn’t check for that condition, VBA will end ungracefully). If it exists (e.g. you ran this code already), it’ll simply use that sheet.
It pastes the copied rows and columns starting from cell A1 of the new sheet. Change the 2nd Range(“A1”) call to specify your desired target cell.
To check if the target sheet exists, I call a custom function “Sheet_Exists()” which takes the name of the sheet to look for, and returns TRUE or FALSE. TRUE if it was found, otherwise FALSE. To find it, the function goes through the entire workbook array of sheet names in it.
The main subroutine has a unique name: CopyFromSheetXRows(). Although it copies rows and columns, so call it whatever you like as long as it won’t conflict with other VBA functions.
The function is kept separate from the subroutine so we can call it from other modules as well.
That’s it. Hope this is helpful. I know I use it often.
