Copy and transpose a range of cell every 10 columns?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top