From excel timesheet create a import file csv

[Kaz5208]

Hi, really hoping for some help please.
I have a excel timesheet which details hours worked against different pay elements. For example , columns would be ...
Ee no., name, no.of hrs, hourly rate, total pay. Header above theses columns would be PE Basic pay( 88). Following to the right , the next columns would be again, no.of hours, hourly rate, total pay. However the header above this set of columns would be PE overtime 1.5 (89) .

I am looking to create a csv file from this data. We currently copy and paste, but this is taking an age!!!
I would need the columns in the following order.

Ee no. PE code I.e 88, no. of hours, hourly rate
We dont need the data rows where there are no hours for a pay element, so need to delete those.
Then directly under the data rows have Ee no. PE code 89, no.of hours, hourly rate. So the import file is effectively has 4 columns but many rows.

Hope the above makes sense... I would appreciate any help.
Many thanks in advance

 

[Kaz5208]

WOW!! thank you. This is amazing!!
Where do you learn this stuff from? Can you just explain the key actions like DIM & AS long? thanks. I would really like to learn this vba as I have other timesheet layouts i would like to apply this logic to and create import files.

The only bit i have a problem with is

'Save file
wPath = "C:\trabajo\"
wFile = "Import file creation.csv"
sh2.Copy
ActiveWorkbook.SaveAs Filename:=wPath & wFile, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
MsgBox "End"

End Sub

I get an error 400 on my macbook. Something about active object?

ActiveWorkbook.SaveAs Filename:=wPath & wFile, FileFormat:=xlCSV, CreateBackup:=False

Do i need to change the "C:\trabajo\" to destination of my choice?

Thanks once again, this is going to save me soo much time!!!

 

[DanteAmor]

WOW!! thank you. This is amazing!!
Where do you learn this stuff from? Resolving user doubts. I don't use any of this in my work, hehe.

Can you just explain the key actions like DIM & AS long? This is used to declare a variable, the variables are used to store data, and you must specify the type of variable, Long is a type of variable. Check the following link:

Declaring variables (VBA)

Office VBA reference topic

docs.microsoft.com

The only bit i have a problem with is

'Save file
wPath = "C:\trabajo\" Yes, change this information to the name of your folder.

I get an error 400 on my macbook. Something about active object?
ActiveWorkbook.SaveAs Filename:=wPath & wFile, FileFormat:=xlCSV, CreateBackup:=False
Do i need to change the "C:\trabajo\" to destination of my choice?

Thanks once again, this is going to save me soo much time!!!

I'm glad to help you. Thanks for the feedback.