copying few columns from one worksheet to new worksheet

[venkat356]

HI,

Am new to this forum.i have one worksheet from which i need to copy only few columns (Ex column A,B, D,G...need not be in sequence.....) to a new sheet.can you please help me to write a macro for this.In addition: Do we need to specify the number of rows in column or is there any way to get data till the end of column automatically.

Thanks in Advance.

 

[venkat356]

The number of columns on each sheet mostly remains same....but some times may vary.......

 

[VoG]

Try

Sub CopyCols()
Dim cols, LR As Long, ws1 As Worksheet
Dim i As Integer, iCol As Integer
Dim originalSetting As Integer, wb As Workbook, sht As Worksheet
cols = Array("A", "D", "G")
originalSetting = Application.SheetsInNewWorkbook
Application.ScreenUpdating = False
For Each sht In Sheets(Array("Monday", "Tuesday")) '<<<<<<<<<<<<<< change to suit
    Set ws1 = sht
    Set wb = Workbooks.Add
    iCol = 0
    With ws1
        For i = LBound(cols) To UBound(cols)
            iCol = iCol + 1
            LR = .Range(cols(i) & Rows.Count).End(xlUp).Row
            .Range(cols(i) & "1:" & cols(i) & LR).Copy Destination:=wb.Sheets("Sheet1").Cells(1, iCol)
        Next i
    End With
    wb.SaveAs ws1.Name & ".xls"
    wb.Close savechanges:=False
Next sht
Application.CutCopyMode = False
Application.SheetsInNewWorkbook = originalSetting
Application.ScreenUpdating = True
End Sub

 

[jmthompson]

VoG II- I am just curious here, but if venkat356 needs to select which sheets will be converted to files each time, could this code be modified to include and InputBox for the user to enter the appropriate sheet name? Of course, the user will need to run the macro for each applicable sheet...

 

[venkat356]

HI Vog,

its great worked exactly the way i requested.........Thanks a lot...

vog as jmthompson said can i have the chance of including a inputbox so tha ti can choose the required one and generate the book...please suggest me......Thanks thompson..

 

[VoG]

With an InputBox (but no error check to make sure the sheet(s) exist!)

Sub CopyCols()
Dim cols, LR As Long, ws1 As Worksheet
Dim i As Integer, iCol As Integer, j As Integer
Dim originalSetting As Integer, wb As Workbook
Dim MySheets As String, ListSheets
cols = Array("A", "D", "G")
MySheets = InputBox("Enter list of sheets separated by spaces")
If MySheets = "" Then Exit Sub
ListSheets = Split(MySheets)
originalSetting = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Application.ScreenUpdating = False
For j = LBound(ListSheets) To UBound(ListSheets)
    Set ws1 = Sheets(ListSheets(j))
    Set wb = Workbooks.Add
    iCol = 0
    With ws1
        For i = LBound(cols) To UBound(cols)
            iCol = iCol + 1
            LR = .Range(cols(i) & Rows.Count).End(xlUp).Row
            .Range(cols(i) & "1:" & cols(i) & LR).Copy Destination:=wb.Sheets("Sheet1").Cells(1, iCol)
        Next i
    End With
    wb.SaveAs ws1.Name & ".xls"
    wb.Close savechanges:=False
Next j
Application.CutCopyMode = False
Application.SheetsInNewWorkbook = originalSetting
Application.ScreenUpdating = True
End Sub

 

[jmthompson]

I can always learn something from you, Vog, thanks!

 

[venkat356]

HI vog thanks for ur code..it worked exactly the way i wanted...........

 

[venkat356]

HI Vog

how to include checkbox rather than inputbox in the above code......

checkbox what imean is it will have all the names and which ever needed can be checked to create the output........

please help me....

Regards.....

 

[VoG]

HI Vog

how to include checkbox rather than inputbox in the above code......

checkbox what imean is it will have all the names and which ever needed can be checked to create the output........

please help me....

Regards.....

That sounds like you want me to create a UserForm to go with this code

Now, I'm happy to help but not to design entire projects. Take a look at this http://www.contextures.com/xlUserForm01.html - it should get you started.

 

[venkat356]

HI Vog,

Thank you very much for your help.The link you provided is very good,i was looking for such resource.I dont know coding am just learning and the site has simple steps to learn.I will try with that and will get back to you if i need any help.

Regards,