Copying cell values into a different worksheet on a loop

detweiler

Board Regular
Joined
Aug 2, 2013
Messages
62
As a follow up to the copy cell value to different worksheet with loop post, thanks to @rainbow7766 for getting the initial ask out there and @jolivanes for providing an efficient working solution, which was:

VBA Code:
Sub Maybe_So()
Dim dataArr
Dim lr As Long, lc 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
lc = shM.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
dataArr = shM.Cells(1).Resize(lr, lc).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
Again, credit where credit is due, the code was supplied by @jolivanes.

While I did get it to work, I do have two questions.
  1. My data has a header row and would like to know how to get the macro to start on the second row to pull the values into the new worksheet?
  2. How do I get the macro to recognize there is no next row of data so that it doesn't create an empty worksheet?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Re: "My data has a header row and would like to know how to get the macro to start on the second row to pull the values into the new worksheet?"
Change this
Code:
dataArr = shM.Cells(1).Resize(lr, lc).Value
to this
Code:
dataArr = shM.Cells(2, 1).Resize(lr - 1, lc).Value

Re" "How do I get the macro to recognize there is no next row of data so that it doesn't create an empty worksheet?"
lr finds the last used cell in Column A so there should not be any empty cells at the end.
If you have empty cells between the first or second cell and the last used cell in column A, you have to let us know.
Maybe this will take care of it if that is indeed the problem.
Insert this
Code:
If not dataArr(i, 1)  = "" Then
after the
Code:
For i = 1 To lr
line and insert
Code:
End If
after the
Code:
End With
line
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top