Copying Columns to each notepad

[xsdip]

I am very new to excel VBA. I have learned the basics of VBA programing language. Could you please anyone help me in this.

Assuming I have numbers in F9 to F208996 and G9 to G330571 etc.

How do I capture these numbers and convert it into separate nodepad files. i.e. I mean to say, F column datas in one textpad and G column datas in another textpad etc.

In figure below, I need to capture the bold numbers. The columns to be converted to each notepad are F,G,H,I always. The row to be converted to notepad starts always from 9th row till lot of numbers

I have made a rough code. But not sure how to write loop.

Sub Export()
Dim lastrow As Long
Dim lastcolumn As Long
Dim i As Integer, j As Integer

lastrow = Cells(1, "A").End(xlDown).Row
lastcolumn = activesheets.Cells(1, Column.Count).End(xlToLeft).Column

For i = 1 To lastrow
For j = 1 To lastcolumn



Next j
Next i


End Sub

 

[Alex Blakenburg]

1) You need to double click on the folder you want to use
2) And update this line as per the below (adding the slash to the end)

folder_Name = .SelectedItems(1) & "\"

 

[xsdip]

1) You need to double click on the folder you want to use
2) And update this line as per the below (adding the slash to the end)

folder_Name = .SelectedItems(1) & "\"

I gave the same. Double clicked and added \ at the end of the folder_name. But still textpad is generating outside the folder.

 

[Alex Blakenburg]

Tell me what folder you want it to appear in and then give me a screen shot of the dialogue box just before you hit OK

eg the below saves the files to the folder "MXF MrExcel Test folder"

 

[xsdip]

1) You need to double click on the folder you want to use
2) And update this line as per the below (adding the slash to the end)

folder_Name = .SelectedItems(1) & "\"

This works perfectly in Excel 2016. But when I work on Excel 2013, text files gets generated outside the folder even if we double click and give backslash.

Regarding the Folder selection, referring to the image which you have provided, I am double clicking on MXF MrExcel Test Folder(So it will go inside the folder) and then only I am clicking Ok.

If we do this, in excel 2016, text file generates inside the "MXF MrExcel Test Folder" itself. But in Excel 2013, textfile generates inside "Test" Folder only (Referring to image). i.e. outside of the "MXF MrExcel Test Folder"

Why is it coming like that? Could you please help me.

 

[Alex Blakenburg]

I don't have 2013 so I can't test it. Since you have it working in 2016, you are obviously doing the right thing.
It doesn't make sense that it works differently in 2013.

 

[xsdip]

Okay Sure. I will work on the same. Thank you so much for all the learning.

 

[Alex Blakenburg]

Happy to help. I am login off for the night.

 

[xsdip]

Hi @Alex Blakenburg

As an extension to the above code, I need to loop through a folder and Pick the text files and import it to excel and do some calculations on the excel and I have to export some columns from excel to Textpad.

So I have created a code to loop through the folders and pick the files and did the calculation part also. But while exporting to textpad, it asks everytime to pick the location for the textpad to get saved. Could you please help me to save all the exported textpad by selecting the path Location once.

Below is my code

'Looping through file in a folder
With Application.Filedialog (msofiledialogfolderpicker)
.title ="Please select folder"
.show
.allowmultiselect = false

myfolder = .selecteditems (1) & "\"
textfiletotopen = Dir (myfolder)

End with

Do while textfiletoopen <>""

'''''-------Here I have the code to import the textfile from the folder and do the required calculations.""""""

""----------Below is the code which you have helped me for exporting some columns to textpad"

Application.ScreenUpdating = False

Set wbRead = ThisWorkbook
Set shRead = wbRead.ActiveSheet

Set cellStart = shRead.Range("K8") '<----- Update starting position
firstRow = cellStart.Row + 1
currCol = cellStart.Column
name = Range("L5"

' Get the last cell with data in row 1
lastCol = shRead.Cells(cellStart.Row, shRead.Columns.Count).End(xlToLeft).Column

With Application.FileDialog(msoFiledialogFolderPicker)
.allowmultiselect = False
.title = "Select.."
If .show = 0then exit sub
folder_name = .selecteditems(1)
End with

For i = cellStart.Column To lastCol
Set wbTemp = Workbooks.Add
Set shTemp = ActiveSheet
With shRead
.Range(.Cells(firstRow, i), .Cells(Rows.Count, i).End(xlUp)).Copy
End With

shTemp.Range("A1").PasteSpecial Paste:=xlPasteValues
file_Name = shRead.Cells(cellStart.Row, i).Value

Application.DisplayAlerts = False
wbTemp.SaveAs fileName:=folder_Name & file_Name, _
FileFormat:=xlTextMSDOS, CreateBackup:=False
wbTemp.Close SaveChanges:=False
Application.DisplayAlerts = True
Next i

Application.ScreenUpdating = True

textfiletoopen = Dir

Loop

End Sub

 

[Alex Blakenburg]

Move the code below so that it is not inside the Do ...... Loop section and put it before the Do statement.

        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Title = "Select.."
            If .Show = 0 Then Exit Sub
            folder_Name = .SelectedItems(1)
        End With

 

[xsdip]

Move the code below so that it is not inside the Do ...... Loop section and put it before the Do statement.

        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Title = "Select.."
            If .Show = 0 Then Exit Sub
            folder_Name = .SelectedItems(1)
        End With

This is of great help. Thank you so much .. ..