Solved Copy and paste a row in a new worksheet VBA

September 26, 2019 at 08:43:45
Specs: Windows 10
Hi Everyone,
I am stuck, I want to copy specific columns and paste them in a specific order in a new workbook named with the value of a specific cell.

Here are the columns I need to copy and paste,
A11:A5000,B11:B5000,U11:U5000,V11:V5000,W11:W5000,X11:X5000,C11:C5000,E11:E5000,DA11:DA5000,L11:L5000,G11:G5000,J11:J5000,BQ11:BQ5000,BR11:BR5000,BS11:BS5000,BV11:BV5000,CG11:CG5000,N11:N5000,P11:P5000,AP11:AP5000,AQ11:AQ5000,AS11:AS5000,AT11:AT5000,AU11:AU5000,BG11:BG5000,AV11:AV5000,AW11:AW5000,AX11:AX5000,AY11:AY5000

What should I do?!

Thank you for your help,

Anais


See More: Copy and paste a row in a new worksheet VBA

Reply ↓  Report •

#1
September 26, 2019 at 09:34:49
Since all of your Row numbers are the same, you only need to worry about your Column letters.

Load the Column letters into a VBA array and loop through the array, doing a copy/paste, Column by Column.

Append the array elements (the Column letters) to your static row numbers each time through the loop.

If you need help with that, let us know.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
September 27, 2019 at 05:27:43
✔ Best Answer
I played with this a little bit and came up with something that might work for you.

Since I don't know the details of your workbook layout, the following code will copy data from Sheet(1) and begin the paste in Sheet(2)!A1.

With more specifics, the code can be modified to fit your needs.

Sub CopyInOrder()

'Load Columns letters into array
  Dim myArr As Variant
  myArr = Split("A:B:U:V:W:X:C:E:DA:L:B:J:" & _
                "BQ:BR:BS:BV:CG:N:P:AP:AQ:" & _
                "AS:AT:AU:BG:AV:AW:AX:AY", ":")

'Hide the copying until done
  Application.ScreenUpdating = False

'Loop through Array, Copy, Paste
    For nxtCol = 0 To UBound(myArr)
       Sheets(1).Range(myArr(nxtCol) & "11:" & _
                       myArr(nxtCol) & "5000").Copy _
       Sheets(2).Columns(nxtCol + 1)
    Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question