VBA Loop and respective worksheets

[ItalianPlatinum]

Need some help or pointed in a direction to handle a roadblock I have on my VBA. See below:

in laymans terms I am setting a range, clearing the macro, running a macro with the set range, then repeating. The part I need to include is before going to the next range it needs to transfer it its respective sheet. That part I don't know how to map the range to its respective sheet.

Ranges are like this: A####, B####, and etc.
Sheets: A, B and etc.
So A#### with Sheet A and so forth

' Run loop for range
i = 1
Do Until Sheets("Loop").Range("FILTER").Offset(i, 0) = ""
FILTER = Sheets("Loop").Range("FILTER").Offset(i, 0)
Sheets("Security").Range("REQ") = FILTER
Call Clear
Call SECDIS
i = i + 1
Loop

Any help is warranted. i have 18 ranges. so it seems tedious and not very efficient to code that all out 18 times to accomplish.

 

[Rijnsent]

Hi IP,
I don't fully get your issue, but hopefully this will point you in the right direction (mockup/dummy code):

i = 1
c = Chr(i + 64) '-> ascii code, 65 = A, 66 = B, etc.
Rng = Sheets(c).Range(c & "_hello")  'Put some strings together to make the range-name

Cheers,
Koen

 

[ItalianPlatinum]

So that will assume each sheet is a letter right? A, B, C, D? Hmm so my sheets could be numerical 0-9, or alhpa A-Z.

Let me try to better explain the ask as maybe i didn't do a good enough job explaining cause i think if understood it may be an easier approach than I might be even thinking about and you guys can open my eyes on it:

I have a macro Call SECDIS that extracts from a core system. The data is so large that it reaches the excel row limit if I run it with ALL no filters/limits. But I can limit how the system extracts the data by setting filters. i.e. Sheets("Security").Range("REQ") = A####### - therefore that is why I did a loop. Set that filter on that sheet, Clear that sheet, then run that data extract (Call SECDIS).

BUT the problem with the loop is that I need to transfer that data to a sheet before I restart my loop moving onto B###### and etc. So I created and set predefined sheets that will replicate what to expect, put them on a helper sheet, and that is where I am stuck is moving the data off of that sheet ("security") onto either its respective sheet "A" - i don't care if i even create the sheet as we go just a way to have the data on a sheet i can reference later to set formulas for reporting etc. I hope that helps and doesn't further confuse.

Column A is the sheet names - I get that from the core system of eligible filters those could change, column B is how the filter is used by the core system, column C is how i set the sheets in my VBA. I had to black out some names cause they reference client names.

C

 

[Rijnsent]

So If I get it right the data you're looking for is on sheet "Security" after you run "Call SECDIS"? And will need to be copied to e.g. sheet "A"?
This code does something in that direction, I took the A1:D100 range as an example, but you probably have to change that for it to work.

' Run loop for range
  i = 1
  Do Until Sheets("Loop").Range("FILTER").Offset(i, 0) = ""
    FILTER = Sheets("Loop").Range("FILTER").Offset(i, 0)
    SheetName = Sheets("Loop").Range("FILTER").Offset(i,-1).Value 'Assuming from your screenshot it's in the column left of the filter
    
    Sheets("Security").Range("REQ") = FILTER
    Call Clear
    Call SECDIS
    'Copy-paste the data
    Sheets("Security").Range("A1:D100").Copy
    'Or maybe you'd need to find the maximum row:
    lastRow = Sheets("Security").Cells(Sheets("Security").Rows.Count, "A").End(xlUp).Row
    Sheets("Security").Range("A1:D" & lastRow).Copy
    Worksheets(SheetName).Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode=False  'Clear the copy-paste memory
    i = i + 1
Loop

 

[ItalianPlatinum]

So i am getting an error on this row. Since my original post mind you I changed sheet name loop to that Cus one. I set the worksheet below to reference.

Dim i As Long
Dim SheetName As Worksheet

SheetName = WsCus.Range("FILTER").Offset(I, -1).Value 'Assuming from your screenshot it's in the column left of the filter

when I hover over it says SheetName = Nothing

Set WsSec = Sheets("Security")
Set WsCus = Sheets("Cus")

I see everything exists to pull according to your code too

 

[Rijnsent]

You left out quite a bit of code, which again makes it hard to see what's going wrong, but it might be this:

Dim SheetName As Worksheet
'Should be:
Dim SheetName As String
SheetName = WsCus.Range("FILTER").Offset(I, -1).Value 'This basically reads a value as a string, not as a worksheet-object
'Here: Worksheets(SheetName).Range("A1") -> it uses the string as the name of the Worksheet

 

[ItalianPlatinum]

Ok I see thanks for that. So the filter is starting on row 2. But my data starts on row 1. FILTER is set to B1. So the first loop went to H.

' Run loop for range and apply to its sheet
  i = 1
  Do Until WsCus.Range("FILTER").Offset(i, 0) = ""
    FILTER = WsCus.Range("FILTER").Offset(i, 0)
    SheetName = WsCus.Range("FILTER").Offset(i, -1).Value 'Assuming from your screenshot it's in the column left of the filter

With WsSec
    .Range("4VALUE") = FILTER
    .Activate
End With

    Call Clear
    Call Sec
    
    'find the maximum row:
    lastRow = WsSec.Cells(WsSec.rows.count, "A").End(xlUp).row
    WsSec.Range("A10:L" & lastRow).Copy
    Worksheets(SheetName).Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False 'Clear the copy-paste memory
    i = i + 1
Loop

 

[Rijnsent]

In your code, change i=1 to i=0 , than it should start at the first line.

 

[ItalianPlatinum]

Thank you. So while looping it errored when it couldn't find a sheet. Must have been a new one. How do I wrap that in error handling? So if the sheet doesn't exist to create it or to at least warn the user and exit the sub.

 

[Rijnsent]

Google search is your friend: Check if sheet exists, create it if it doesn't