将信息从一个选项卡拉到另一个选项卡

I need to populate specific columns in one tab from the original data held in the first tab. I have done a quick reordered VBA code to help anybody understand what I am trying to do in the hope they can refine. I also need the VBA code to stop at the line where the data ends. currently the recorded VBA code pulls down to row 159 yet the data in the original tab stops at row 72. I would have stopped my VBA code at the line where the information ends but this changes from day to day and could be anywhere from row 1 to 158. (If I populate a cell past where the data ends in the original it makes the sheet unusable for other purposes I require it for, even if it is a zero.)

我需要在第一个选项卡中保存的原始数据中填充一个选项卡中的特定列。我已经做了一个快速重新排序的VBA代码,以帮助任何人了解我想要做的事情,希望他们可以改进。我还需要VBA代码停在数据结束的行。当前记录的VBA代码下拉到第159行,但原始选项卡中的数据在第72行停止。我会在信息结束的行停止我的VBA代码,但这种情况每天都在变化,可能是第1行的任何地方(如果我将一个单元格填充到数据以原始数据结尾的位置,那么表单就会使该表格无法用于其他目的,即使它是零。)

There is data in column A and B, so I was thinking if the macro could say if data is in cell A15 then pull though info from G15 etc?? No data no pull. I know this may seem basic to some but as a novice I am still getting my head around VBA.

A列和B列中有数据,所以我在想是否宏可以说数据是否在单元格A15中然后从G15等拉取信息?没有数据没有拉。我知道这对某些人来说似乎是基本的但作为一个新手,我仍然围绕着VBA。

Sub Populate_Order()
ActiveWindow.SmallScroll Down:=-15
Range("M8").Select
ActiveCell.FormulaR1C1 = "='Order Sheet'!R[7]C[-6]"
Range("M8").Select
Selection.AutoFill Destination:=Range("M8:M159"), Type:=xlFillDefault
Range("M8:M159").Select
ActiveWindow.SmallScroll Down:=-135
Range("Q8").Select
ActiveCell.FormulaR1C1 = "='Order Sheet'!R[7]C[-9]"
Range("Q8").Select
Selection.AutoFill Destination:=Range("Q8:Q159"), Type:=xlFillDefault
Range("Q8:Q159").Select
ActiveWindow.SmallScroll Down:=-132
Range("R8").Select
ActiveCell.FormulaR1C1 = "='Order Sheet'!R[7]C[-9]"
Range("R8").Select
Selection.AutoFill Destination:=Range("R8:R159"), Type:=xlFillDefault
Range("R8:R159").Select
ActiveWindow.SmallScroll Down:=-123
Range("I3").Select

1 个解决方案

#1

to find the next empty row try something like this

找到下一个空行尝试这样的事情

Dim sourceSheet As Worksheet: Set sourceSheet = ThisWorkbook.Worksheets("sheet1")
Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("sheet2")
lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row

destSheet.range("A" & lMaxRows + 1).Value = sourceSheet.range("M1").Value

this is a simple example of finding the next empty row then setting column A in that row to the value from cell M1 in another sheet

这是查找下一个空行然后将该行中的列A设置为另一个工作表中的单元格M1的值的简单示例

#1