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

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

Does the col B on the output sheet come from col B on sheet "B"?

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

The data in Column B comes from Column A on Sheet B.

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

In that case try

Sub AlexVLZ()
   Dim RngB As Range, Cl As Range
   
   With Sheets("B")
      Set RngB = .Range("A2", .Range("A" & 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

DONE!!! OUTSTANDING!!!

With only a couple of adjustments, it now does what I want. And far more efficient than my code.

I will now review the "RESIZE" bit you added - I was unfamiliar with that command.

thanks you very much! May you ride to Vanlhalla peacefully!

Sub AlexVLZ()
   Dim RngB As Range, Cl As Range
   
   With Sheets("Datos")
      Set RngB = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
   End With
   RngB.Copy Sheets("Resultado").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(RngB.Count * 5)
   With Sheets("País")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Cl.Copy Sheets("Resultado").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * RngB.Count)
      Next Cl
   End With

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

You're welcome & thanks for the feedback

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

hi! sorry for coming up again...

Doubt, lets say the second tab "Details", contains data from cells from A to D. How could i change it to accomodate this?

thanks!

Alex

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

Which tab are you referring to when you say tab "Details", also in what way do you want to "accommodate it"

 

[AlexVLZ]

Re: Desperate noob: Copy&paste through a selection

Yes. The Details tab.

Your code takes a column A from the "Details" tab to paste it in column B in the "Results" tab. But can i amend the code in case we need to extract more columns from the "Details" tab, lets say that this tab has a column range from "A to D".

 

[Fluff]

Re: Desperate noob: Copy&paste through a selection

Try

Sub AlexVLZ()
   Dim RngB As Range, Cl As Range
   
   With Sheets("Datos")
      Set RngB = .Range("A1").CurrentRegion
   End With
   RngB.Copy Sheets("Resultado").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(RngB.Rows.Count * 5)
   With Sheets("País")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Cl.Copy Sheets("Resultado").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * RngB.Rows.Count)
      Next Cl
   End With
End Sub