copy cell value to different worksheet with loop

[rainbow7766]

Hi, i am new to excel vba. i have a "master" worksheet with 100 rows of data and "template" worksheet. "template" worksheet have a fixed table design.
how can use vba copy "master" range A1, B1 to "template" worksheet B2, D2, then it will loop and read "master" second row A2, B2, create new sheet from "template" worksheet, paste the A2, B2 data to same B2, D2 cell? then loop the same process until the last row of data? Thank u vry much!

 

[My Aswer Is This]

You said: Quote:
A1, B1 copy to B2, D2

A1 to B1 is two cells
B2, D2 is three cells
So how can we do that?
And how are we going to name all these new sheets?

 

[jolivanes]

As "My Answer Is This" alluded to, your post is a little meagre on information.
Ensure you have sheets with names exactly as in here or change references in code as required.

Sub Maybe_So()
Dim dataArr
Dim lr As Long, i As Long, curSh
Dim shT As Worksheet, shM As Worksheet
Application.ScreenUpdating = False
Set shM = Worksheets("Master")
Set shT = Worksheets("Template")
curSh = ActiveSheet.Name
lr = shM.Cells(Rows.Count, 1).End(xlUp).Row
dataArr = shM.Cells(1).Resize(lr, 2).Value
    For i = 1 To lr
        shT.Copy After:=Sheets(Sheets.Count)
            With Sheets(Sheets.Count)
                .Name = "Template " & i
                .Cells(2, 2).Value = dataArr(i, 1)
                .Cells(2, 4).Value = dataArr(i, 2)
            End With
    Next i
Sheets(curSh).Activate
Application.ScreenUpdating = True
End Sub

I don't know if the result is what you want.
If not, explain in detail.

 

[rainbow7766]

You said: Quote:
A1, B1 copy to B2, D2

A1 to B1 is two cells
B2, D2 is three cells
So how can we do that?
And how are we going to name all these new sheets?

Hi, Thanks for the reply, the new duplicated sheets can be any name or just the default.
which mean:
Master A1 copy to Template B2
Master B1 copy to Template D2
duplicate Template as new Sheet3 then
Master A2 copy to Sheet3 B2
Master B1 copy to Sheet3 D2
then keep looping until end of the last row.

 

[rainbow7766]

Hi, Thanks for the reply, the new duplicated sheets can be any name or just the default.
which mean:
Master A1 copy to Template B2
Master B1 copy to Template D2
duplicate Template as new Sheet3 then
Master A2 copy to Sheet3 B2
Master B1 copy to Sheet3 D2
then keep looping until end of the last row.

Sorry, shud be this.
Master A1 copy to Template B2
Master B1 copy to Template D2
duplicate Template as new Sheet3 then
Master A2 copy to Sheet3 B2
Master B2 copy to Sheet3 D2
then keep looping until end of the last row.

 

[rainbow7766]

As "My Answer Is This" alluded to, your post is a little meagre on information.
Ensure you have sheets with names exactly as in here or change references in code as required.

Sub Maybe_So()
Dim dataArr
Dim lr As Long, i As Long, curSh
Dim shT As Worksheet, shM As Worksheet
Application.ScreenUpdating = False
Set shM = Worksheets("Master")
Set shT = Worksheets("Template")
curSh = ActiveSheet.Name
lr = shM.Cells(Rows.Count, 1).End(xlUp).Row
dataArr = shM.Cells(1).Resize(lr, 2).Value
    For i = 1 To lr
        shT.Copy After:=Sheets(Sheets.Count)
            With Sheets(Sheets.Count)
                .Name = "Template " & i
                .Cells(2, 2).Value = dataArr(i, 1)
                .Cells(2, 4).Value = dataArr(i, 2)
            End With
    Next i
Sheets(curSh).Activate
Application.ScreenUpdating = True
End Sub

I don't know if the result is what you want.
If not, explain in detail.

Hi jolivanes.
Thnk you so much. this is exactly what i need.
but do you mind if you could explain a little bit what this does? my actual data is way more than this and want to put more cells on it. trying to learn, so it would be great with some explanation. appreciate it very much.

example, Master G2 to Template D3, which area do i add it in. trying to modify the numbers but didn't make it work.

---
r = shM.Cells(Rows.Count, 1).End(xlUp).Row
dataArr = shM.Cells(1).Resize(lr, 2).Value
For i = 1 To lr
shT.Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = "Template " & i
.Cells(2, 2).Value = dataArr(i, 1)
.Cells(2, 4).Value = dataArr(i, 2)
End With
Next i
---

 

[jolivanes]

Please do not quote whole posts. All extra clutter we don't need.
Use post numbers or a short quote.

Re: "Master G2 to Template D3"
As it stands, you cant because the data array does not include that. It only has Columns A and B data (shM.Cells(1).Resize(lr, 2).Value).

Re: "Master G2 to Template D3"
Only this or in combination with other cells?

In other words, what needs to be copied/pasted?

 

[rainbow7766]

@jolivanes Thank you very much.

Re: "Master G2 to Template D3"
As it stands, you cant because the data array does not include that. It only has Columns A and B data (shM.Cells(1).Resize(lr, 2).Value).
>> then can i include the remaining Columns or specific Columns?


Re: "Master G2 to Template D3"
Only this or in combination with other cells?
>> in fact hav many more, i juz pull out some data as example. i trying to play around wit .Cell(x, x).Value = dataArr(i, x), i know how to find the "Template" cells. but because the data array does not include, so i get an errors. possible to include other columns as per question above? i have data from A >> Q, but not all are requires.


In other words, what needs to be copied/pasted?
>> a full list of data from Columns A >> Q ~50-100 rows, Template is like a fill in the blank form and get data from Master's specific Columns and pump in

 

[jolivanes]

Change this line

Dim lr As Long, i As Long, curSh

to this

Dim lr As Long, lc As Long, i As Long, curSh

and this line

dataArr = shM.Cells(1).Resize(lr, 2).Value

to this

dataArr = shM.Cells(1).Resize(lr, lc).Value

Add whatever you want between the With......End With statement below the .Name line
In your example, G2 to D3, you would add

.Cells(3, 4).Value = dataArr(i, 7)    'Cells(3, 4) = "D3" and dataArr(i, 7) is the i Row and Column 7 which is Column "G"

 

[jolivanes]

I forgot to mention that you need to add this line

lc = shM.Cells.Find("*", , , , xlByColumns, xlPrevious).Column

below the

lr = shM.Cells(Rows.Count, 1).End(xlUp).Row

line.