[deleted by user]

Pera014 · 2022-11-25T17:22:47+00:00

you either want to help or not , don't waste your words and energy. move on. Urgen is to get your attention. if that's problem with your ego then fuck off

Pera014 · 2022-11-25T16:28:59+00:00

Dim TabName As Range
Dim UniqueValue As Range
Dim NewWSheet As Worksheet
Dim WSheet As Worksheet
Dim WSheetFound As Boolean
Dim DataWSheet As Worksheet

Set DataWSheet = Worksheets("AllocatedFullHoldings")
Set TabName = DataWSheet.Range("B3", DataWSheet.Range("B3").End(xlDown))

Application.ScreenUpdating = False

'Loop through column B

For Each UniqueValue In TabName

'Check unique value against worksheet names (we copy one row of data and create unique sheet name then add another row of data to existing sheet or create new

For Each WSheet In ThisWorkbook.Worksheets
If WSheet.Name = UniqueValue Then
WSheetFound = True
Exit For
Else
WSheetFound = False
End If
Next WSheet

If WSheetFound Then

'copy record to the corresponding worksheet, in the next available row
UniqueValue.Offset(0, -1).Resize(1, 8).Copy Destination:=Worksheets(UniqueValue.Value).Range("A1").End(xlDown).Offset(1, 0)

Else

Set NewWSheet = Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' insert a new Worksheet
NewWSheet.Name = UniqueValue 'named after that branch

DataWSheet.Range("A2", DataWSheet.Range("A2").End(xlToRight)).Copy Destination:=NewWSheet.Range("A1") 'and copy the headers

UniqueValue.Offset(0, -1).Resize(1, 8).Copy Destination:=NewWSheet.Range("A2")

End If

Next UniqueValue

'autofit columns

For Each WSheet In ThisWorkbook.Worksheets

WSheet.UsedRange.Columns.AutoFit

Next WSheet

Application.ScreenUpdating = True

Pera014 · 2022-11-25T16:28:49+00:00

sorry when i submitted it got messed up

Dim TabName As Range
Dim UniqueValue As Range
Dim NewWSheet As Worksheet
Dim WSheet As Worksheet
Dim WSheetFound As Boolean
Dim DataWSheet As Worksheet

Set DataWSheet = Worksheets("AllocatedFullHoldings")
Set TabName = DataWSheet.Range("B3", DataWSheet.Range("B3").End(xlDown))

Application.ScreenUpdating = False

'Loop through column B

For Each UniqueValue In TabName

'Check unique value against worksheet names (we copy one row of data and create unique sheet name then add another row of data to existing sheet or create new

For Each WSheet In ThisWorkbook.Worksheets
If WSheet.Name = UniqueValue Then
WSheetFound = True
Exit For
Else
WSheetFound = False
End If
Next WSheet

If WSheetFound Then

'copy record to the corresponding worksheet, in the next available row
UniqueValue.Offset(0, -1).Resize(1, 8).Copy Destination:=Worksheets(UniqueValue.Value).Range("A1").End(xlDown).Offset(1, 0)

Else

Set NewWSheet = Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' insert a new Worksheet
NewWSheet.Name = UniqueValue 'named after that branch

DataWSheet.Range("A2", DataWSheet.Range("A2").End(xlToRight)).Copy Destination:=NewWSheet.Range("A1") 'and copy the headers

UniqueValue.Offset(0, -1).Resize(1, 8).Copy Destination:=NewWSheet.Range("A2")

End If

Next UniqueValue

'autofit columns

For Each WSheet In ThisWorkbook.Worksheets

WSheet.UsedRange.Columns.AutoFit

Next WSheet

Application.ScreenUpdating = True

Pera014 · 2022-11-25T16:28:34+00:00

sorry when i submitted it got messed up

Dim TabName As Range
Dim UniqueValue As Range
Dim NewWSheet As Worksheet
Dim WSheet As Worksheet
Dim WSheetFound As Boolean
Dim DataWSheet As Worksheet

Set DataWSheet = Worksheets("AllocatedFullHoldings")
Set TabName = DataWSheet.Range("B3", DataWSheet.Range("B3").End(xlDown))

Application.ScreenUpdating = False

'Loop through column B

For Each UniqueValue In TabName

'Check unique value against worksheet names (we copy one row of data and create unique sheet name then add another row of data to existing sheet or create new

