Macro to repeat copy paste until empty cell

[sbeedle]

I am hoping someone might be able to help me create a macro that copy and pastes data from two sources. I have a store list and a sku list. Each store will need to be married to the sku list in a column. For instance, store number 3 gets copy and pasted in front of each sku in the list. The sku list is then duplicated under the first and the next store number is copy and pasted in front of that list. This would continue until the end of the store list. I have attached screen shot to hopefully help explain. Column A is store list, C and D the sku list/description, columns F-H the result that I would like to see. The result in the screen shot is only for the first 2 stores and I would like to have it run the whole list of stores. This is just a small portion of the data I am looking to do this with - I am just showing a small example to apply to the larger actual project. Thank you in advance for any help you may be able to provide!!

Scott

 

[Fluff]

How about

Sub sbeedle()
   Dim Rng As Range, Cl As Range
   Dim i As Long
   i = 3
   Set Rng = Range("C3:D" & Range("C" & Rows.Count).End(xlUp).Row)
   For Each Cl In Range("A3", Range("A" & Rows.Count).End(xlUp))
      Cl.Copy Range("F" & i).Resize(Rng.Rows.Count)
      Rng.Copy Range("G" & i)
      i = i + Rng.Rows.Count
   Next Cl
End Sub

 

[sbeedle]

How about

Sub sbeedle()
   Dim Rng As Range, Cl As Range
   Dim i As Long
   i = 3
   Set Rng = Range("C3:D" & Range("C" & Rows.Count).End(xlUp).Row)
   For Each Cl In Range("A3", Range("A" & Rows.Count).End(xlUp))
      Cl.Copy Range("F" & i).Resize(Rng.Rows.Count)
      Rng.Copy Range("G" & i)
      i = i + Rng.Rows.Count
   Next Cl
End Sub

Thank you so VERY much!! This was great! I changed a couple things to include the first store and item number (C3 to C2 and A3 to A2) and changed integer to 2 to line it up at the top but what you did was incredible and was perfect! I can't begin to tell you how much time this is going to save me. Serious heartfelt thanks!

 

[sbeedle]

How about

Sub sbeedle()
   Dim Rng As Range, Cl As Range
   Dim i As Long
   i = 3
   Set Rng = Range("C3:D" & Range("C" & Rows.Count).End(xlUp).Row)
   For Each Cl In Range("A3", Range("A" & Rows.Count).End(xlUp))
      Cl.Copy Range("F" & i).Resize(Rng.Rows.Count)
      Rng.Copy Range("G" & i)
      i = i + Rng.Rows.Count
   Next Cl
End Sub

I realize now looking at the screenshot that the numbers being off was my fault as I had screen shot a sheet that was a bit different. Sorry that I implied you might have had something wrong - you were perfect, I was not! Thank you again!!!

 

[Fluff]

You're welcome & thanks for the feedback.