Posts by Arby

Yes will be leaving it as is for now, but the data included on the sheet may change going forward so if the column order changed, then was checking my code interpretation was correct if needed to be changed.

Genius for the addition....you are indeed correct on your assumption

Great, Thanks again vcoolio.....

Am I right in saying that if the master data list changed ie had extra columns in, all that would need tweaking would be lines 13 & 14?

.AutoFilter 7, ar(i)  '---> The number would change to the corresponding number of the column in which the data to be filtered by is in
.Columns("A:G").Offset(1).Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2) '---> the range of the columns would be expanded as required ie A - AA

The spreadsheet is to be used by people who are not spreadsheet savvy....

Quote from Arby

In a nutshell the master list is the database of all youths that attend sports training. The sub worksheets need to be populated by age ie U12s on one and U14s (or anyone with an age older than 12) on the other . These sub sheets will then be the ones printed to form the base of the termly registers.

I am aware I could just use the filter buttons on the master list to separate out those rows required, but a couple of less IT literate people will be in the main using the information so it needs to be foolproof and as easy to use as possible, hence the requirement to input into the master and then press one button for that info to be magically split per age category.

Thanks in advance

The only change is rather than the data being copied/split by age group ie U12s, U14s as it was, the master list of details needs to be split by the data in Col G, which is now either an S4, S5 or S6. It is this line in the code that I'm having trouble to redirect to look for that information then to split the relevant rows of data to the relevant sheet.....

example attached

TIA

Arby.xlsm

Hi

The above code has been working great (no surprise as you are all genii )

However I am now needing to use the same (similar) code, but instead of it being split into 2 sheets depending on the data in Col G ( It was on age) into 3 named sheets based on the data in the column, which has changed.

The data in the column is now either an S4, S5 or S6.

So on the click of the button take the master data list and copy it to the relevant worksheet based on if the value in Col G is S4, S5 or S6

I have tried to tweak the original code, I know the line that needs changing, but I'm not hitting the lucky jackpot it would seem.

I can get it to copy it to one of the named sheets, but the data doesn't split up.......

Original code below, and the line that I know I need to change is Line 11 fat the IF function part

Any help would be very much appreciated as always

Sub TransferData()


 Dim ar As Variant
 Dim sh As Worksheet, ws As Worksheet
 Dim i As Integer
 Dim lr As Long
 
Set sh = Sheets("Master List")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
sh.Range("X3:X" & lr) = "=IF(G3<=12,""U12s"",""U14s"")" '---->Formula placed in Column X of Master List to define sheets in the array.
ar = sh.Range("X3", sh.Range("X" & sh.Rows.Count).End(xlUp))


Application.ScreenUpdating = False


For i = 1 To UBound(ar)
 Set ws = Sheets(ar(i, 1))
 ws.UsedRange.Offset(1).ClearContents
 sh.Range("X2:X" & lr).AutoFilter 1, ar(i, 1)
 sh.Range("A3:G" & lr).Copy
 ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
 ws.Columns.AutoFit
 sh.[X2].AutoFilter
Next i


sh.Select
[COLOR=#FF0000]sh.[A1].AutoFilter[/COLOR]
sh.Columns(24).ClearContents


Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"


End Sub

Awesome, thanks a lot, much appreciated!

Thanks, that would be fab too! This is the code being used to split the data by specified column

Sub Splitdatabycol()
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
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set WS = xTRg.Worksheet
lr = WS.Cells(WS.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = WS.Columns.Count
WS.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
WS.Activate
For i = (titlerow + xTRg.Rows.Count) 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
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
WS.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
WS.AutoFilterMode = False
WS.Activate
Application.DisplayAlerts = True
End Sub

Thanks, works great

Yes will bear that in mind!

Hi Guys

I have a workbook that has been split into numerous sheets according to specified column data via VBA

However I need the 1st row of each sheet to have the filter drop down options available. I could go through each sheet individually, highlight the top row and click filter, but as I have about 30 sheets each with numerous columns that's rather time consuming.

Can someone help me with the coding to do that please. I don't want to filter the data via vba, I just want each sheet to have the top row with the filter drop down buttons applied.

TIA

Marvellous, that is perfect. Thank you so much.

cheers, I was almost there, it was the " " that was getting me, I was thinking more along the lines of a blank or empty statement, didn't think to just have nothing between 2 sets of speech marks!

Just one thing I've noticed is....my master sheet has filters set on the column header title, as do the sub sheets. When the macro is run via the update button, the filter arrows then vanish from the master sheet.....is there anyway of preventing that from happening as I still need to be able to filter data on the master list as well as the individual group sheets

Thanks in advance

A

Vcoolio

Thanks a bunch, that is looking pretty much exactly what I was requiring and works perfectly.

Having run it I can see I really need the 12 YO's to be included in the U14 sheet rather than the U12s, so have tweaked the code to do that (know a teeny teeny bit of coding, but not enough to write macro from scratch).

At the moment there are a number of rows of data that do not have an age in the column, which get pulled across to the U14 worksheet.

Can you advise on what line to add at/after the IF statement to make the empty G rows be moved to a separate worksheet (lets call it no age)?

Thanks

Arby

Hi Guys

New to VBA, relatively adept with most other parts of excel.

I have got a macro that takes the data from 2 sub worksheets and copies that across to a master list at the click of a button, but I'm really needing the reverse. I'm needing a macro to copy data inputted into the master list into 2 separate worksheets in same workbook according to what age a person is.

In a nutshell the master list is the database of all youths that attend sports training. The sub worksheets need to be populated by age ie U12s on one and U14s (or anyone with an age older than 12) on the other . These sub sheets will then be the ones printed to form the base of the termly registers.

I am aware I could just use the filter buttons on the master list to separate out those rows required, but a couple of less IT literate people will be in the main using the information so it needs to be foolproof and as easy to use as possible, hence the requirement to input into the master and then press one button for that info to be magically split per age category.

Thanks in advance