Copy & paste through a selection

[AlexVLZ]

Hello all,

First post here!

I am leaning VBA through trial and error, and I need some guidance.

Requirements:

• Tab A (input): a fix list of countries of 4 (Column A: Spain, UK, USA, Italy)
• Tab B (input): a variable table of data.
• Tab C (output): Combine data from Tab A and B. I need to have a table of data (column B) for each country (column A). The code will need to do this for each country in Tab A.

I can manage to copy&paste the data correctly for Spain, but I cannot get the VBA to select the next country from the Tab A list...take it to Tab C and then paste the table (Table from Tab B) next to it.... and so on

MANY THANKS FOR THE HELP!

Alex

 

[onlyadrafter]

Re: Desperate noob: Copy&paste through a selection

Hello,

Does this work as expected?

Sub combined()
    With Sheets("A")
        For MY_A_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
            MY_A = .Range("A" & MY_A_ROWS).Value
        With Sheets("B")
            For MY_B_ROWS = 1 To .Range("A" & Rows.Count).End(xlUp).Row
                MY_AB = MY_A & .Range("A" & MY_A_ROWS).Value
                Sheets("C").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_AB
            Next MY_B_ROWS
        End With
        Next MY_A_ROWS
    End With
End Sub

Have assumed tab names are A, B and C. Change as required.

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

When you say you have TAB A, B & C are these 3 separate sheets, or do you have 1 input sheet with countries in column A & data in col B?

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

wow!! Almost!! But in Sheet A in Column A and Data in Column B

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

Yes, with Tabs I mean different sheets. So Sheet A, B and C. So the purpose is to combine the first two in the second. The previous reply is a very good one, but he concatenated the A and B, when I need these in separate columns. Still, pretty good.

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

Sorry my friend for making you waste your time. Please ignore my previous post.

The VBA gave me this result:

[TABLE="width: 88"]
<tbody>[TR]
[TD]EspañaAAAA[/TD]
[/TR]
[TR]
[TD]EspañaAAAA[/TD]
[/TR]
[TR]
[TD]EspañaAAAA[/TD]
[/TR]
[TR]
[TD]EspañaAAAA[/TD]
[/TR]
[TR]
[TD]EcuadorBBBB[/TD]
[/TR]
[TR]
[TD]EcuadorBBBB[/TD]
[/TR]
[TR]
[TD]EcuadorBBBB[/TD]
[/TR]
[TR]
[TD]EcuadorBBBB[/TD]
[/TR]
[TR]
[TD]FranciaCCCC[/TD]
[/TR]
[TR]
[TD]FranciaCCCC[/TD]
[/TR]
[TR]
[TD]FranciaCCCC[/TD]
[/TR]
[TR]
[TD]FranciaCCCC[/TD]
[/TR]
</tbody>[/TABLE]

Which is close to what I need. Whis is:

[TABLE="width: 88"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]España[/TD]
[TD]AAAA[/TD]
[/TR]
[TR]
[TD]España[/TD]
[TD]BBBB[/TD]
[/TR]
[TR]
[TD]España[/TD]
[TD]CCCC[/TD]
[/TR]
[TR]
[TD]España[/TD]
[TD]DDDD[/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD]AAAA[/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD]BBBB[/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD]CCCC[/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD]DDDD[/TD]
[/TR]
[TR]
[TD]Francia[/TD]
[TD]AAAA[/TD]
[/TR]
[TR]
[TD]Francia[/TD]
[TD]BBBB[/TD]
[/TR]
[TR]
[TD]Francia[/TD]
[TD]CCCC[/TD]
[/TR]
[TR]
[TD]Francia[/TD]
[TD]DDDD[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Many thanks

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

How about

Sub AlexVLZ()
   Dim RngB As Range, Cl As Range
   
   With Sheets("B")
      Set RngB = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
   End With
   RngB.Copy Sheets("C").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(RngB.Count * 4)
   With Sheets("A")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Cl.Copy Sheets("C").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * RngB.Count)
      Next Cl
   End With
End Sub

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

Hmmm not quite. I got this far with my code below.

MY ERRORS:
1) The Data i correctly getting pasted in column B, but from cell B2. I want it to be from B1 down .
2) The country list is getting copied across, but not repeated in sequence --because i dont know how to write down that bit of the code.

Sub MACRO()

Application.ScreenUpdating = False

'COPY&PASTE DATA IN RESULTS
        Dim x As Integer
        For x = 1 To 4
            Dim copySheet As Worksheet
            Dim pasteSheet As Worksheet
            Worksheets("Resultado").Activate
            Set copySheet = Worksheets("Datos")
            Set pasteSheet = Worksheets("Resultado")
            Range("A" & (ActiveCell.Row)).Select
            copySheet.Range("A1:A4").Copy
            pasteSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1).Activate
            
        Next x

'INSERT COLUMN

Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'COPY&PASTE COUNTRY RESULT

    Dim i As Integer
    For i = 1 To 4
    
        Sheets("País").Select
        Range("A1:A4").Select
        Selection.Copy
        Sheets("Resultado").Select
        Range("A1:A1").Select
        ActiveSheet.Paste
    Next i
        
Worksheets("Resultado").Activate

End Sub

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

Did you try the code I suggested?

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

Happy new year!

Yes, I tried it. The results were:

1) Your code did repeat the countries twice each, which is VERY close to what I need in column A. How did you code it to repeat twice? As what I need is that each country get repeated 5 times (one per line of data in column B). The data coming from Sheet A and B can be variable, sometimes 5 lines or 5000 lines.

2) Column B came out empty. In theory there should be a block of data of 5 lines per country listed in column A.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]España[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]España[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]Francia[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]Francia[/TD]
[TD](empty)[/TD]
[/TR]
[TR]
[TD]....[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]