Moving multiple sets of columns to new tabs via VBA

[mjd]

Hello,

We get an excel data sheet sent to us weekly that has security information all on one excel worksheet - each security has two columns (ie - A:B, C:D, E:F, etc etc), and we need to copy each security to its own worksheet and save that sheet as a text file for my company to import this data into our accounting software. Now, this could be upwards of 250 columns, or 125 worksheets. Is it possible to automate this? I have mocked up what it would look like for columns A:B -> Sheet2, I just don't know how to proceed to get it to loop so that columns C:D copy to Sheet3 and so on.

    Sub DataMove()    Columns("A:B").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "'"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C14")
    Range("C2:C14").Select
    Range("A2:C14").Select
    Range("C14").Activate
End Sub

Any thoughts would be greatly appreciated.

Thanks,
Mike

 

[mumps]

Does the data you want copied end at the same row in every column? Do the individual security sheets already exist or do they have to be created by the macro? If they already exist, how are they named? If they have to be created, how do you want them named? Can you explain what you are trying to do with this code:

 Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "'"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C14")

 

[mjd]

Does the data you want copied end at the same row in every column? Do the individual security sheets already exist or do they have to be created by the macro? If they already exist, how are they named? If they have to be created, how do you want them named? Can you explain what you are trying to do with this code:

 Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "'"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C14")

Hey Mumps, thanks for the response

1) The row counts will not be the same across the paired column sets, unfortunately.

2) That bit of code to insert the " ' " into column C on the destination tab. Our accounting software requires a third column to accept these files via import, so we are forced to used a blank field for all the corresponding data in the preceding columns.

3) the tabs will not exist beyond Sheet1, Sheet2, Sheet3 that is standard in excel. Each tab will need to be named according to the security, which will be found in Row 1, Column 1 for each paired grouping (a1, c1, e1, etc). So if c1 is "987hgf123" sheet4 would be retitled "987hgf123" and the file would be saved as a tab separated text with an extension of ".rte"

Thanks for your time!! I really appreciate it!

mike

 

[mumps]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim lColumn As Long
    lColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim x As Long
    For x = 1 To lColumn - 1 Step 2
        LastRow = Sheets("Sheet1").Columns(x).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Sheets("Sheet1").Cells(1, x)
        Sheets("Sheet1").Cells(1, x).Resize(LastRow, 2).Copy Cells(1, 1)
        Cells(1, 3).Resize(LastRow) = "'"
    Next x
    Application.ScreenUpdating = True
End Sub

 

[mjd]

!!! Hot ****, that works perfectly! Thank you so very very much, you just saved me hours and hours or work every week. you are a life saver and i salute you!!

 

[mumps]

You are very welcome.