在Excel工作表之间移动数据 [英] Moving Data between excel Sheets

问题描述

我必须创建健康报告以对公共汽车进行日常监控.我还必须确保在季度末之前至少对所有公交车进行一次健康检查.我将简要介绍我的工作.

I have to create health reports for daily surveillance on buses. I also have to make sure that I have done health checks on all the buses at least once before the end of the quarter. I will give a brief description of what I have to do.

1>每天都会有10到15个健康查询来询问我每天必须进行健康报告的随机公交车号.

1> Every day 10-15 health queries comes in for random bus number for which I have to do a health report daily.

2>在第二季度末之前，我必须对所有公共汽车进行健康检查并报告.

2> Before the end of the quarter I have to do health checks for all the buses and report it.

我的问题:

例如，我有一个要列出的800辆公交车的主列表.我想创建一个字段，在其中输入要进行运行状况检查的特定总线号，该字段应在工作表1中称为:已完成，其余未完成的总线号应在工作表2中进入:未完成.这样，我不必在本季度末为随机查询重做报告.因此，每天我都会在该字段中输入随机总线号，它应该从未完成"表中删除这些总线，并将其添加到已完成"表中.

I have a master list of total of 800 buses which I want to put in sheet 3 for example. I want to create a field where I enter a particular bus number for which I do health check and it should go in Sheet 1 called: Completed and the remaining not completed buses numbers should go in sheet 2 called: Not Completed. This way I don't have to redo the reporting for the random queries at the end of the quarter. So every day I keep entering random bus number in that field and it should remove those buses from Not Completed sheet and add it to Completed sheet.

有没有一种特殊的方法?

Is there a particular way to do it?

推荐答案

添加一个按钮是最简单的，然后在按钮的vba代码中，将需要从第二页移动到第三页的行，然后删除工作表2中有问题的行.

It'd be easiest to just add a button, and in the vba code for the button move the row you need to move from Sheet 2 to Sheet 3, then delete the row in question on Sheet 2.

该按钮的代码:

Private Sub Complete_Click()

  Dim FoundRow As Integer
  Dim LastRow As Integer

  FoundRow = Sheets("Sheet2").Range("A:A").Find(Sheets("Sheet2").Range("C1"), _
             Sheets("Sheet2").Range("A2"), xlValues).Row
  LastRow = Sheets("Sheet1").UsedRange.Rows.Count+1

  Sheets("Sheet2").Range("A:A").Rows(FoundRow).EntireRow.Copy
  Sheets("Sheet1").Range("A:A").Rows(LastRow).PasteSpecial (xlValues)
  Sheets("Sheet2").Range("A:A").Rows(FoundRow).EntireRow.Delete (xlShiftUp)

End Sub

然后我将在工作表3上有一个按钮，该按钮将重置所有工作表.

I'd then have a button on Sheet 3 that would reset all the Sheets.

以下是工作表3按钮的一些简单代码(尽管我会在其中放置一个安全消息框):

Here's some simple code for the button for Sheet 3 (though I would put a safety Message box in):

Private Sub ResetSheet_Click()

  Worksheets("Sheet1").UsedRange.ClearContents
  Worksheets("Sheet2").UsedRange.ClearContents

  Worksheets("Sheet3").UsedRange.Copy
  Worksheets("Sheet2").Range("A2").PasteSpecial (xlPasteValues)

End Sub

这篇关于在Excel工作表之间移动数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！