Repeat 1 cell as many times as there are entries in another column

mt03530 · Feb 1, 2018

Hello!

My ultimate goal is to pull 3 types of information from n number of worksheets into a summary page. I was successful with copying and pasting unique values (see below), however, I am having issues copying one cell from each worksheet and having it repeat multiple times.

I have multiple worksheets that look something like this:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Amanda[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Color[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gold[/TD]
[/TR]
</tbody>[/TABLE]

My code for copying ID and Color is:

Code:

Private Sub CommandButton1_Click()
    Dim wks As Worksheet


     
    For Each wks In ThisWorkbook.Worksheets
        If Not wks.Name = "Summary" Then
            wks.Range("A7:A" & wks.Cells(Rows.Count, "A").End(xlUp).Row).Copy _
            Destination:=Worksheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1)
               wks.Range("D7:D" & wks.Cells(Rows.Count, "D").End(xlUp).Row).Copy _
            Destination:=Worksheets("Summary").Cells(Rows.Count, "B").End(xlUp).Offset(1)


        End If
    Next
    
    
End Sub

Without having to count the number of entries myself, how do I now copy "Amanda" for each entry for Amanda (in this case 3 times)? Thanks!

mt03530 · Feb 2, 2018

Fluff said:

How about

Code:

Private Sub CommandButton1_Click()
   Dim Wks As Worksheet
   Dim Rng As Range
   
   With Sheets("Summary")
      For Each Wks In ThisWorkbook.Worksheets
         If Not Wks.Name = "Summary" Then
            Set Rng = Wks.Range("A7:A" & Wks.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
            With .Range("A" & .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
               Rng.Copy .Offset(1)
               Rng.Offset(, 3).Copy .Offset(1, 1)
               .Offset(1, 2).Resize(Rng.Count).Value = Wks.Range("A4")
               Set Rng = Nothing
            End With
         End If
      Next Wks
   End With
   
End Sub

This assumes that you have something in the summary sheet when you run the code

This is perfect!!!
Thank you so much for all your help!! I really appreciate it!

As a beginner, may I ask how you learned VBA so well?

Fluff · Feb 2, 2018

Glad to help & thanks for the feedback

Most of what I know has been gleaned from this site over a number of years.

mt03530 · Feb 2, 2018

Fluff said:
Glad to help & thanks for the feedback

Most of what I know has been gleaned from this site over a number of years.

I guess I just have to be patient then haha.

Thanks again!

mbpress01 · Feb 2, 2018

Hello Fluff, could you elaborate further on the learning process. Did you take procedures from this site and analyze them by looking up VBA help, did you have a prior background in programming etc. I am learning myself but the process is hard and simple procedures seem to take literally 3hrs that someone with your skills would have it done in 30 minutes and probably less. I have reviewed a lot of your code and its amazing in its usefulness so while I can't speak for everyone, thank you for your efforts.

Fluff · Feb 2, 2018

Initially (when I was a lurker) I was simply trying to find some code that was similar to what I needed & would then try to understand it enough so that I could modify it to suit.
Then, when I became unemployed, I had plenty of time on my hands & started getting very active on here trying to help others.
When there wasn't much I could help with, I'd find a post that I didn't know how to answer (but which had already been answered) & would do some research to find out how to do it.
Once I had managed to get a macro that worked I'd compare it to the answer already supplied & if different to mine try & figure out the other macro.

At the same time some MVPs would post code to threads I was involved in pointing out other options & ways of doing things, which helped me learn even more.

hiker95 · Feb 2, 2018

I am learning myself but the process is hard and simple procedures seem to take literally 3hrs

mbpress01,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 12/19/2017

https://www.mrexcel.com/forum/excel...e-template-excel-spreadsheet.html#post4972842

hiker95 · Feb 3, 2018

Repeat 1 cell as many times as there are entries in another column

mt03530

New Member

mt03530

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

mt03530

New Member

mbpress01

New Member

Fluff

MrExcel MVP, Moderator

hiker95

Well-known Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Repeat 1 cell as many times as there are entries in another column

New Member

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock