Naislou
09-27-2019, 02:37 AM
Hello everyone,
I hope you are well. I am new to this forum. The reason I am coming is that I have never used VBA code until now.
I basically want to extract specific columns in a defined order and with the name of a cell (cell A1) of the file I am copying from.
I have come up with this code It sometimes work with less columns but eventually it comes up with an issue on the Range line.
Can someone help me please?:crying:
Thank you!!
Sub Macro1()
'Step 1 Copy the data
Sheets("Data").Range("A11:A5000,B11:B5000,U11:U5000,V11:V5000,W11:W5000,X11:X5000,C11:C5000,E11:E 5000,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,AX 11:AX5000,AY11:AY5000").Copy
'Step 2 Create a new workbook
Workbooks.Add
'Step 3 Paste the data
ActiveSheet.Paste Destination:=Range("A1")
'Step 4 Turn off application alerts
Application.DisplayAlerts = False
'Step 5 Save the newly created workbook
ActiveWorkbook.SaveAs _
Filename:="C:TempMyNewBook.xlsx"
'Step 6 Turn application alerts back on
Application.DisplayAlerts = True End Sub
I hope you are well. I am new to this forum. The reason I am coming is that I have never used VBA code until now.
I basically want to extract specific columns in a defined order and with the name of a cell (cell A1) of the file I am copying from.
I have come up with this code It sometimes work with less columns but eventually it comes up with an issue on the Range line.
Can someone help me please?:crying:
Thank you!!
Sub Macro1()
'Step 1 Copy the data
Sheets("Data").Range("A11:A5000,B11:B5000,U11:U5000,V11:V5000,W11:W5000,X11:X5000,C11:C5000,E11:E 5000,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,AX 11:AX5000,AY11:AY5000").Copy
'Step 2 Create a new workbook
Workbooks.Add
'Step 3 Paste the data
ActiveSheet.Paste Destination:=Range("A1")
'Step 4 Turn off application alerts
Application.DisplayAlerts = False
'Step 5 Save the newly created workbook
ActiveWorkbook.SaveAs _
Filename:="C:TempMyNewBook.xlsx"
'Step 6 Turn application alerts back on
Application.DisplayAlerts = True End Sub