From excel timesheet create a import file csv

K

Kaz5208

New Member
Joined
Nov 7, 2019
Messages
6
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi @Kaz5208, welcome to the forum!

To perform a macro, you can provide the following data:
- Initial column of your data
- Final column of your data.
- Initial row of data.
- Row of headings.
- In which column values ​​equal to 0 should be removed.
- Which columns will be copied and what will be the order in the file.
- File name.
- File path.
- CSV separated by commas?
 
Upvote 0
Hi, thanks for your message. Sorry for the delay... was hoping that I could load a test timesheet template to show you, but I don't know if its possible on a thread chat?

So column data...

Initial column of your data A
- Final column of your data.AM
- Initial row of data. 12
- Row of headings.11
- In which column values ​​equal to 0 should be removed = From the "hours" column for each element
- Which columns will be copied and what will be the order in the file. Copied will be column A "Emp", Pay element "88" (in cell "C8") then Basic hours column C, then rate from column D.
- File name. Import file creation
- File path. C:// my documents
- CSV separated by commas? Yes by commas.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Rate[/TD]
[TD="width: 64"]Column A[/TD]
[TD="width: 64"]STARTS ON ROW 12[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay[/TD]
[TD]Column B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Hours[/TD]
[TD]Column C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Rate[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay[/TD]
[TD]e[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Hours[/TD]
[TD]f[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Rate[/TD]
[TD]g[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay[/TD]
[TD]h[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Hours[/TD]
[TD]i[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Rate[/TD]
[TD]j[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay[/TD]
[TD]k[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Hours[/TD]
[TD]l[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Rate[/TD]
[TD]m[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay[/TD]
[TD]n[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Hours[/TD]
[TD]o[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay Rate[/TD]
[TD]p[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 128, colspan: 2"]Basic Pay[/TD]
[TD]q[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Holiday[/TD]
[TD="class: xl70, width: 64"]Hours[/TD]
[TD]r[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Holiday Pay[/TD]
[TD="class: xl70, width: 64"]Rate[/TD]
[TD]s[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Holiday[/TD]
[TD="class: xl70, width: 64"]Pay[/TD]
[TD]t[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Holiday[/TD]
[TD="class: xl70, width: 64"]Hours[/TD]
[TD]u[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Holiday Pay[/TD]
[TD="class: xl70, width: 64"]Rate[/TD]
[TD]v[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Holiday[/TD]
[TD="class: xl70, width: 64"]Pay[/TD]
[TD]w[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, colspan: 2"]Training Hours/Courses[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, colspan: 2"]Training Rate[/TD]
[TD]y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 128, colspan: 2"]Training[/TD]
[TD]z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 128, colspan: 2"]Shadowing Hours[/TD]
[TD]AA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 128, colspan: 2"]Shadowing Rate[/TD]
[TD]AB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 128, colspan: 2"]Shadowing[/TD]
[TD]AC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 128, colspan: 2"]Xmas Pay Hours[/TD]
[TD]AD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 128, colspan: 2"]Xmas Pay Rate[/TD]
[TD]AE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 128, colspan: 2"]Xmas pay[/TD]
[TD]AF[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The following columns .....................

We will need "ee(col A), Pay element (BEcol R11) then always 1.00 hour (this isn't on the timesheet) then rate will be the "total amount" (AG Col R12)

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl68, width: 128, colspan: 2"]Travel Time[/TD]
[TD]AG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Expenses[/TD]
[TD]AH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Dom Care On Call[/TD]
[TD]AI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Senior Increment[/TD]
[TD]AJ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Pay Adjustment[/TD]
[TD]AK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Missed Calls[/TD]
[TD]AL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Finders Fee[/TD]
[TD]AM[/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl68, width: 128, colspan: 2"]Travel Time[/TD]
[TD]AG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Expenses[/TD]
[TD]AH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Dom Care On Call[/TD]
[TD]AI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Senior Increment[/TD]
[TD]AJ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Pay Adjustment[/TD]
[TD]AK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Missed Calls[/TD]
[TD]AL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 128, colspan: 2"]Finders Fee[/TD]
[TD]AM[/TD]
[/TR]
</tbody>[/TABLE]
</body>

I have done very basic VBA, so if you can justs start me off with the coding, I can always have a go at finishing it off.

Another thing to note is that new employees can be added to the timesheet each week, so row91 may not always be the last data row. So I was thinking of using the follow

'Copy the data<o:p></o:p>
<o:p></o:p>
Sheets("Timesheet").Range("A12",Range("A12").End(xlDown)).Copy

Is this correct?

Many thanks in advance.

K
 
Upvote 0
Please, Put the letter of the column.

- In which column values ​​equal to 0 should be removed.

I don't understand which columns go in the file.
Do you want to put all the columns?
Is the order of the columns in the file the same as you have it in the excel sheet?
 
Upvote 0
[TABLE="width: 1345"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Branch[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wages[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tax Week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]To[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8.21 [/TD]
[TD][/TD]
[TD][/TD]
[TD]8.35 [/TD]
[TD][/TD]
[TD][/TD]
[TD]8.46 [/TD]
[TD][/TD]
[TD][/TD]
[TD]8.50 [/TD]
[TD][/TD]
[TD][/TD]
[TD]8.60 [/TD]
[TD][/TD]
[TD][/TD]
[TD]8.71 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD]88 [/TD]
[TD]88 [/TD]
[TD]88 [/TD]
[TD]89 [/TD]
[TD]89 [/TD]
[TD]89 [/TD]
[TD]90 [/TD]
[TD]90 [/TD]
[TD]90 [/TD]
[TD]91 [/TD]
[TD]91 [/TD]
[TD]91 [/TD]
[TD]92 [/TD]
[TD]92 [/TD]
[TD]92 [/TD]
[TD]93 [/TD]
[TD]93 [/TD]
[TD]93 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE No[/TD]
[TD]Employee[/TD]
[TD]Basic Pay Hours[/TD]
[TD]Basic Pay Rate[/TD]
[TD]Basic Pay[/TD]
[TD]Basic Pay Hours[/TD]
[TD]Basic Pay Rate[/TD]
[TD]Basic Pay[/TD]
[TD]Basic Pay Hours[/TD]
[TD]Basic Pay Rate[/TD]
[TD]Basic Pay[/TD]
[TD]Basic Pay Hours[/TD]
[TD]Basic Pay Rate[/TD]
[TD]Basic Pay[/TD]
[TD]Basic Pay Hours[/TD]
[TD]Basic Pay Rate[/TD]
[TD]Basic Pay[/TD]
[TD]Basic Pay Hours[/TD]
[TD]Basic Pay Rate[/TD]
[TD]Basic Pay[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD]T & NI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1102[/TD]
[TD]Jo Bloggs[/TD]
[TD]24.00[/TD]
[TD]8.21[/TD]
[TD]197.04 [/TD]
[TD]34.00[/TD]
[TD]8.35[/TD]
[TD]283.90 [/TD]
[TD][/TD]
[TD]8.46[/TD]
[TD]0.00 [/TD]
[TD][/TD]
[TD]8.50[/TD]
[TD]0.00 [/TD]
[TD][/TD]
[TD]8.60[/TD]
[TD]0.00 [/TD]
[TD][/TD]
[TD]8.71[/TD]
[TD]0.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]So csv file

1102,88,24,8.21
1102,89,34,8.35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1102,90,0,8.46[/TD]
[TD]Delete this line as no hours to import[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]headers for csv file ...........
pay no, pay element, hours,rate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks
 
Upvote 0
I don't understand your columns, I need you to put the letter of the column, example: A, B, C, D, etc.

Please, Put the letter of the column.

- In which column values ​​equal to 0 should be removed.

I don't understand which columns go in the file.
Do you want to put all the columns?
Is the order of the columns in the file the same as you have it in the excel sheet?
 
Upvote 0
If it is difficult for you to explain it with the columns, then upload 2 files to the cloud, an excel file with data, put the column headings in green and the rows in yellow that should be sent to the CSV file.


You also upload the CSV file to the cloud with the example of the output, obviously based on the same excel file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Hi,

Hope this explains what I am trying to achieve.

This is the link to the dropbox, dummy data entered.
Tab 1 = Timesheet
Tab 2 = Data from Timesheet (manually copy & paste)
Tab 3 = The csv file to import into system.


test timesheet to import.xlsx


Many thanks for your patience with me on this forum.

Karen
 
Upvote 0
Run this macro "create_csv_file"
The macro only requires 2 sheets, the input and output ("Timesheet" and "Final import")
If necessary change the name of these sheets ("Timesheet" and "Final import") in the macro

VBA Code: 
Dim k As Long 'This line goes to the beginning of all the code
Sub create_csv_file()
  Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, j As Long
  Dim wPath As String, wFile As String
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh1 = Sheets("Timesheet")
  Set sh2 = Sheets("Final import")
  sh2.Range("A2:D" & Rows.Count).Clear
 
  'copy and paste Data
  k = 2
  For j = Columns("C").Column To Columns("BB").Column Step 3
    i = 12
    Do While sh1.Cells(i, "A") <> ""
      Call Put_data(sh1, sh2, i, j, k, 1)
      i = i + 1
    Loop
  Next
  For j = Columns("BE").Column To Columns("BN").Column
    i = 12
    Do While sh1.Cells(i, "A") <> "" And sh1.Cells(8, j).Value <> ""
      Call Put_data(sh1, sh2, i, j, k, 0)
      i = i + 1
     Loop
  Next
 
  '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
'
Sub Put_data(sh1, sh2, i, j, k, st)
  If sh1.Cells(i, j).Value <> "" And sh1.Cells(i, j).Value <> 0 Then
    sh2.Cells(k, "A").Value = sh1.Cells(i, "A").Value
    sh2.Cells(k, "B").Value = sh1.Cells(8, j).Value
    sh2.Cells(k, "C").Value = IIf(st = 1, sh1.Cells(i, j).Value, 1)
    sh2.Cells(k, "D").Value = sh1.Cells(i, j + st).Value
    k = k + 1
  End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Pivot Table from Power Query - reorder Columns (Excel 2021)
Replies
4
Views
573
craigcurtin
C
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
1K
Micron
M
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
949
Peter_SSs
Peter_SSs
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
192
Madam_Xavier
M
M
  • Question Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
974
MichaelMorris
M

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top