需要根据另一个工作表中的值用连续的行数据填充工作表 [英] Need to fill a sheet with consecutive rows data based on value in another worksheet

问题描述

好，这是情况.我创建了一个包含5张纸的工作簿.

OK here's the situation. I've created a workbook with 5 sheets in it.

第一页包含数量"，描述"，价格"和一些其他数据的列，但这些都是相关的.

The first sheet has columns for QTY, DESCRIPTION, PRICE and some other data, but those are the pertinent ones.

根据输入的数据，填写人工发票和若干订单.那工作正常.

Based on the data entered, a labor invoice and several order forms are filled out. That is working correctly.

我遇到的问题是，我还需要它来填写一张名为合同"的表格.主表上大约有75个项目，但合同永远不会超过30个项目.

The issue I am having is I also need it to fill out a sheet named Contract. There are approximately 75 items on the main sheet, but the contract will never have more than 30 items.

我只需要连续将主表上的QTY值非零的行连续拖到合同表，这样就不会有空白行，直到数据集上的QTY非零的项目用完为止主表.

I need to pull over only rows that have a non-zero value for QTY on the main sheet to the contract sheet, consecutively so that there are no blank rows until the data-set runs out of items with non zero QTY on the main sheet.

IE ，如果主表上有15个非连续行，其QTY值非零，那么在合同表上，我需要30列中的前15行才能获得QTY，说明，主工作表行中的PRICE，其QTY值非零.

I.E. if there are 15 non-consecutive rows on the main sheet with non-zero values for QTY, on the Contract sheet I need the first 15 rows out of 30 to pull over QTY, DESCRIPTION, PRICE from the main worksheet rows with non-zero QTY values.

我希望我有道理..这让我很沮丧！

I hope I am making sense.. It's got me stumped!

谢谢

我刚刚意识到，我只需要将QTY AND Contract Cost的值不为零的数据拉到合同表中即可！哎呀！

I just realized, I need to only pull data that has non zero values for QTY AND Contract Cost to the contract sheet! oops!

推荐答案

尝试一下.我假设您需要从主体到合同的拉动数据.希望我能正确回答您的问题
主要工作表:

合同工作表(结果)

Try this. I assumed that you need pull data from main to contract. I hope i get your question right
Main worksheet :

Contract worksheet (Result)

Option Explicit
Dim MyWorkbook As Workbook
Dim MyWorksheet As Worksheet
Dim MyOutputWorksheet As Worksheet

Sub PullData()
Set MyWorkbook = Workbooks(ActiveWorkbook.Name)
Set MyWorksheet = MyWorkbook.Sheets("Main")
Set MyOutputWorksheet = MyWorkbook.Sheets("Contract")

    Dim myValue As Long
    Dim RowPointer As Long

    For RowPointer = 2 To MyWorksheet.Cells(Rows.Count, "B").End(xlUp).Row
        If MyWorksheet.Range("A" & RowPointer).Value > 0 And MyWorksheet.Range("A" & RowPointer).Value <> "" Then
            If MyOutputWorksheet.Cells(Rows.Count, "B").End(xlUp).Row > 15 Then
                Exit Sub
            End If
            MyWorksheet.Range(("A" & RowPointer) & ":C" & RowPointer).Copy Destination:=MyOutputWorksheet.Range("A" & MyOutputWorksheet.Cells(Rows.Count, "B").End(xlUp).Row + 1)
        End If
    Next RowPointer


End Sub

这篇关于需要根据另一个工作表中的值用连续的行数据填充工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！