Copying varying list into another column, but with spaces between in VBA

[Shakin25]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi,

I am using Excel 2003.

I have a list of numbers in column A. The amount of numbers in the list varies.
Each number is a store account that I need to match to a list of products in column E.
Basically I have 5 products to be returned from all of the stores, but in order for me to load them into another system, I need to duplicate the products as many times as I have stores and then paste each store number at the top of each group of products. The products are duplicated below one another.
I have completed most of the macro, but am stuck on the pasting the numbers next to each group of products. I have a generic place holder in each spot that I want the number to copy over.

Below is the coding I am using to copy each cell in the list of stores. I just need to know how to paste them.

Dim j As Integer
Dim Count As Integer
Count = Range("D1")
For j = 1 To Count
Mycount = Range("c1") + 1
Range("C1") = Mycount

Range("A4").Select
ActiveCell.Offset(Mycount, 0).Copy

Next j

 

[RSXchin]

what are you doing with what you copy?(where do you want it pasted) please post all relevant code. an example will make this easier to deal with.

oh yeah, welcome to the forums

 

[Shakin25]

Hi,

Not sure how to attach the spreadsheet?

I am copying cell A5 to cell E5, Then Cell A6 to Cell E12, then cell A7 to Cell E19. The catch though is that the cells in column E can vary.

I was thinking of using the formula in the first post to copy the data, then go to E5 and use

Selection.End(xlDown).Select

as many times as the counter has ticked over to go down to the cell I needed, but I don't think that will work.

Thanks though, it's a great site.

 

[RSXchin]

You don't HAVE to copy paste. You can just say...

[E5] = [A5]

The second part is where you're copying from.

 

[Shakin25]

The main issue is the dynamic nature of the cells. I think I might have a solution though. Is there any way to offset the rows of a cell by the amount in another cell?

eg: instead of Offset(5,2)
maybe Offset(Cell(E1),2)