Create sophisticated loop

[Sunny007]

Dear guys,

im currently struggling with creating a vba code for the problem displayed:

Please create a temp_sheet, transfer the values and formatting from the first sheet found with the prefix "raw_" and apply a text filter in the temp_sheet.
Find the selected range in sheet "table 1" in column A and use the neighboring values in column B as filter criteria in the text filter in column B of the temp_sheet.

table 1 looks like this (user selects all File A cells)



Column A
Column B





File A
454/456zzuuCd/452





File A
100-100





File A
Advc9873





File B
4545






then the temp_sheet should look like this



Column A
Column B
Column C
24-06-24
24-06-25
24-06-26
24-06-27


SheetB
454/456zzuuCd/452
Travel
0,5





SheetB
100-100
Training

-1,7




SheetB
Advc9873
Travel



8












In the next step, i want the content of the cells (starting from column B and row 2) to be inserted to the sheet, whose name is in column A. When inserting it to the respective sheet, i want the correspondending column header to be considered, because the arrangement of columns of temp_sheet and e.g. SheetB deviates from another.

keep in mind, that
1) table 1: the selected range in table 1 can consider out of more then >50 cells
2) table 1: the structure of neighboring values in column B can deviate in terms of string (number/values/letters)
3) temp_sheet/SheetB: the amount of columns can be more >100 columns, but i want everything to be considerd, if the criteria matches
4) in some cases, temp_sheet can have columns headers, that dont exist in SheetB. Those columns should be skipped.
5) once the values have been successfully transmitted into SheetB, i want temp_sheet to be cleared and the content of "raw_" sheet no.2 to be inserted and to repeat everything like with the first "raw_" sheet. I want to process all "raw_" sheets, one by one.

Can someone help me?
Thank you very much!!

[June7]

If you have struggled with this, what code do you have so far?

Might help to provide file for analysis.

[Sunny007]

Unfortunately, my VBA knowledge is very poor, which is why I cannot create any code on my own. The tables look like the ones described in the post, except that they have more rows and more columns with date values. If you could help me, I would be extremely grateful!

[June7]

I am mainly an Access user. Don't code much in Excel.
Whatever you are doing in Excel, I would probably do in Access.

So you haven't even attempted anything. Not much of a struggle.

I am not interested in building a workbook. If you can't bother to provide existing file, my incentive to help is fading.

[Sunny007]

I am not interested in building a workbook. If you can't bother to provide existing file, my incentive to help is fading.

Fair point! I've attached the Workbook. I'm looking forward to seeing your solution. Thank you very much in advance.
FYI: keep in mind, that the name of the source sheet / string after raw_ can vary. I could be also named "raw_MasterC"



I am mainly an Access user. Don't code much in Excel.
Whatever you are doing in Excel, I would probably do in Access.



Due to various circumstances, I have to stick to excel.

[Gasman]

Sounds like homework to me?

[Sunny007]

Sounds like homework to me?

No, it's not homework. What school would give its students such complex tasks? Anyway, I don't understand what you're trying to tell me?

[Paul_Hossler]

No, it's not homework. What school would give its students such complex tasks? Anyway, I don't understand what you're trying to tell me?

The rules of the forum don't want the members to DO any homework for students, but only to HELP or SUGGEST ways for students to do it for themselves so that they will learn.

Sometimes we get questions that are almost like "Do #7 - Write a macro that wil ....". You get the idea

Sometimes the question is a little blurred

Not homework .. fair enough

[Paul_Hossler]

1. The pivot table is from "'\Users\BezabihEmanraw_SheetB!$GB$1684uel\Desktop\BP Überarbeitung\[workbook.xlsm]BP_WBS_export'!$A:$F"

2. 'RawSheetB has Col A added. I assume that a manual add

3. SHeet B has ...

Column A Column B Column C Column A Column B Column C Column A Column B Column C Column A Column B Column C WBS / Order number=

twice, then a bunch of Act dates and then a bunch of FC dates (Actuals and Forecast?)

"SheetB" and "External' in 2 columns

WBS and the dates I get, but what goes in the other columns are a mystery.

4. How do you tell ACT from FC?

5. "I want to process all "raw_" sheets, one by one." -- everything into one worksheet or seperate ones.

It might be easier to just start with your export sheet / file(s) (probably a CSV??) and format your output, when needs better definition

[Sunny007]

Hey Paul,
thank you for your comment.
1. please ignore the link. The source data is a list. the pivot is simply a support to get the data into the right shape (for the final target-sheet) -> like cross table. The Temp_sheet is a support to filter the values based on the criteria mentioned above (because you can not apply a textfilter on a pivot table).
2. correct, it was inserted manually
3. the header in row 2 in the temp_sheet is corresponding to the header in row 2 in the target sheet, ignore row 1 in both sheets
4. Its not necessary to differentiate between ACT and FC when transfering the data, so please ignore the line one in the target sheet
5. seperate ones. Currently, there is only one raw sheet. But in the future there could be more raw_ sheets, and the all should then be processed (each of them in a empty temp_sheet)

[Paul_Hossler]

I'd like to start with a file of the actual rawest input data, say 100-200 rows with a variety of variables

[June7]

Looked at workbook.
I do not understand your stated requirements.
I do not understand SheetB. Why does it not horizontal scroll?

Need someone more adept at manipulating Excel to figure this out.

[Sunny007]

This Post can be closed, i've implemented an easier solution on my own. Thanks anyway :)

[Aussiebear]

Hmmm... What solution did you arrive at? It would be somewhat educational given the initial inquiry was a little vague and those people trying to assist where clearly restricted by a baffling worksheet.
layout description. For future reference, since we cant see over your shoulder at your workbook, either supply a sample workbook or be very descriptive when first asking for assistance. The fact that we are 14 posts into the thread and people still don't understand the issue is clearly indicative that the query is poorly explained or badly layout.

We would love to assist you, but you need to be a little helpful yourself.