我有一个工作表,其中包含一些财务数据要转移到会计系统。
我可以说我知道一些关于编程的东西,但是Excel宏对我来说有点太多了,所以请建议一些(甚至部分)解决我的问题的方法。谢谢!
主工作簿列包括:
需要传输的行具有Checked="Yes“和Transferred="”
输出工作表必须为主表的每一行有两行(因为贷方和下注必须分开)。输出列必须为:
在此之后,需要将主工作表的已传输列设置为"Pending",并提示另存为对话框输入新工作簿(可能具有某些默认名称和路径)。
再次感谢!
发布于 2010-10-25 22:09:21
这似乎是一份你会喜欢的简单工作。首先,只需访问- http://www.ozgrid.com/Excel/free-training/basic-index.htm
如果你需要任何特别的帮助,请随意写..
干杯..
发布于 2018-08-23 04:59:34
这里有一个如何做到这一点的例子。遗漏了一些东西,但这应该能让你上路。
Sub Transfer()
Dim iRow As Long
Dim iTotalRows As Long
Dim iOutput As Long
Dim wsMaster As Worksheet
Dim wbNew As Workbook
Dim wsOutput As Worksheet
Dim sNewFile As String
'the name of your source sheet
Set wsMaster = ThisWorkbook.Worksheets("Master")
'create your new target workbook
Set wbNew = Application.Workbooks.Add
Set wsOutput = wbNew.Worksheets(1)
wsOutput.Name = "Output" 'optional: name the output sheet
'place your headings
With wsOutput
.Cells(1, 1) = "Date"
.Cells(1, 2) = "Account"
'etc
End With
iTotalRows = wsMaster.UsedRange.Rows.Count
'assumes headings in row 1, so start scanning from row 2
For iRow = 2 To iTotalRows
'hard-coding the column positions here... not ideal but you can improve this bit
If wsMaster.Cells(iRow, 6) = "Yes" And wsMaster.Cells(iRow, 7) = "" Then
iOutput = iOutput + 2
wsOutput.Cells(iOutput, 1) = wsMaster.Cells(iRow, 3) 'date
wsOutput.Cells(iOutput + 1, 1) = wsMaster.Cells(iRow, 3) 'date again on the next row
wsOutput.Cells(iOutput, 2) = wsMaster.Cells(iRow, 2) 'Account
wsOutput.Cells(iOutput + 1, 2) = wsMaster.Cells(iRow, 2) 'Account again on the next row
'etc
'set pending flag
wsMaster.Cells(iRow, 7) = "Pending"
End If
Next
'prompt to save the new file: suggest a name with today's date encoded in it
sNewFile = Application.GetSaveAsFilename("newFile" & Format(Now, "yymmdd") & ".xlsx")
If sNewFile <> "" Then wbNew.SaveAs sNewFile
End Sub
https://stackoverflow.com/questions/4015226
复制相似问题