Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110155

Re: Assistance in transposing multiple sets of data

Show all headers | View raw

Hi Mark,

Am Thu, 10 Aug 2017 05:01:07 -0700 (PDT) schrieb Living the Dream:

> Required Format: ( Sheet 2 )
> 
> _____A________B________C_______D_________E_________F__________G_______________
> 1___Date_____Unit____HrsTot__HrsIdle__HrsActive___Loads____Rev/Cost___________
> 2__26/6/17___C001____15.25_____0_______15.25_______6_________0.00_____________
> 3__26/6/17___C002_____0.00_____0________0.00_______0_________0.00_____________
> 4__Etc.....

I don't know if I understood your table layout correctly. 
Try:

Sub TransposeTable()
Dim rng1 As Range, rng2 As Range
Dim i As Integer, rowsC1 As Integer, rowsC2 As Integer
Dim Lrow As Long, j As Long

With Sheets("Sheet1")
   Lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
   For j = 9 To Lrow Step 48
      For i = 1 To 31 Step 6
         Set rng1 = .Range(.Cells(j, i), .Cells(j + 13, i + 5))
         Set rng2 = .Range(.Cells(j + 18, i), .Cells(j + 37, i + 5))
         rowsC1 = rng1.Rows.Count
         rowsC2 = rng2.Rows.Count
         Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp)(2) _
            .Resize(rowsC1, 6).Value = rng1.Value
         Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp)(2) _
            .Resize(rowsC2, 6).Value = rng2.Value
         Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2) _
            .Resize(rowsC1 + rowsC2) = .Cells(j - 2, i)
      Next
   Next
End With
End Sub


Regards
Claus B.
-- 
Windows10
Office 2016

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Assistance in transposing multiple sets of data Living the Dream <noodnutt@gmail.com> - 2017-08-10 05:01 -0700
  Re: Assistance in transposing multiple sets of data Claus Busch <claus_busch@t-online.de> - 2017-08-10 16:07 +0200
    Re: Assistance in transposing multiple sets of data Living the Dream <noodnutt@gmail.com> - 2017-08-11 04:06 -0700
      Re: Assistance in transposing multiple sets of data Claus Busch <claus_busch@t-online.de> - 2017-08-11 13:36 +0200
        Re: Assistance in transposing multiple sets of data Living the Dream <noodnutt@gmail.com> - 2017-08-11 05:05 -0700
          Re: Assistance in transposing multiple sets of data Living the Dream <noodnutt@gmail.com> - 2017-08-11 05:13 -0700
            Re: Assistance in transposing multiple sets of data Claus Busch <claus_busch@t-online.de> - 2017-08-11 14:15 +0200
          Re: Assistance in transposing multiple sets of data GS <gs@v.invalid> - 2017-08-11 15:33 -0400
            Re: Assistance in transposing multiple sets of data Living the Dream <noodnutt@gmail.com> - 2017-08-29 06:14 -0700
              Re: Assistance in transposing multiple sets of data GS <gs@v.invalid> - 2017-08-29 11:11 -0400
                Re: Assistance in transposing multiple sets of data Claus Busch <claus_busch@t-online.de> - 2017-08-29 18:29 +0200
                Re: Assistance in transposing multiple sets of data GS <gs@v.invalid> - 2017-08-29 15:01 -0400

csiph-web