Copy across rows but miss columns

jl2509 · Nov 21st 2018

Hi All,

I would appreciate some help on the following please.

Sheet1 - Data1 is in Cell E11, then Data2 is in K11, then Data 3 is in Q11 etc

I want to be able to show the data on sheet2 but in sequential coumns by dragging cell B3 across tthe columns

i.e Data 1 in Cell B3, Data2 in Cell C3, Data3 in Cell D3 etc.

I have played around with index and columns to create a formula to copy this accross but cannot accomplish it.

If anyone can put light on this, can you please exaplin what the steps are in the index and columns forula (if you go this way)

Thanks

**alansidman** · Nov 21st 2018

Here is a VBA solution

Code

Option Explicit


Sub Across()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Application.Union(s1.Range("E11"), s1.Range("K11"), s1.Range("Q11")).Copy s2.Range("B3")
    Application.CutCopyMode = False


End Sub

Display More

jl2509 · Nov 21st 2018

Hi Appreciate the repsonse,

Is there a way to do this in a formula not a macro?

Thanks

**alansidman** · Nov 21st 2018

highlight E11, hold down the control key, Highlight K11, hold down control key, highlight Q11. Select Copy. Go to Sheet2, Cell B3. Click on Paste.

No formula function for this.

jl2509 · Nov 22nd 2018

Hi All,

Sorry to be a pain, but this is not my intent.

This is not just about copying the data, it needs to be dynamic hence as records on sheet 1 change. I need to use a formula such as offset(columns, index(columns etc but dont know how to achaieve this.

Thanks

jl2509 · Nov 22nd 2018

managed to sort this now, appreciate the help offered.

Copy across rows but miss columns

Participate now!

Share