In my workbook there are several work sheets are available and I want to save
the each worksheets B COLUMN CELL CONTENTS in a SEPARATE NOTEPAD FILES with
the SHEET NAMES AS FILE NAME.

For example in sheet1 the B COLUM cell contents should be exported as a
notepad file with the sheet name as file name in my desktop and like that it
should export the remaining sheets B column contents in a separate separate
notepad files and it should automatically save the files in my desk top when
I run a macro.

At the same time the note pad file should be generated by way of selecting
the start to end of the cell contents in b column and there will not be any
blank cell in between the B column range. So the macro should export only
the cells which are having the content continuously in B column.

If, anyone knows it, then please provide the VB code for the same and thanks
in advance.

TGV

Hi,

Try this. Alt +F11 to open VB editor, right click 'ThisWorkbook' and insert
module and paste the code below in. If the notepad file already exists it is
deleted and a new one created that contains column B

Sub write_Texts()
Dim WS As Worksheet
Dim x As Long, lastrow As Long
saveDir = "C:\" 'Change to suit
For Each WS In Worksheets
lastrow = WS.Cells(Cells.Rows.Count, "B").End(xlUp).Row
targetfile = saveDir & WS.Name & ".txt"
If Dir(targetfile) < "" Then Kill targetfile
Open targetfile For Output As #1
For x = 1 To lastrow
Print #1, WS.Cells(x, 2).Value
Next
Close #1
Next
End Sub


Mike

"TGV" wrote:

In my workbook there are several work sheets are available and I want to save
the each worksheets B COLUMN CELL CONTENTS in a SEPARATE NOTEPAD FILES with
the SHEET NAMES AS FILE NAME.

For example in sheet1 the B COLUM cell contents should be exported as a
notepad file with the sheet name as file name in my desktop and like that it
should export the remaining sheets B column contents in a separate separate
notepad files and it should automatically save the files in my desk top when
I run a macro.

At the same time the note pad file should be generated by way of selecting
the start to end of the cell contents in b column and there will not be any
blank cell in between the B column range. So the macro should export only
the cells which are having the content continuously in B column.

If, anyone knows it, then please provide the VB code for the same and thanks
in advance.

TGV

Thank you mike, you are always helping me to finish up my job very easily.
Once again Thank you very much!!!

TGV

"Mike H" wrote:

Hi,

Try this. Alt +F11 to open VB editor, right click 'ThisWorkbook' and insert
module and paste the code below in. If the notepad file already exists it is
deleted and a new one created that contains column B

Sub write_Texts()
Dim WS As Worksheet
Dim x As Long, lastrow As Long
saveDir = "C:\" 'Change to suit
For Each WS In Worksheets
lastrow = WS.Cells(Cells.Rows.Count, "B").End(xlUp).Row
targetfile = saveDir & WS.Name & ".txt"
If Dir(targetfile) < "" Then Kill targetfile
Open targetfile For Output As #1
For x = 1 To lastrow
Print #1, WS.Cells(x, 2).Value
Next
Close #1
Next
End Sub


Mike

"TGV" wrote:

In my workbook there are several work sheets are available and I want to save
the each worksheets B COLUMN CELL CONTENTS in a SEPARATE NOTEPAD FILES with
the SHEET NAMES AS FILE NAME.

For example in sheet1 the B COLUM cell contents should be exported as a
notepad file with the sheet name as file name in my desktop and like that it
should export the remaining sheets B column contents in a separate separate
notepad files and it should automatically save the files in my desk top when
I run a macro.

At the same time the note pad file should be generated by way of selecting
the start to end of the cell contents in b column and there will not be any
blank cell in between the B column range. So the macro should export only
the cells which are having the content continuously in B column.

If, anyone knows it, then please provide the VB code for the same and thanks
in advance.

TGV

Dear Mike,

I require another vba code from you and here after I will not disturb you.
In A column I am having some data from a1 to 6000 that is a6000 cells
continuously and there will not be any blank cells. At the same time the
data will be vary and the 6000 cells are not stable value. So the macro
should itself select the range from A1 Cell to ending cell.

Now in C Column I want a macro to fill the C1 column cell serial number as 1
and the C2 Column cell serial number as 2 and the C3 column cell serial
number as 3 like this it should fill the continuous numeric series up to the
data which is available in A column. Here is the criteria if the C Column
cell serial number touches 200 then I want the C201 cell serial number as 1
and C202 cell serial number as 2 and C203 cell serial number as 3 like this
it should automatically start numbering 1 to 200 for every 200 cells.

(Explaining the above in brief:
In C401 cell I want the cell serial number as 1 and c402 cell I want the
serial number as 2 like this it should continue and if it reaches C601 cell
then again I want the cell serial number as 1 and C602 cell serial number as
2 like this it should calculate for every 200 cells.)

Finally I want to concatenate the A1,C1 & D1 values in B1 Cell and the same
formula should be pasted B Column upto the data available in A column.

Hope I have explained the same clearly.

Thank you,

TGV



"TGV" wrote:

Thank you mike, you are always helping me to finish up my job very easily.
Once again Thank you very much!!!

TGV

"Mike H" wrote:

Hi,

Try this. Alt +F11 to open VB editor, right click 'ThisWorkbook' and insert
module and paste the code below in. If the notepad file already exists it is
deleted and a new one created that contains column B

Sub write_Texts()
Dim WS As Worksheet
Dim x As Long, lastrow As Long
saveDir = "C:\" 'Change to suit
For Each WS In Worksheets
lastrow = WS.Cells(Cells.Rows.Count, "B").End(xlUp).Row
targetfile = saveDir & WS.Name & ".txt"
If Dir(targetfile) < "" Then Kill targetfile
Open targetfile For Output As #1
For x = 1 To lastrow
Print #1, WS.Cells(x, 2).Value
Next
Close #1
Next
End Sub


Mike

"TGV" wrote:

In my workbook there are several work sheets are available and I want to save
the each worksheets B COLUMN CELL CONTENTS in a SEPARATE NOTEPAD FILES with
the SHEET NAMES AS FILE NAME.

For example in sheet1 the B COLUM cell contents should be exported as a
notepad file with the sheet name as file name in my desktop and like that it
should export the remaining sheets B column contents in a separate separate
notepad files and it should automatically save the files in my desk top when
I run a macro.

At the same time the note pad file should be generated by way of selecting
the start to end of the cell contents in b column and there will not be any
blank cell in between the B column range. So the macro should export only
the cells which are having the content continuously in B column.

If, anyone knows it, then please provide the VB code for the same and thanks
in advance.

TGV