Variable being used in different module VBA

[noellebert]

'm very new to VBA and I need all the help I can get. Module 1 counts the numbers of cells with integers in the first row starting at C1 (A1 and B1 are titles)in the 'LLP Disc Sheet'. The number of cells for this specific worksheet is 9. However, 9 is not always the number. Sometimes the number is 1, 2, 3, 4, etc. It just depends if the user fills in those cells. I'm trying to store that number 9 to use in Module 2.

Module 2 produces copies of an entire sheet called 'MasterCalculator', which I plan on renaming each sheet produced to the Cell values that were counted in Module 1, Row 1 of 'LLP Disc Sheet' starting at C1. The number of copies produced must match the calculation in Module 1 (Which is currently 9).

I can't seem to figure out how to reference the variable 'lc' in the t3() module in the test() module. The number of copies of the MasterCalculator Sheet is inaccurate.

MODULE 1

Public lc As Integer Sub t3() Dim lc As Long, sh As Worksheet
Set sh = ActiveSheet
With sh lc = Rows(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count - 1
End With
ThisWorkbook.Save
End Sub

MODULE 2

Sub test()
Dim sh As Worksheet
Dim last_is_visible As Boolean

With ActiveWorkbook
last_is_visible = .Sheets(.Sheets.Count).Visible
.Sheets(Sheets.Count).Visible = True
.Sheets("MasterCalculator").Copy After:=.Sheets(Sheets.Count)
Set sh = .Sheets(Sheets.Count)
If Not last_is_visible Then .Sheets(Sheets.Count - t3.lc).Visible = False
sh.Move After:=.Sheets("LLP Disc Sheet")
End With End Sub

[maras_mak]

Use tags around the text to improve the readability of the code.
It is not possible to check - no attachment.
Maybe like this ...
Module 1

Please Login or Register  to view this content.

Module 2

Please Login or Register  to view this content.

[Keebellah]

If you need lc as a constant variable then you must declare it as such

Outside the Macro in the VBA project you declare lc as integer, well make hta long and remove the dim lc which sets it back to 0

[noellebert]

So I got my code to produce the correct amount of sheets. It's to equal the number of cells in row 1 starting at column C in the 'LLP Disc Sheet', which is 9. This number changes whenever the user deletes cells in that row or add more cells.

I now want to rename each sheet produced (the 9 sheets) to equal a cell value in the first row of the 'LLP Disc Sheet'. For instance, one sheet will be named 77777 the other sheet is 66666, 55555, and so on. I keep getting an error whenever I tried to do ActiveSheet.rename

Lastly, I want to change B1's formula in each sheet of the 9 sheets produced to match the name of the sheet. So I tried to do find/replace and still had an error.

Please Login or Register  to view this content.

I've attached the excel file.

MODULE 1

Please Login or Register  to view this content.

[Keebellah]

Which sheets do you want to rename?

[noellebert]

After running the macro I have in place it will create 9 'Master Calculator' sheets. I'm trying rename those sheets 77777 (c1) 66666(d1) 55555(e1) 44444(f1) 333333(g1) 22222(h1) 111111(i1) 88888(j1) 999999(k1) these values are found in the 'LLP Disc Sheet'. Each time I run this macro there will be different number in those cells and there might be less or more cell filled in that row. I'm having a lot of trouble to figure out a loop that will rename the sheets according to the cells.

[Keebellah]

Replace your code with this code:

Please Login or Register  to view this content.

It can only be run once or else you get errors that the sheet already exists.

[noellebert]

It only now creates 4 sheets (44444, 55555, 66666, 77777) instead of 9. But it names those 4, so thank you! Trying to figure out why the for loop stopped at those 4.

[Keebellah]

That’s very simple check you own code. The loop you coded does that you only have 4 formulas that are counted, I thought this was intentional
I’m it going to check why you use that way to calculate the loop count

[Keebellah]

This is the loop I'm talking about:

Please Login or Register  to view this content.

This only adds up to 4 but your original code just said Fit i = 2 to Range....

But you did not indicate which worksheet,, anyway this is the reason.

When you are running code, DO NOT ASSUME the active worksheet to avoid errors always indicate which and where it applies.

[Keebellah]

Edited your code and added a progress indicator in the StatusBar so you know it's busy

Please Login or Register  to view this content.

[Keebellah]

I can already see your next question coming up

[Keebellah]

Here's my file with the corrected code and progress indicator.
Press the Smiley to run.
Remember you can only run it once or else you have to delete the created worksheets first.
BTW, How're you guys doing in Florida? Hope all's well

[noellebert]

This is perfect! The status bar update is a nice addition and I see how my count was off. Thank you so much And Florida is hanging in there still continuing to quarantine. Hope all is well in the Netherlands.

[noellebert]

So I'm still trying with this. I'm working on the second part of the question - how change B1's formula in each sheet of the 9 sheets produced to match the name of the sheet. I was hoping to involve it in the loop after naming the sheet.

Please Login or Register  to view this content.

I wanted to create a wsCol = 1 then do wsCol= wsCol + 1 like you did above with shCol, however, I wasn’t sure how to replace that in the cell formulas

[Keebellah]

You'll have to be very specific with your explanation.
What formula is the one related to which (new) worksheet?

[Keebellah]

Okay, I found out where the formulas are. Cells B1 and M4
I modified the VBA code and added two extra macros to stop and reset all events.
The process now runs inside 2-3 seconds
There are still REF errors but I cannot seem to place them since there is no reference.
Hope this gets close to what you wanted.

[noellebert]

Thank you so much! ☺️ It worked perfectly when I ran it against the full version on my side. I hope I can finish this project with less questions. Thanks again for the big boost! Very much appreciated

[Keebellah]

Glad it worked.
You know where to find me when you've got questions.

[noellebert]

Hi again! I’ve been working on other parts of the project, but I’m stuck with another task. I’m trying to add a column for each engine in the ‘Revenue w_Risk Projection’. The columns are already there because I put them in manually, but I’d like the page to be empty until I run the macro and that’s what it will look like after. All of the information there will be taken from the ‘LLP Disc Sheet’, which the formulas show. For instance, C1:D42 cells will be for the first engine. The second engine if there is one (in this case there is) will go in cells E1:F42.

The formula most used in the columns is the Index Match function. Most of the information matches the corresponding engine in the ‘LLP Disc Sheet’ other than Row 34 and 35 in the ‘Revenue w_Risk Projection’. Rows 44 plus will remain on the ‘Revenue w_Risk Projections’. Those have to be added manually no matter what. I’d like this to be a separate macro and not involved in the other one you really helped on…. Unless it makes sense to combine them.

Please Login or Register  to view this content.

I of course appreciate any help you're willing to give. Thank you!

[Keebellah]

Can you post the sample file again with what you have and what you want?
Makes it easier then having to dive into the file and look it up.

[noellebert]

I highlighted the columns that will not be there before you run the macro in "Revenue w_Risk Projections". After the macro is ran those columns will be there. I'm not sure how to properly translate the excel formulas into VBA Code.

I attached the spreadsheet.

[Keebellah]

Okay, I'll see if I understand what you want to do.

If I understand you correctly you want to make the formulas work with the new worksheets. Correct?

One simple thing (that's the way I start when it come to formulas), is to start the macro recorder and then while recording select a cell with the formula and click it.
after that select another cell (or formula) and stop the macro recorder.

If you open the VBA editor and look at the macro, probably stored in Module1 (or the next number in sequence) and it's named Macro1 or another number (if you didn't give it a special name) and you'll see how a formula looks in VBA, then try to picture it looking at the result in Excel sheet

But, I'll see if I can help, have to understand what it is you're doing to be able to get the correct logic

[Keebellah]

See here, selecting cell E4:

Please Login or Register  to view this content.

Your macro example you say value but that's not correct as you van see, it's not a value but a formula and all the quotes around it

The R an C values as the R for row and C for column
The number is a reference to the column if in square brackets it's a hard reference like when you use the $ signs

[Keebellah]

So it's the whole column in where the formula refers to the new sheet numbers/

[noellebert]

Yes. If there are only two serial numbers with its info listed in the 'LLP Disc Sheet' there will only be two serial numbers and its info with the formulas listed in the 'Revenue w_Projections' sheet. If there are 5 or 7 serial numbers/columns in the 'LLP Disc Sheet' then the 'Revenue w_Projections' sheet will match it and have all the formulas. I want to be able to run the macro and have all the info there without putting it in manually. The number of serial numbers always changes so the column amount in the 'Revenue w_Projections' sheet will change with it.

Ohhh, I haven't tried recording before.

[noellebert]

yes it's the entire column

[Keebellah]

Recording your first macro is the first step all of us went though and once you start grasping the idea you'll see it's not at all difficult, it's just 'translating' you thought to code

[noellebert]

Thanks! Then I'll put it through a loop so it counts the columns to know how much to bring over? Because each time will be different

[Keebellah]

I looked at what you want and the formulas you have:

In the code block I placed two columns with the rows of cells that apply
If you look carefully the rows agree up to row 31
Then the row numbers differ between the left column and that in the LLP Disc Sheet

Please Login or Register  to view this content.

What I don't see is what do you need me (or the macro to do?) this seems okay since it references the LLP Disc Sheet and not the sheets that will be created by the macro, or am I missing something?

[noellebert]

Sorry I had to edit the worksheet to hopefully make this easier. This first macro you really helped with me is a loop that counts the columns in the ‘LLP Disc Sheet’ to make that many more sheets and fills in two formulas as well as name the sheet. This next macro I’d like to also count the columns in the ‘LLP Disc Sheet’ to make new columns in the ‘Revenue w_Risk Projection’ and add formulas. I think there has to be a count, because sometimes there will be less columns or more columns to add depending on how many serial numbers are on the ‘LLP Disc Sheet’. I’ve attached an Excel sheet what it will look like before you run the macro that creates the new columns. Then when you run the macro ‘Column’ all of 77777 is then shown with the correct formulas in the ‘Revenue w_Risk Projection’.

I’m confused on what to do with my loop to make it fill out the rest of the columns for the other serial numbers on the ‘Revenue w_Risk Projection’. Serial numbers 66666, 55555, 44444, 33333, 22222, 11111, 88888, 99999 need to be added. It also needs to work if a user adds another serial number and runs the macro again. It has to add that serial number to the already 9 columns.

Please Login or Register  to view this content.

[Keebellah]

Hi, I’ll download the file later and take look.
Let me ask one thing, you want the use to run the same first macro when a new serial number is added this means that a let’s say a xxxxxx or a zzzzzz sheet might have to be added, what do you want the macro to go with 77777 or one of the already previously added sheets? Skip, refresh (which will mean recreate )
I’m sure you don’t want your work to het overwritten, must te user be prompted?
You see all actions have consequences and have to be taken into account

[noellebert]

Like how the newly created sheets in the first macro have to be deleted before you run it again... these columns would have to be cleared too if a new serial number is there. I just want to know what I should do to my macro above to include all of the other serial numbers no matter how many there are (even though the max will probably be 15). Since when I run it only 77777 appears.

[Keebellah]

You loop is okay for what goes the LLP sheet
But here is where you're forgetting something.
All the references to Activesheet.Range("Cx") keep referencing to column C but here you need an extra variable to hold the column letter or number
Then you need to adapt the formula there too.
To write it down here will will take too long.

I'll work on it but for starters try the macro recording again.

Do it for the to top parts. Start the recorder

Select C1 and place the formula
select C4 to C7 and do the same, then select column D and repeat it referencing to the llp next column and do it for one more.
After that stop the recorded and open the vba editor and look at the macro and you'll see what I'm pointing out.
I'll pick it up later today but it's fun to do.

[Keebellah]

Okay, here's what I've been able to put together.
The two sheets have a Smiley
Give it a run.

[Keebellah]

One more thing, there are still unresolved references in your file and you should treat your formulas nicer.
Make sure they don't return errors and so, use the functions like IFERROR or ISNA etc.
I did this for most of them for you but you should try that too, makes it visually more 'friendly'

[noellebert]

This is wonderful. I understood what you meant by a second variable (once I recorded), however, wasn't sure how to store it. So if I want to do the same thing with rows instead of columns I can use a string to hold the passed rows. Assuming I use strings for addresses. Thank you. Your code is very easy to read and helps me a lot. I learn a lot from it. Again, thank you so much!

[Keebellah]

You're welcome.
Glad to have been able to help and inspire you.
Happy coding.