Warm tip: This article is reproduced from stackoverflow.com, please click
excel vba project

How to paste static data in a new column on one worksheet for every new row added to a table on anot

发布于 2020-03-27 10:23:47

I am a novice in VBA and coding in general and am in the beginning stages of creating a project management tool. The process of using this tool begins with an employee entering a list of accounts that need to be opened. For each account there is a standardized list of tasks that need to be completed for that account to be officially be opened.

What I want is that for each new account that is added, the standardized list of tasks appear together on a separate sheet than where the accounts that need to be opened are.

What I am thinking of is using a loop such that for each new row that is added to the table of accounts that need to be opened, the standardized list of tasks is copied from a worksheet in the workbook to the worksheet containing all the standardized lists for all the accounts. This is code I have in the works. However, I could use help and direction.

Dim New_Markets As Range
Set New_Markets = ThisWorkbook.Worksheets("List of Markets").Range("A1:B104")

Dim LastRow As Long
LastRow = ThisWorkbook.Worksheets("SKAs").ListObjects("Table1").Range.Rows.Count

Dim SKAs As Range
Set SKAs = ThisWorkbook.Worksheets("SKAs").Range("A1:E" & LastRow)

For Each Rows In SKAs
'I want to add a column of New_Markets to the sheet "Market to Open"
Questioner
ak40837
Viewed
96
ak40837 2019-07-13 00:04

This was the answer that I was looking for:

Sub Adding_New_SKAs()

Dim New_Markets As Range
Set New_Markets = ThisWorkbook.Worksheets("List of Markets").Range("A1:B104")

Dim LastRow As Integer
LastRow = ThisWorkbook.Worksheets("SKAs").Range("A1").CurrentRegion.Rows.Count

Dim i As Integer, Index As Integer

Index = 0

For i = 2 To LastRow
   New_Markets.Copy Sheet2.Range("A1").Offset(0, Index)
   Index = Index + 3
Next i

Columns.AutoFit

End Sub