Ok Gang, This is going to be a Hard one for you Smart people.

I need a Super powerful Macro so when i click on the macro it
automatically saves the chosen data as a .CSV file.

I'll Give you E.g.

I enter data (numbers,text) from Cell "A1" across to Cell "G1" and down
to "G12" then back to "A12" making a nice box full of Sexy data. Then
After all that data is entered i Click on my Super Powerful Macro and
it gets all that information and saves it into a .csv file somewhere
esle. So in the end i would have my normal Excel doc and my New .csv
doc.......

If anyone can help me with this you will be the smartest person alive.


--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

How about:

Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet

Set myRng = Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("a1").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With
End Sub


Change the name and folder to what you want.

And select the range to save before you run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

joelbeveridge wrote:

Ok Gang, This is going to be a Hard one for you Smart people.

I need a Super powerful Macro so when i click on the macro it
automatically saves the chosen data as a .CSV file.

I'll Give you E.g.

I enter data (numbers,text) from Cell "A1" across to Cell "G1" and down
to "G12" then back to "A12" making a nice box full of Sexy data. Then
After all that data is entered i Click on my Super Powerful Macro and
it gets all that information and saves it into a .csv file somewhere
esle. So in the end i would have my normal Excel doc and my New .csv
doc.......

If anyone can help me with this you will be the smartest person alive.

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson

OK Dave I used your really cool code to allow me to Save the data i
wanted to a .CSV file somewhere on my PC. I changed it alittle to suit
my needs. I made the macro and when i clicked on the marco it makes the
.CSV file but there is no data in it. Have a look at what i changed.
Thanks mate


Option Explicit
Sub ConvertToCsv()


Dim myRng As Range
Dim wks As Worksheet

Set myRng = Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("n11:w33").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:="C:\testfolder\converted.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With
End Sub

Any Infomation you can help me with???


--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

Check to see what's in your current selection--is there data in that range?

And change this line back:
wks.Range("n11:w33").PasteSpecial Paste:=xlPasteValues
to
wks.Range("a1").PasteSpecial Paste:=xlPasteValues



joelbeveridge wrote:

OK Dave I used your really cool code to allow me to Save the data i
wanted to a .CSV file somewhere on my PC. I changed it alittle to suit
my needs. I made the macro and when i clicked on the marco it makes the
CSV file but there is no data in it. Have a look at what i changed.
Thanks mate

Option Explicit
Sub ConvertToCsv()

Dim myRng As Range
Dim wks As Worksheet

Set myRng = Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("n11:w33").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
SaveAs Filename:="C:\testfolder\converted.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
Close savechanges:=False
End With
End Sub

Any Infomation you can help me with???

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson

just to expand...

wks.range("a1") is the topleft cell of that new worksheet.

All the code is doing is copying your selection and pasting (as values) to that
top left cell (excel will expand the number of columns/rows to match the range
being copied).

Then it's saving that newly created file as a .csv file.

Dave Peterson wrote:

Check to see what's in your current selection--is there data in that range?

And change this line back:
wks.Range("n11:w33").PasteSpecial Paste:=xlPasteValues
to
wks.Range("a1").PasteSpecial Paste:=xlPasteValues

joelbeveridge wrote:

OK Dave I used your really cool code to allow me to Save the data i
wanted to a .CSV file somewhere on my PC. I changed it alittle to suit
my needs. I made the macro and when i clicked on the marco it makes the
CSV file but there is no data in it. Have a look at what i changed.
Thanks mate

Option Explicit
Sub ConvertToCsv()

Dim myRng As Range
Dim wks As Worksheet

Set myRng = Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("n11:w33").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
SaveAs Filename:="C:\testfolder\converted.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
Close savechanges:=False
End With
End Sub

Any Infomation you can help me with???

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson

--

Dave Peterson

Well i changed myRng.Copy
wks.Range("a1").PasteSpecial Paste:=xlPasteValues back to ("a1") as you
told me. The thing im finding is that its only tranfering things to .CSV
if i highlight them then click on the macro. I would like if it
automatically copied the data i only want between "N11" and "W32".

