Copy and transpose a range of cell every 10 columns?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,712
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
I want to be able to copy data back and forth using two macros that can transpose the info,

so the first one is Grid to Line

With this one i want to copy Sheet"New" Range ("I11:R27")
to Sheet"Data" find first empty row in column D and paste it. as one row so transpose it every 10 columns so i end up with one row of data,

the second might be more tricky,

what i need is for the macro to find the row with the data in it buy using the data in Sheet New,cell D5

then copy the row columns P:GD transposing every 10 columns

please help if you can
thanks
Tony
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Sub TRANSPOSEDATA()
Dim WB As Workbook
Dim Rng As Range, cll As Range
Dim DataSht As Worksheet, NewSht As Worksheet
Dim LstRw As Long, Rw As Long
Set WB = ThisWorkbook
Set DataSht = WB.Worksheets("Data")
Set NewSht = WB.Worksheets("New")

Set Rng = NewSht.Range("I11").CurrentRegion

''clear previous Data
LstRw = DataSht.Cells(DataSht.Rows.Count, "D").End(xlUp).Row
DataSht.Cells(2, 4).Resize(LstRw + Rw, 1).Clear

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'New Last Row after Clear Data
LstRw = DataSht.Cells(DataSht.Rows.Count, "D").End(xlUp).Row

For Each cll In Rng
Rw = Rw + 1
    With DataSht.Cells(LstRw + Rw, 4)
    .Value = cll.Value
        With .Interior
            .Pattern = xlSolid
            .Color = RGB(250, 250, 250)
        End With
    
    End With
Next

End Sub

AAAAABAACAADAAEAafAAGAAHAAIAAJ
1173349658197113129145
2183450668298114130146
3193551678399115131147
42036526884100116132148
52137536985101117133149
62238547086102118134150
72339557187103119135151
82440567288104120136152
92541577389105121137153
102642587490106122138154
112743597591107123139155
122844607692108124140156
132945617793109125141157
143046627894110126142158
153147637995111127143159
163248648096112128144160




Book1
D
1
2AAA
3AAB
4AAC
5AAD
6AAE
7Aaf
8AAG
9AAH
10AAI
11AAJ
121
1317
1433
1549
1665
1781
1897
19113
20129
21145
222
2318
2434
2550
2666
2782
2898
29114
Data
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,712
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you DossFM
lot of usefull stuff i can use here
Thanks
Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,126,888
Messages
5,621,416
Members
415,839
Latest member
Pollydooner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top