automatically save some columns in another worksheet

S

steve

I have 6 worksheets with the same information for different age groups, but I
want each branch to send me a summary sheets so that it it easy for me to
make a large overall summary of the company.

Is it posible to have a system where data from the 6 worksheets will
automatically save to the summary worksheet? (I only need some of the
columns of data in the summary)

Thanks
Steve
 
S

steve

Thanks Ron, but I have to confess I have no idea how to use your tips! I had
to give up on the last solution you gave me for a different question. Is
there a page to explain how to use your examples?
I don't think that any of your examples really do what i want: From each of
the 6 worksheets, I want excel to automatically save columns A-G, I, K, M
onto a summary worksheet in the same workbook. That way I only have to move
one lot of data (the summary worksheet) to my overall summary sheet which
will be in another document.

Is that possible?
Thanks
Steve
 
R

Ron de Bruin

Hi Steve

Do you want to copy the data in the columns next to each other or
below each other.
to give up on the last solution you gave me for a different question
Click to expand...
Which question
 
S

steve

Hi Ron

Thanks very much form your time with this. This is a follow on from a
similar question that you very kindly answered before:

I have several branches that keep finess test records in 6 sheets in a
workbook, one sheet for each age group. I want to create a summary of every
single fitness test result. You answered and explained how to run MS Query
but I was concerned that it would be too cumbersome (40 branches x 6 sheets)
and there was a problem that each month when I wanted to add the next months
data it would overwrite the previous month's. You very kindly provided the
link to your webpage but I had no idea how to follow the instructions.

It occured to me that if I could save some of the columns of data from the
sheets directly onto a further summary sheet in that document each time it
was updated I would only have to MS Query to summarise 40 sheets (one from
each branch) instead of 40x6.

The data that I need from the 6 results sheets is in columns A-G, I, K, M
whichh I would want to appear on the summary sheet in columns A-G, and I-K
(the missing columns are gradings that i don't need in the overall summary
sheet - I only need the raw data). Once it has saved to the documnt's
summary sheet (it will only have to do it once as they will open a new
document each month) I will then be able to MS Query to summarise all of the
summary sheets. Hope that makes sense!!

Do you think this is possible or am I asking too much of excel??

Once again many thanks
Rgds
Steve
 
R

Ron de Bruin

Hi Steve

Try this in a test workbook.
It not copy the first row if each sheet(Header I think)
You can change that


Sub Test5()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy only the values
'or use the PasteSpecial option to paste the format also.


'With sh.Range(sh.Rows(2), sh.Rows(shLast))
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

End If
Next
Range("H1,J1,L1,N1:IV1").EntireColumn.Clear
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
 
S

steve

Thanks Ron. I think I need to go through this first then I will understand
your instructions.
Once again, thanks very much for your time
Kind regards
Steve
 
Top