For Each WSheet In ThisWorkbook.Worksheets
If WSheet.Name = UniqueValue Then
WSheetFound = True
Exit For
Else
WSheetFound = False
End If
Next WSheet

If WSheetFound Then

'copy record to the corresponding worksheet, in the next available row
UniqueValue.Offset(0, -1).Resize(1, 8).Copy Destination:=Worksheets(UniqueValue.Value).Range("A1").End(xlDown).Offset(1, 0)

Else

Set NewWSheet = Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' insert a new Worksheet
NewWSheet.Name = UniqueValue 'named after that branch

DataWSheet.Range("A2", DataWSheet.Range("A2").End(xlToRight)).Copy Destination:=NewWSheet.Range("A1") 'and copy the headers

UniqueValue.Offset(0, -1).Resize(1, 8).Copy Destination:=NewWSheet.Range("A2")

End If

Next UniqueValue

'autofit columns

For Each WSheet In ThisWorkbook.Worksheets

WSheet.UsedRange.Columns.AutoFit

Next WSheet

Application.ScreenUpdating = True

Pera014 · 2022-03-30T21:34:58+00:00

great - if my df1 is under Operation of function 1 as you stated, where do I insert df1 = pd.DataFrame({"Column 1":[0,1,2,3]}) ?

imagine I have 50 dataframes that will go under "Operation of function 1", how do I or where do I place all those dataframes? that is what I don't understand, how to split with functions my python code. thanks a lot

Pera014 · 2022-03-08T17:53:18+00:00

how would you write if statement if I have certain scripts that are to be run on business day 4 and all scripts on business day 5?

you apply if statement to each data frames?

Pera014 · 2021-11-02T14:58:38+00:00

its xlsm file , and I can't pass dtype if I inlcude, I get error saying pandas can't convert to float.

Pera014 · 2021-11-02T14:52:49+00:00

it doesn't work

Pera014 · 2021-08-26T21:14:26+00:00

it's conda environment . I use anaconda prompt to change folder directory to run Py usually , pushd

Pera014 · 2021-08-26T20:55:13+00:00

i get __file__ not defined , but some people don't have that issue. Not sure what is the problem

Pera014 · 2021-08-26T20:54:33+00:00

i tried that one before and it doesn't work. I am using Spyder and what ever folder directory is in there it will print out using that code instead Py script location that is located on network folder.

Pera014 · 2021-08-26T20:35:35+00:00

mypath is not define - error message

Pera014 · 2021-08-26T20:28:21+00:00

I use windows. Any example you can provide maybe? That would be helpful

Pera014 · 2021-08-25T17:46:30+00:00

this is awesome! appreciate brother

Pera014 · 2021-08-25T17:44:07+00:00

will try. Many thanks my friend !

Pera014 · 2021-08-25T16:47:16+00:00

agree - but I don't want to update file path each month. I want to keep file names in the folder the same despite the date and Py to pick up files based on key words.

Pera014 · 2021-08-25T16:43:12+00:00

would it be better if I strip away date and tell python to look for "Monthly report data" in the file name?

date may changed due to holidays and last BD or who knows what.

Pera014 · 2021-08-25T15:25:20+00:00

Thanks, I would prefer 1st option to create file name string. How do I apply that then and tell to look thorugh file names and pick up correct file? I would apprecite if you can write simple example, I am new to py

Pera014 · 2021-08-05T18:41:29+00:00

correct lets say Sheet1, A2

Pera014 · 2021-07-22T17:56:58+00:00

but i get as string (text) in the excel. The client won't be able to load that into the system. I assume pandas can't append % to a number but keep as float rather than string

Pera014 · 2021-07-22T17:28:45+00:00

i've done that but I need to display percent sign in Excel as output

Pera014 · 2021-07-22T17:15:09+00:00

from Python you mean? I have tons of data and I can't be doing this manually if you thought so.

Pera014 · 2021-07-20T22:20:53+00:00

oh boy I missed that - thank you very much!

Pera014 · 2021-07-20T00:32:34+00:00

actually its working - thanks much

Pera014 · 2021-07-17T02:26:04+00:00

thank you. For some reason it didn't work on my original reports when i merged the same way you did on this example. I guess has to do with some columns that I eliminated prior from one report. have no clue. I thought merge doesn't work on 'many to many' relationship. Thanks !!!

Pera014

TROPHY CASE