How to copy and paste rows into alternate starting columns

Jiriyanu

New Member
Joined
Dec 11, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I'm trying to prepare data for import and the software requires me to use two sets of headers in order to import 2 sets of data into one page (2 forms in one page, think tax forms). Problem is I have clients that have over 50 people that need to each have forms.

Basically, I'd like to have all odd rows (A7:Z7, A9:Z9, etc) of sheet A to be pasted starting at A2 of sheet B, and all even rows (A8:Z8, A10:Z10, etc) to be pasted starting at AA2 of sheet B.

Honestly I'm not sure where to start with this coding. I'm still very much a beginner.

Any and all help is appreciated. Thank you!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Jiriyanu()
   Dim UsdRws As Long
   Dim Orws As Variant, Erws As Variant, Oary As Variant, Eary As Variant
   
   With Sheets("Sheet1")
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
   
      Orws = Application.Transpose(Filter(Evaluate("Transpose(if(isodd(row(7:" & UsdRws & ")),row(7:" & UsdRws & "),""x""))"), "x", False))
      Erws = Application.Transpose(Filter(Evaluate("Transpose(if(iseven(row(7:" & UsdRws & ")),row(7:" & UsdRws & "),""x""))"), "x", False))
      Oary = Application.Index(.Range("A1:Z" & UsdRws).Value, Orws, Evaluate("column(A:Z)"))
      Eary = Application.Index(.Range("A1:Z" & UsdRws).Value, Erws, Evaluate("column(A:Z)"))
   End With
   With Sheets("Sheet2")
      .Range("A2").Resize(UBound(Oary), 26).Value = Oary
      .Range("AA2").Resize(UBound(Eary), 26).Value = Eary
   End With
End Sub
 
Solution

Jiriyanu

New Member
Joined
Dec 11, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
How about
VBA Code:
Sub Jiriyanu()
   Dim UsdRws As Long
   Dim Orws As Variant, Erws As Variant, Oary As Variant, Eary As Variant
  
   With Sheets("Sheet1")
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
  
      Orws = Application.Transpose(Filter(Evaluate("Transpose(if(isodd(row(7:" & UsdRws & ")),row(7:" & UsdRws & "),""x""))"), "x", False))
      Erws = Application.Transpose(Filter(Evaluate("Transpose(if(iseven(row(7:" & UsdRws & ")),row(7:" & UsdRws & "),""x""))"), "x", False))
      Oary = Application.Index(.Range("A1:Z" & UsdRws).Value, Orws, Evaluate("column(A:Z)"))
      Eary = Application.Index(.Range("A1:Z" & UsdRws).Value, Erws, Evaluate("column(A:Z)"))
   End With
   With Sheets("Sheet2")
      .Range("A2").Resize(UBound(Oary), 26).Value = Oary
      .Range("AA2").Resize(UBound(Eary), 26).Value = Eary
   End With
End Sub
Thank you so much, Fluff! This worked perfectly with no errors. 😊
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,138
Messages
5,640,337
Members
417,139
Latest member
madcabbie

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