• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need help with Vbe in Excel

Ama

Member
Hello,

I am trying to copy worksheets into one worksheet named combined but I need to keep sheet(1). Then I want to delete the sheets I copied. So far I have failed, the code I have is below but I think the file I have shared has the wrong code. the below code is what I am trying to get to work for Combined worksheet.

Sub CombineWorksheets()
Dim wsSrc As Worksheet
Dim wsDest As Worksheet
Dim rngDest As Range
Dim lastRow As Long
Dim destRow As Long
Set wsDest = Worksheets("Combined")
Set rngDest = wsDest.Range("A1")
Application.DisplayAlerts = False

'loop through all source sheets in this workbook
For Each wsSrc In ThisWorkbook.Sheets
If wsSrc.Name <> "Summary" Then 'all sheets except Summary
lastRow = wsSrc.Cells.SpecialCells(xlCellTypeLastCell).Row
wsSrc.Range("A1", wsSrc.Range("O" & lastRow)).Copy Destination:=rngDest
Set rngDest = rngDest.Offset(lastRow - 1) 'update the destination range
wsSrc.Delete 'delete the source worksheet without a prompt
End If
Next
Application.DisplayAlerts = True 'Turn prompts back on
End Sub
 

Attachments

  • National1.xlsm
    482.8 KB · Views: 8
Hi Ama,

Try below code:

Code:
Option Explicit

Sub CombineWorksheets()
Dim wsSrc As Worksheet
Dim wsDest As Worksheet
Dim rngDest As Range
Dim lastRow As Long
Dim destRow As Long
Set wsDest = Worksheets("Combined")
Set rngDest = wsDest.Range("A1")
Application.DisplayAlerts = False

'loop through all source sheets in this workbook
For Each wsSrc In ThisWorkbook.Sheets
If wsSrc.Name <> "Summary" And wsSrc.Name <> "Combined" Then 'all sheets except Summary
lastRow = wsSrc.Cells.SpecialCells(xlCellTypeLastCell).Row
wsSrc.Range("A1", wsSrc.Range("O" & lastRow)).Copy Destination:=rngDest
Set rngDest = rngDest.Offset(lastRow - 1) 'update the destination range
wsSrc.Delete 'delete the source worksheet without a prompt
End If
Next
Application.DisplayAlerts = True 'Turn prompts back on
End Sub

Regards,
 
Hello, I am trying to find current age from DOB in column D2 and checking which category each one is in (Junior, Senior, Master, Grand Master, Great Grand Master) then placing the answer in column J2. There are 3040 rows in the spreadsheet and I am trying to get the following code to do the job. Thank you for your help.

Sub AgeGroup()

Range("D2").Select
If (ActiveCell.Value <= 18) Then
Range("J2").Value = "Junior"
ElseIf (ActiveCell.Value > 18) Then
Range("J2").Value = "Senior"
ElseIf (ActiveCell.Value >= 40) Then
Range("J2").Value = "Master"
ElseIf (ActiveCell.Value >= 50) Then
Range("J2").Value = "Grand Master"
ElseIf (ActiveCell.Value >= 60) Then
Range("J2").Value = "Great Grand Master"

Else: Range("J2").Value = ""

End If
End Sub
 
Last edited:
Hi ,

This is one way of doing it. If you are not comfortable with this , we can write a different type of procedure , which is not a UDF.

Narayan
 

Attachments

  • Ama_Example.xlsm
    12.8 KB · Views: 4
Thanks Narayan but can we write a different type of procedure, Ama. I like the case procedure though.
 
Hi Narayan,

It doesn't like ag in my module but its works fine in yours.
Ok, it works in general not option explicit, but it only places Great Grand Master in the J column.

Ok did the Dim ag As String, it works. It suppose to work out the age and compare it to the Category of Junior, Senior, Master, Grand Master, Great Grand Master. It worked in another file with DOB but doesn't seem to work here. I must be missing something.
 
Last edited:
Hi ,

Have you gone through the code ?

You need to specify the data range which has the ages ; in my file , I have specified the range D2:D8.

Because I have used column D for the ages , I have used an offset of 6 to specify that the output should be in column J , 6 columns from column D.

Narayan
 
Hi Narayan,

How would i calculate the DOB from the current year using case to work out the age and compare it to the Category of Junior, Senior, Master, Grand Master, Great Grand Master.
 
Hi Narayan, I have attached a copy of the National3.xlsm The case procedure is in the module
 

Attachments

  • Copy_of_National3[1].xlsm
    788 KB · Views: 4
Hi ,

See your file. There was a mistake in the way I had written the Case statement ; that has been corrected now.

Narayan
 

Attachments

  • Copy_of_National3[1].xlsm
    806.3 KB · Views: 1
Hi ,

See your file. There was a mistake in the way I had written the Case statement ; that has been corrected now.

Narayan

Thank you, it works yeh, Narayan, can I use the same type of code to get the details for the Summary Sheet? The Summary sheet is going to be a main sheet to show extracted details calculated for example number of Paddlers, Sweeps, Drummers, Total. Then the number of Juniors, Seniors etc and total. Eventually i will connect the code to the buttons, but for now I am playing with the sheets code to make sure everything is
working.

SummarySheet.PNG

This is sheet(1) Summary Sheet.
 
Hi ,

The Summary data display can come through formulae ; there is no need for code.

See the file now.

Narayan
 

Attachments

  • Copy_of_National3[1].xlsm
    807.1 KB · Views: 6
Hi ,

Why would you want to do that ?

Coding all those formulae is not as straightforward as you think ; what is the problem if those formulae remain there ? You never need to bother about them , since the ranges referred to in them are dynamic , and as your data range expands or shrinks , the ranges will adjust automatically.

Narayan
 
HI Narayan,

You know the template in Summary, the cell next to Paddlers are the ones that are not listed in the Combined sheet, so the blank cells in the Role column are presumed paddlers and need to be counted, how do I do that, do I use the formula already put in the cell you placed and modify it to suit? Ive added a link to the excel file

=COUNTIF(OFFSET(ListofLastNames,,MATCH("Role",Combined!$A$1:$Z$1,0)-1),LEFT(Summary!A7,1))

http://forum.chandoo.org/attachments/copy_of_national3-1-xlsm.13861/
 
Back
Top