Please Check my VBA code

[Meko1]

Hello
I would like a advanced VBA user to see my code pls.

I am just making simple if functions in VBA, but since I am new it is not that easy to me.

So the code is following:


If Sheet10.Range("N2").Value > 0 Then ActiveSheet.Range("B22").Value = 0.5
Else: Range("B22").Value = 0

If Sheet10.Range("M2").Value > 0 Then ActiveSheet.Range("B23").Value = 0.1
Else: Range("B23").Value = 0

If Sheet10.Range("L2").Value > 0 Then ActiveSheet.Range("B25").Value = 0.1
Else: Range("B25").Value = 0

If Sheet10.Range("J2").Value > 0 Then ActiveSheet.Range("B24").Value = 0.1
Else: Range("B24").Value = 0

If Sheet10.Range("K2").Value > 0 Then ActiveSheet.Range("B24").Value = 0.1
Else: Range("B24").Value = 0


So, am I correct, and while using else I want it to leave that cell with value 0.

And I have another issue, in other problem, while moving data from one sheet cells to another sheet cells, and those cells already have data inside, how to ask VBA if there is data go 1cell down and fill from there.

Tanks in advance.
Hope for your quick response.

 

[Roy_Excel_Island_Apps]

Hi Meko,

I would change it to:

With  ActiveSheet

If Sheet10.Range("N2").Value > 0 Then .Range("B22").Value = 0.5
Else: .Range("B22").Value = 0

If Sheet10.Range("M2").Value > 0 Then .Range("B23").Value = 0.1
Else: .Range("B23").Value = 0

If Sheet10.Range("L2").Value > 0 Then .Range("B25").Value = 0.1
Else: .Range("B25").Value = 0

If Sheet10.Range("J2").Value > 0 Then .Range("B24").Value = 0.1
Else: .Range("B24").Value = 0

If Sheet10.Range("K2").Value > 0 Then .Range("B24").Value = 0.1
Else: .Range("B24").Value = 0

End with

 

[eirikdaude]

My first and foremost advice to anyone starting out in VBA is don't use ActiveSheet, Select, ActiveWorkbook etc., unless you have a really good reason for it. It's bound to bite you in the *** sooner or later. For more details see e.g. here.

 

[My Aswer Is This]

I see your question and would like to know your overall objective.
It's much easier to help new users if we know your overall objective.
Having a half dozen if statements is not normally needed if we know the over all objective
And see you may even have a lot more of these and again it would be nice to know the end game.

Some times we can build a simple loop to do something like this if we knew the end result.
And I agree using active sheet is not a good plan.

I use things sort of like this
Is Sheets("Alpha").Range("A1").Value="Duck" then Sheets("Bravo").Range("A1").value="Bird"

And to find the next empty cells is easy if we know what column your sending your new data to.
I look forward to seeing back from you.
And else would mean do this. If you want to do nothing you do not need to use else.

 

[Meko1]

Hey,
Thank you all for the information, and I agree that using active sheet is not the best idea, but, in my case I am making a school excel workbook where I will have around 500 sheets, for each student and even more if I need them, so it is just impossible to use 500 different names for the macro, since I am using the same macro for every new sheet. That is why ActiveSheet works best atm for me.

Regarding finding new empty cells. So far this is code which I am using and as I mentioned I will have few hundreds of sheets so, the data needs to be filled from every ActiveSheet to Sheet1. As you can see first data transfer includes cells B4, C4, D4, E4. If I need some extra cells I will add them, now I am stuck with that I do not know how to ask VBA if they see data in those cells to go 1 cell down and fill those cells in the case B5,C5,D5,E5...

Sheet1.Range("B4") = ActiveSheet.Range("B6")
Sheet1.Range("C4") = ActiveSheet.Range("B7")
Sheet1.Range("D4") = ActiveSheet.Range("B5")
Sheet1.Range("E4") = ActiveSheet.Range("B12")


At the moment I am improving the new excel file for the school, I want them to work in an improved workbook, since the old one is mess, and wile renewing it, it will take hours, days to transfer all the necessary data from one workbook to another. So my solution is that using several macros, buttons, to cut the time at its maximum.
This is last problem I have in VBA and I am sure after it the file will be ready.

Thanks in advance,
Hope to hear from you soon.

 

[eirikdaude]

The obvious solution if you need to work on several sheets which look similar would be something like

dim ws as worksheet

for each ws in thisworkbook.worksheets
  if instr(1, ws.name, "sheet", vbtextcompare) > 0 then ' edit to match whatever criteria the sheets you want to copy from should fulfill
     Sheet1.Range("B" & Application.WorksheetFunction.Max(3, Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row)).Offset(1, 0)= ws.range("B6")
     etc.
  end if
next ws

You could also use an offset for each iteration of the loop to increase the offset by 1

 

[My Aswer Is This]

I hope you understand I'm just trying to help you and not disagree with the way you think you need to do this:

Creating a Button in 500 sheets and then expecting user to click each button is not the best way.

We can write one script that will run for all sheets and return the end result back to a master sheet
And this same script could run on any new sheets you may add.

So tell me. Is the script to look for the same cells in all sheets except for sheet named master?
The sheet named Master would be the sheet where all the values are returned to.
And we could even show you the name of the sheet when the returned value are from.

If something like this would work all you need if for one script and one button on the master sheet

Are you interested in this way?
And then if you want to modify the script you only need to modify one script

 

[My Aswer Is This]

Here is a script which should do all you want with no need for a button on every sheet.
Put this script in a button on your master sheet which I have named Master

The script will perform what you want on all sheets even new sheets.

Sub Check_All_Sheets()
'Modified 12/7/2020  9:39:01 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrowa As Long
Dim Lastrowb As Long
Dim Lastrowc As Long
Dim Lastrowd As Long
Dim Lastrowe As Long

For i = 2 To Sheets.Count
    Lastrowa = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Lastrowb = Sheets("Master").Cells(Rows.Count, "B").End(xlUp).Row + 1
    Lastrowc = Sheets("Master").Cells(Rows.Count, "C").End(xlUp).Row + 1
    Lastrowd = Sheets("Master").Cells(Rows.Count, "D").End(xlUp).Row + 1
    Lastrowe = Sheets("Master").Cells(Rows.Count, "E").End(xlUp).Row + 1
    
    With Sheets(i)
        .Range("B6").Copy Sheets("Master").Cells(Lastrowb, 2)
        .Range("B7").Copy Sheets("Master").Cells(Lastrowc, 3)
        .Range("B5").Copy Sheets("Master").Cells(Lastrowd, 4)
        .Range("B12").Copy Sheets("Master").Cells(Lastrowe, 5)
        Sheets("Master").Cells(Lastrowa, 1).Value = .Name
    End With
Next
Application.ScreenUpdating = True
End Sub

 

[eirikdaude]

@My Aswer Is This Your script will crash unless the first indexed sheet is the master one. Considering the number of sheets the OP indicates is involved you may want to turn off more than just screenupdating, these are the two macros I usually use for that...

Sub deactivate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    ' ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
End Sub

Sub reactivate()
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
    ' ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting
End Sub

 

[My Aswer Is This]

OK. I'm assuming the Master sheet is sheet 1
If that is not true let me know.

You did say:
Sheet1.Range("B4") = ActiveSheet.Range("B6")

Assume this means first sheet in workbook