So at the moment it only converts data to .csv format if i go and drag
my mouse over it and then press the macro. Any clue how i can make it
smarter?


--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

I need to marco to copy the set cells in my worksheet, not the total
sheet. So from "N11" down to "W32" i want it so auto select that and
then save it to .csv file.

Also everytime i do this, say for diffenent information, will it auto
save over the old one?? I would like it to ask if it wants to overright
or a option to save to some other name. Just a sweet thing it would do
then. thanks


--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

Always N11:W32?

Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet
Dim myFileName As Variant
Dim resp As Long

myFileName = Application.GetSaveAsFilename(filefilter:="CSV Files, *.csv")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

If Dir(myFileName) = "" Then
'do nothing, no existing file
Else
resp = MsgBox(Prompt:="That file exists, Overwrite?", Buttons:=vbYesNo)
If resp = vbNo Then
MsgBox "CSV file not saved--Try again later!"
Exit Sub
End If
End If

Set myRng = ActiveSheet.Range("N11:W32") 'Selection

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

myRng.Copy
wks.Range("a1").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:=myFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With
End Sub


joelbeveridge wrote:

I need to marco to copy the set cells in my worksheet, not the total
sheet. So from "N11" down to "W32" i want it so auto select that and
then save it to .csv file.

Also everytime i do this, say for diffenent information, will it auto
save over the old one?? I would like it to ask if it wants to overright
or a option to save to some other name. Just a sweet thing it would do
then. thanks

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson

Yea its always going to be N11:W36 that needs to be copied and saved to
.csv format. If it can do that ill be so happy i might cry..lol


--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson

It worked really good i almost cryed. You are a champ.

I would like to know if i can add one more Cell to be saved with the
data aswell? The Cell is like "B3"?? or is it locked?


--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

"Dave Peterson" wrote:

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson

It depends on where you want that B3 pasted to.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myRng2 as Range '<--added this
Dim wks As Worksheet
Dim myFileName As Variant
Dim resp As Long

myFileName = Application.GetSaveAsFilename(filefilter:="CSV Files, *.csv")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

If Dir(myFileName) = "" Then
'do nothing, no existing file
Else
resp = MsgBox(Prompt:="That file exists, Overwrite?", Buttons:=vbYesNo)
If resp = vbNo Then
MsgBox "CSV file not saved--Try again later!"
Exit Sub
End If
End If

Set myRng = ActiveSheet.Range("N11:W32") 'Selection
Set myRng2 = activesheet.range("b3") ' added

'create a new workbook with a single sheet
Set wks = Workbooks.Add(1).Worksheets(1)

'paste B3 to A1 and move the rest down a row???
myrng2.copy
wks.range("a1").PasteSpecial Paste:=xlPasteValues

myRng.Copy
wks.Range("a2").PasteSpecial Paste:=xlPasteValues

With wks.Parent
Application.DisplayAlerts = False
.SaveAs Filename:=myFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
.Close savechanges:=False
End With
End Sub

joelbeveridge wrote:

It worked really good i almost cryed. You are a champ.

I would like to know if i can add one more Cell to be saved with the
data aswell? The Cell is like "B3"?? or is it locked?

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

Gocool wrote:

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

"Dave Peterson" wrote:

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson


--

Dave Peterson

It was perfect Dave. Thanks a ton!!! catch you with someother query soon. ;-)

"Dave Peterson" wrote:

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

Gocool wrote:

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

"Dave Peterson" wrote:

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson


--

Dave Peterson

I have master & child file in which records are available as below..

Master file


Col1 Col2 Col3 Col4
HQ_1 xyz abc
HQ_3 hhh abc
HQ_4 lkd


Child File

Col1 Col2 Col3 Col4 Col5 Col6
HQ_1 121 test xyz abc
HQ_2 2 dev
HQ_3 34 dev hhh abc
HQ_4 24 test lkd

col2 & col3 of Master file has to be added to child as col4 and col5 in
respective rows.

How can i do this?? is there a way to do this using MACRO???

kindly please help me

"Dave Peterson" wrote:

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

Gocool wrote:

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

