Replies

Hi all,

I have a CSV file that i import the data onto a payroll system. Before I import the data on one of the columns I put three zeros in front of numbers and I use the following formula =”000″ & B on a different cell i.e. f2, I then copy paste values on cell b2. this displays the zeros before the numbers on Column./ cell B2. I than save the file it gives me a few warning and click yes to those, etc. and usually when I open the file the zeros are there, but this time they are all gone.

Does anyone know what the problem is?

Cheers

I believe that Excel have converted text to number automatically.
Why don’t you try custom formatting your cell, it is much more robust

If it has a subtotal formula (i. e., “=subtotal(x,y)”)

I am assuming that you do not want the subtotal rows
simply click on Data on the menu bar
select Subtotals
click on Remove All

This will remove all subtotals and grand total.

Hi

Does the attached do what you want?

Format the cell M14 by right click
choose Format Cells >>select Custom and choose [h]:mm:ss

HTH

Thanks Prasad and Hans

However it still does the same as you can see from the screenshot below with dummy data, this is with both the & and the Concatenate formula?

Editted: helps if I attach the picture!!

I see no problem with the formula you posted.
Care to share a sample of your copy? It would be some other issue than the formula itself.

Thanks.

Is there a different between running the macro from a commandbutton
and from the workbook’s macro

The error happen at Cells.Select when I run it from a commandbutton
but its work when using the workbook’s macro.

Code:

Sub PasteOnlyValuesAndFormat()
	'
	 Cells.Select
	 Selection.Copy
	 Workbooks.Open Filename:="filespec"
	 Sheets("Sheet1").Select
	 Cells.Select
	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
		  SkipBlanks:=False, Transpose:=False
	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		 SkipBlanks:=False, Transpose:=False
	End Sub

TIA

I’m sorry I can’t seem to get your spreed sheet to work, but thanks for your help anyway.

I tried it and its works.

Did you enable the macro when you first open the file and double click on the cell Color or Number

You could use code like:

Code:

Application.CutCopyMode = False
Range("A1").Select

To exit CutCopyMode, and re-select the first cell in the worksheet.

You could use code like this to save the new workbook with a reference to a name in C5:

Code:

   Dim NewWbk As Workbook
   Dim strFileName As String
   strFileName = Range("C5").Text
   Set NewWbk = Workbooks.Add(Template:="Workbook")
   NewWbk.SaveAs Filename:=strFileName
   Set NewWbk = Nothing

Gary

Thanks.

The variables would be those assigned by you earlier in the project.

If you need to open an existing workbook the code would change to:

Code:

Sub PasteOnlyValuesAndFormat()
	'
	 Cells.Select
	 Selection.Copy
	 Workbooks.Open Filename:="filespec"
	 Sheets("Sheet1").Select
	 Cells.Select
	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
		  SkipBlanks:=False, Transpose:=False
	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		 SkipBlanks:=False, Transpose:=False
	End Sub

If you need to open a New workbook the line
Workbooks.Open Filename:=”filespec”

would be replaced with
Workbooks.Add Template:=”Workbook”

Thanks, this works.
In addition, would I use this line at the end to
end the copy mode in “Template”

Application.CutCopyMode = False

How could I save the new workbook with reference to the name in C5?

Again thanks for your guide.

Assuming that you wish to paste the data into a file whose name is stored in the variable “FN” (without the quotes), the line will change to:

Windows(FN).Activate

The line of code activates the window where you will paste the data. You will also need to ensure that the appropriate sheet is active.

How could the variables be known or come from?

What if we need open a new workbook?
How could we change this to

Does the following help?

Code:

Sub PasteOnlyValuesAndFormat()
   '
	   Cells.Select
	   Selection.Copy
	   Windows("Target").Activate
	   Cells.Select
	   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
			SkipBlanks:=False, Transpose:=False
	   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		   SkipBlanks:=False, Transpose:=False
   End Sub

Thanks for looking into this.

There’s an error at this line
Windows(“Target”).Activate

What does this line suppose to do?

thanks

Pl have a look on attachment. I am struggling with formating cell to % in 2 decimal places along with up/down arrow. Any suggestion.

Seem like it can’t be formatted as percentage due to adding in the symbol as its have become text
How’s about this if you need to include percentage symbol in the result?

I did it by going to Insert | Symbol, and selecting my required symbols out onto the worksheet. Once I had the symbols in the ws, I copied them into my formula’s.

I’m starting to wish that I had not taken this route now though, I’ve created several problems that I now have to find workarounds for.

That’s progress grrrrrrr..

That’s what I am doing and find it really cumbersome
I am yet to try the barron’s method

FWIW, you can simplify a lot and use a custom number format rather than conditional formatting and IF formulas. See attached (cell C1 has the custom format depending on whether it’s positive, negative or 0)

Thanks, but where and how do I get the symbol into the formula?

Thanks Mike!

would you mind posting your file to share this
I am still lost as to how to have the symbol in the formula

thanks

It does – just add more sheet names to the list. If you have many sheets, it is easier to enter the sheet names in a range of cells, say H1:H10 if you have 10 sheets. You can then use H1:H10 instead of {“Sheet1″;”Sheet2″}

(If the sheet names may contain spaces, you must use ”‘”&H1:H10&”‘”)

Hi Hans

Thanks for the tip.