Taking data from one tab and splitting into two based on values of cells

wao5012

New Member
Joined
May 7, 2018
Messages
32
Good Morning All,

I am wondering if it is possible to take the code below and add a next step. In this step i want to take the data from sheet 2 (Tab 2) and split it into two additional tabs called "s breaks" and "c breaks".

So, if the cells in column "N" contain "Working with Client" or "Expecting Transaction on Overnight Feed" they should be placed in the "c breaks tab" and everything else should go into the "s break tab".


Sub Copy()


Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = "C:\Users\woswald\Documents\Castlelake"
Filepath = FolderPath & "*.xls*"
Filename = Dir(Filepath)
Dim erow As Long, lastrow As Long, lastcolumn As Long


Do While Filename <> ""
If Filename <> "Investran_Consolidated.xlsm" Then
Dim wb As Workbook
Set wb = Workbooks.Open(FolderPath & Filename)
For counter = 1 To 2
'Sheets("Sheet1").Select
wb.Worksheets(counter).Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy


'Sheets("Sheet1").Select
Workbooks("Investran_Consolidated.xlsm").Worksheets(counter).Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActivateSheet.Paste
Next counter
Application.CutCopyMode = False
wb.Close False
Filename = Dir()
End If
Loop


erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select


End Sub
 
Last edited:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,099,331
Messages
5,467,984
Members
406,562
Latest member
tobruk

This Week's Hot Topics

Top