"Dave Peterson" wrote:

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson


--

Dave Peterson

I'm not sure I understand, but if you can use =vlookup() manually, you could use
a macro that did the same kind of thing.

Gocool wrote:

I have master & child file in which records are available as below..

Master file

Col1 Col2 Col3 Col4
HQ_1 xyz abc
HQ_3 hhh abc
HQ_4 lkd

Child File

Col1 Col2 Col3 Col4 Col5 Col6
HQ_1 121 test xyz abc
HQ_2 2 dev
HQ_3 34 dev hhh abc
HQ_4 24 test lkd

col2 & col3 of Master file has to be added to child as col4 and col5 in
respective rows.

How can i do this?? is there a way to do this using MACRO???

kindly please help me

"Dave Peterson" wrote:

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

Gocool wrote:

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

"Dave Peterson" wrote:

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

thanks for the pointer.. I tried with VLOOKUP() for a range of cells. its
working fine.. for few fields its end up showing #REF in case of
error/incorrect match. I need these cells to be empty cells. HOW do i do this?

"Dave Peterson" wrote:

I'm not sure I understand, but if you can use =vlookup() manually, you could use
a macro that did the same kind of thing.

Gocool wrote:

I have master & child file in which records are available as below..

Master file

Col1 Col2 Col3 Col4
HQ_1 xyz abc
HQ_3 hhh abc
HQ_4 lkd

Child File

Col1 Col2 Col3 Col4 Col5 Col6
HQ_1 121 test xyz abc
HQ_2 2 dev
HQ_3 34 dev hhh abc
HQ_4 24 test lkd

col2 & col3 of Master file has to be added to child as col4 and col5 in
respective rows.

How can i do this?? is there a way to do this using MACRO???

kindly please help me

"Dave Peterson" wrote:

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

Gocool wrote:

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

"Dave Peterson" wrote:

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I'm not sure how you got Ref errors in some of the formulas.

But make sure your look up table is wide enough to retrieve the column you want.

for instance:
=vlookup(a1,sheet2!a:e,6,false)
will return that #Ref! error, since I'm trying to bring back the 6th column of a
5 column range.

If you have trouble fixing your formula, include it in your reply.

And Debra Dalgleish has notes for =vlookup():
http://www.contextures.com/xlFunctions02.html

Gocool wrote:

thanks for the pointer.. I tried with VLOOKUP() for a range of cells. its
working fine.. for few fields its end up showing #REF in case of
error/incorrect match. I need these cells to be empty cells. HOW do i do this?

"Dave Peterson" wrote:

I'm not sure I understand, but if you can use =vlookup() manually, you could use
a macro that did the same kind of thing.

Gocool wrote:

I have master & child file in which records are available as below..

Master file

Col1 Col2 Col3 Col4
HQ_1 xyz abc
HQ_3 hhh abc
HQ_4 lkd

Child File

Col1 Col2 Col3 Col4 Col5 Col6
HQ_1 121 test xyz abc
HQ_2 2 dev
HQ_3 34 dev hhh abc
HQ_4 24 test lkd

col2 & col3 of Master file has to be added to child as col4 and col5 in
respective rows.

How can i do this?? is there a way to do this using MACRO???

kindly please help me

"Dave Peterson" wrote:

If you just want to create the file without asking...

..SaveAs Filename:="C:\temp\mynamehere.csv", FileFormat:=xlCSV
becomes
..SaveAs Filename:="C:\temp\mynamehere" _
"_" & format(date, "yyyy_mm_dd") & ".csv", FileFormat:=xlCSV

This would be used in the first suggested code. Not the one that allows the
user to choose a name.

Gocool wrote:

Hi Dave,

Same query but I have to save with the filename appended with current date.

pls. help

thanks

"Dave Peterson" wrote:

Did it work when you tried it?

joelbeveridge wrote:

Yea its always going to be N11:W36 that needs to be copied and saved to
csv format. If it can do that ill be so happy i might cry..lol

--
joelbeveridge
------------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php...o&userid=37045
View this thread: http://www.excelforum.com/showthread...hreadid=567765

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson