Copy and Paste into Multiple Columns

[Grimm127]

I have a list. Item number in Rows and Stores in Cloumns. In the Column I have the number of units related to the Item number for each door.

I would like to create VBA code that will copy Item# (ColumnA) and paste it in front of each Store Column.
So it should look like Item#:Store A: Item#: Store B:Item#:Store C
The number of doors is random. It coulb be 2 or 100 depending on what is rec'd.

How can I do this?
[TABLE="width: 500"]
<TBODY>[TR]
[TD]ITEM#[/TD]
[TD]Store A[/TD]
[TD]Store B[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]87654[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</TBODY>[/TABLE]

 

[WarPigl3t]

Thank you for providing a dataset of how it looks like before the macro is run. Please provide a dataset of how it should look like after the macro is run. Your explination is confusing.

 

[Grimm127]

Thank you for providing a dataset of how it looks like before the macro is run. Please provide a dataset of how it should look like after the macro is run. Your explination is confusing.

Here you go:

Hope this helps.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Item#[/TD]
[TD]Store A[/TD]
[TD]Item #[/TD]
[TD]Store B[/TD]
[TD]Item#[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]1[/TD]
[TD]12345[/TD]
[TD]8[/TD]
[TD]12345[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]2[/TD]
[TD]45678[/TD]
[TD]4[/TD]
[TD]45678[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]3[/TD]
[TD]54321[/TD]
[TD]3[/TD]
[TD]54321[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]87654[/TD]
[TD]1[/TD]
[TD]87654[/TD]
[TD]1[/TD]
[TD]87654[/TD]
[TD]1[/TD]
[/TR]
</TBODY>[/TABLE]

 

[WarPigl3t]

Oh that seams easy enough.

lastRow = Range("A" & Rows.Count).End(xlup).Row
startCol = 3
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Do Until startCol > lastCol
    Column(startCol).Insert
    Range("A1:A" & lastRow).Copy
    Cells(1, startCol).Paste
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    startCol = startCol + 2
Loop

I thinnk that code works but I wrote it in my head so it's untested. Gimme a few minutes and I'll test it for bugs. I wrote this on my tablet but I have to test it on my computer.

 

[WarPigl3t]

Tested Code

Sub myMacro()
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    startCol = 3
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Do Until startCol > lastCol
        startColL = Chr(startCol + 64)
        If Range("A1").Value <> Range(startColL & 1).Value Then
            Columns(startColL).Insert
        End If
        Range("A1:A" & lastRow).Copy
        ActiveSheet.Paste Destination:=Range(startColL & 1)
        lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        startCol = startCol + 2
    Loop
    Application.CutCopyMode = False
End Sub

Here is the original dataset
[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B
[/TD]
[TD="width: 64, align: center"]C
[/TD]
[TD="width: 64, align: center"]D
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]1
[/TD]
[TD="width: 64"]ITEM#[/TD]
[TD="width: 64"]Store A[/TD]
[TD="width: 64"]Store B[/TD]
[TD="width: 64"]Store C[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]2
[/TD]
[TD="width: 64, align: right"]12345[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]3
[/TD]
[TD="width: 64, align: right"]45678[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]4
[/TD]
[TD="width: 64, align: right"]54321[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]5
[/TD]
[TD="width: 64, align: right"]87654[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]



Here is the Output after the macro
[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B
[/TD]
[TD="width: 64, align: center"]C
[/TD]
[TD="width: 64, align: center"]D
[/TD]
[TD="width: 64, align: center"]E
[/TD]
[TD="width: 64, align: center"]F
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]1
[/TD]
[TD="width: 64"]ITEM#[/TD]
[TD="width: 64"]Store A[/TD]
[TD="width: 64"]ITEM#[/TD]
[TD="width: 64"]Store B[/TD]
[TD="width: 64"]ITEM#[/TD]
[TD="width: 64"]Store C[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]2
[/TD]
[TD="width: 64, align: right"]12345[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]12345[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64, align: right"]12345[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]3
[/TD]
[TD="width: 64, align: right"]45678[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]45678[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]45678[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]4
[/TD]
[TD="width: 64, align: right"]54321[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]54321[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]54321[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]5
[/TD]
[TD="width: 64, align: right"]87654[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]87654[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]87654[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Grimm127]

Tested Code

Sub myMacro()
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    startCol = 3
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Do Until startCol > lastCol
        startColL = Chr(startCol + 64)
        If Range("A1").Value <> Range(startColL & 1).Value Then
            Columns(startColL).Insert
        End If
        Range("A1:A" & lastRow).Copy
        ActiveSheet.Paste Destination:=Range(startColL & 1)
        lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        startCol = startCol + 2
    Loop
    Application.CutCopyMode = False
End Sub

Here is the original dataset
[TABLE="class: grid, width: 256"]
<TBODY>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A

[/TD]
[TD="width: 64"]B

[/TD]
[TD="width: 64"]C

[/TD]
[TD="width: 64"]D

[/TD]
[/TR]
[TR]
[TD="width: 64"]1

[/TD]
[TD="width: 64"]ITEM#
[/TD]
[TD="width: 64"]Store A
[/TD]
[TD="width: 64"]Store B
[/TD]
[TD="width: 64"]Store C
[/TD]
[/TR]
[TR]
[TD="width: 64"]2

[/TD]
[TD="width: 64, align: right"]12345
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: right"]8
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 64"]3

[/TD]
[TD="width: 64, align: right"]45678
[/TD]
[TD="width: 64, align: right"]2
[/TD]
[TD="width: 64, align: right"]4
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 64"]4

[/TD]
[TD="width: 64, align: right"]54321
[/TD]
[TD="width: 64, align: right"]3
[/TD]
[TD="width: 64, align: right"]3
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 64"]5

[/TD]
[TD="width: 64, align: right"]87654
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
</TBODY>[/TABLE]



Here is the Output after the macro
[TABLE="class: grid, width: 384"]
<TBODY>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A

[/TD]
[TD="width: 64"]B

[/TD]
[TD="width: 64"]C

[/TD]
[TD="width: 64"]D

[/TD]
[TD="width: 64"]E

[/TD]
[TD="width: 64"]F

[/TD]
[/TR]
[TR]
[TD="width: 64"]1

[/TD]
[TD="width: 64"]ITEM#
[/TD]
[TD="width: 64"]Store A
[/TD]
[TD="width: 64"]ITEM#
[/TD]
[TD="width: 64"]Store B
[/TD]
[TD="width: 64"]ITEM#
[/TD]
[TD="width: 64"]Store C
[/TD]
[/TR]
[TR]
[TD="width: 64"]2

[/TD]
[TD="width: 64, align: right"]12345
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: right"]12345
[/TD]
[TD="width: 64, align: right"]8
[/TD]
[TD="width: 64, align: right"]12345
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 64"]3

[/TD]
[TD="width: 64, align: right"]45678
[/TD]
[TD="width: 64, align: right"]2
[/TD]
[TD="width: 64, align: right"]45678
[/TD]
[TD="width: 64, align: right"]4
[/TD]
[TD="width: 64, align: right"]45678
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 64"]4

[/TD]
[TD="width: 64, align: right"]54321
[/TD]
[TD="width: 64, align: right"]3
[/TD]
[TD="width: 64, align: right"]54321
[/TD]
[TD="width: 64, align: right"]3
[/TD]
[TD="width: 64, align: right"]54321
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
[TR]
[TD="width: 64"]5

[/TD]
[TD="width: 64, align: right"]87654
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: right"]87654
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64, align: right"]87654
[/TD]
[TD="width: 64, align: right"]1
[/TD]
[/TR]
</TBODY>[/TABLE]

I tried the code and it works to a certain point. When I have 57 columns; line:startColL = Chr(startCol + 64)
returns "[". So when it goes to line:If Range("A1").Value <> Range(startColL & 1).Value there is a global fail because the Range(startColL&1).value = [1.

Also a couple of quick questions. Why do you have 64 in startColL = Chr(startCol + 64) and what is the Range("A1").value is it 1? THank you so much for your time and reply. I really appreciate it.

 

[WarPigl3t]

Let me answer your questions in this reply and I'll come up with a fix on my next reply. You asked me why I use 64 in my equation. Well the start column is equal to 3 when the macro starts. Column 3 is the C column. When you use "Range" you have to use letters, not numbers for the column. So I use the "Chr" command to convert the column number to a letter. The problem with this method is that if the number of columns you are using exceeds column Z, then it will fail. You must have had columns that went all the way to AA or further. There is a fix and I'll work on it. Usually people don't need to go past Z column so I never add it to the code unless someone requests it like you did. The reason is because it adds more lines of code that most people don't need and it makes the code more confusing and less attractive. I origially was using Cells instead of Range to prevent this problem from occuring, but I needed to insert a column in the code. To insert a column, I cannot use a column number. I must use a column Letter. So I had to convert the column number to a letter.

Now for your other question about Range("A1").Value. Each column with data is reviewed by the code starting with column 3 also known as the C column. Range A1 has the value "ITEM". So the if statement is saying, if the column that is being reviewed in row 1 has the value "ITEM", then do not insert a new column. The rest of the code copy pastes the data on the current column without adding a new column

 

[Grimm127]

Let me answer your questions in this reply and I'll come up with a fix on my next reply. You asked me why I use 64 in my equation. Well the start column is equal to 3 when the macro starts. Column 3 is the C column. When you use "Range" you have to use letters, not numbers for the column. So I use the "Chr" command to convert the column number to a letter. The problem with this method is that if the number of columns you are using exceeds column Z, then it will fail. You must have had columns that went all the way to AA or further. There is a fix and I'll work on it. Usually people don't need to go past Z column so I never add it to the code unless someone requests it like you did. The reason is because it adds more lines of code that most people don't need and it makes the code more confusing and less attractive. I origially was using Cells instead of Range to prevent this problem from occuring, but I needed to insert a column in the code. To insert a column, I cannot use a column number. I must use a column Letter. So I had to convert the column number to a letter.

Now for your other question about Range("A1").Value. Each column with data is reviewed by the code starting with column 3 also known as the C column. Range A1 has the value "ITEM". So the if statement is saying, if the column that is being reviewed in row 1 has the value "ITEM", then do not insert a new column. The rest of the code copy pastes the data on the current column without adding a new column

Thank you for taking your time to reply. I also understand that the reason that the CHR was pulling a "[" character is because it's based off the ascii and the value the formula was pulling was the number that represented the "[" character. Looking forward to seeing your code. Thank you again.</SPAN></SPAN>

 

[WarPigl3t]

Found this code online. I've used it before so it looks similar to what I did and should work.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">    [COLOR=#00008B]If[/COLOR] iCol <= [COLOR=#800000]26[/COLOR] [COLOR=#00008B]Then
[/COLOR]        ColumnNumberToLetter = Chr(iCol + [COLOR=#800000]64[/COLOR])
    [COLOR=#00008B]Else
[/COLOR]        lRemainder = iCol [COLOR=#00008B]Mod[/COLOR] [COLOR=#800000]26
[/COLOR]        lAlpha = Int(iCol / [COLOR=#800000]26[/COLOR])
        [COLOR=#00008B]If[/COLOR] lRemainder = [COLOR=#800000]0[/COLOR] [COLOR=#00008B]Then
[/COLOR]            lRemainder = [COLOR=#800000]26
[/COLOR]            lAlpha = lAlpha - [COLOR=#800000]1
[/COLOR]        [COLOR=#00008B]End[/COLOR] [COLOR=#00008B]If
[/COLOR]        ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + [COLOR=#800000]64[/COLOR])
</code>[COLOR=#00008B][FONT=Consolas]        End [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]If
[/FONT][/COLOR]

Now here is my code in conjunction with the code I found online.

Sub myMacro()
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    startCol = 3
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Do Until startCol > lastCol
        iCol = startCol
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">    [COLOR=#00008B]If[/COLOR] iCol <= [COLOR=#800000]26[/COLOR] [COLOR=#00008B]Then
[/COLOR]        ColumnNumberToLetter = Chr(iCol + [COLOR=#800000]64[/COLOR])
    [COLOR=#00008B]Else
[/COLOR]        lRemainder = iCol [COLOR=#00008B]Mod[/COLOR] [COLOR=#800000]26
[/COLOR]        lAlpha = Int(iCol / [COLOR=#800000]26[/COLOR])
        [COLOR=#00008B]If[/COLOR] lRemainder = [COLOR=#800000]0[/COLOR] [COLOR=#00008B]Then
[/COLOR]            lRemainder = [COLOR=#800000]26
[/COLOR]            lAlpha = lAlpha - [COLOR=#800000]1
[/COLOR]        [COLOR=#00008B]End[/COLOR] [COLOR=#00008B]If
[/COLOR]        ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + [COLOR=#800000]64[/COLOR])
</code>[COLOR=#00008B][FONT=Consolas]        End [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]If
       startColL = ColumnNumberToLetter
[/FONT][/COLOR]        If Range("A1").Value <> Range(startColL & 1).Value Then
            Columns(startColL).Insert
        End If
        Range("A1:A" & lastRow).Copy
        ActiveSheet.Paste Destination:=Range(startColL & 1)
        lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        startCol = startCol + 2
    Loop
    Application.CutCopyMode = False
End Sub

I didn't feel like modifying either all of my code or all of the code I found online to match. So instead I just said startColL = ColumnNumberToLetter and iCol = startCol. You can get rid of those two lines of code if you change every instance of ColumnNumberToLetter to startColL and change every instance of iCol to startCol. It's easy to do in excel but I'm on my tablet and I'd have to do it manually. On excel you can just press Control F and click on Replace to replace the words. That way you don't accidentally delete something.