I'm a novice.

I would like to format certain columns a without identifying them as "A1-A21" and so on but rather "1-7" within a given range.

I am copying ranges from different files into the main worksheet in a different workbook. After that happens I want the data to be formatted a certain way. I am looking for a way to standardize the formatting so I don't have to change the column references for each range.
So, basically what I am saying is I need a piece of code that will say columns 1-2 within the already specified range should be formatted as %, 3-7 as coma.

Thanks a lot.

Here's a bit of code that might help

This should apply the number formats which you require.
this works by looking at the selection and picking a range within that
Selection i.e. the .Range("A1:B1") looks at the two upperleft cells of the
selection

Hope this Helps
David

Sorry, I don't think I was clear.
That piece of code will work but only for my first cut and paste. That's because my the second file that i'll be copying will be pasted on the same worksheet to the right of the first cut and paste. So if I use your code, I'd have to change the column references for my second section and all the rest.
This becomes a big problem because I'll have over 50 files that will be combined into one with multiple sections fitting into each worksheet.
I hope this makes sense.

Thanks a lot for you help.

Your Reply is Not Quite right

after you cut and paste data in the region you paste into is now selected
as long as you are on the same worksheet.

give my code a try on any section of length 7 and you will see that it selects a range according to the range already selected

for example if your selection was columns D through J

the code i gave you will format columns D and E to Percentage
and C through G as thousand seperator

so when you cut and paste the new data into your workbook.
run the code i gave you and it will change the selected columns

David

I'll be working within different worksheets.
I tried your sample but it didn't work.
Here's my whole code- maybe that will help.

Sub UBD_SubProcess(fname As String, shtMain As Worksheet, rngMain As Range)

Dim wkbk As Workbook
Dim sht As Worksheet
Dim rng As Range

Const path = "C:\Documents and Settings\"

Set wkbk = Application.Workbooks.Open(path & fname)
Set sht = wkbk.ActiveSheet
Set rng = sht.Range("E12:P72")

sht.Activate
rng.Select
Selection.Copy

shtMain.Activate
rngMain.Select
ActiveSheet.Paste

With Selection
.Range("A1:B1").EntireColumn.NumberFormat = "0.00%"
.Range("C1:G1").EntireColumn.Formula = "#,##0"
End With


Application.CutCopyMode = False
Set rngMain = rngMain.Offset(0, 15)
wkbk.Close SaveChanges:=False

End Sub

Sub UpdateBudgetsData()

Dim fname As String
Dim wkbkMain As Workbook
Dim shtMain As Worksheet
Dim rngMain As Range

Set wkbkMain = ActiveWorkbook
Set shtMain = wkbkMain.Worksheets("Rev")
Set rngMain = shtMain.Range("E12")


'1ST FILE
fname = "1REV.xls"
Call UBD_SubProcess(fname, shtMain, rngMain)

'2ND FILE
fname = "2REV.xls"
Call UBD_SubProcess(fname, shtMain, rngMain)

'3RD FILE
' fname = "3REV.xls"
' Call UBD_SubProcess(fname, shtMain, rngMain)


'************************************************
'Now move onto another sheet within the main file
Set shtMain = wkbkMain.Worksheets("REV2")
Set rngMain = shtMain.Range("E12")

'4TH FILE
fname = "4REV.xls"
Call UBD_SubProcess(fname, shtMain, rngMain)

End Sub

Again thank for taking your time.

you were almost there i had to change just a couple of things.
Your Macro should now run smoothly

the only changes i made were to stop sheets activating when required
and to change my selection to your range, and a formula to a numberformat. (I put formula in for my own testing and forgot to change it back again)

I hope this helps

David

I am sorry to keep bugging you but.... the formatting piece doesn't work.
I made the recommended changes and it cuts and pastes fine but the formatting "doesn't take" meaning I don't get an error but it doesn't format the desired columns either.
Thanks again.

where does the problem lie

ive just tested the code above and it seems to format the data fine
it copies the data accross to the worksheet 'REV'
and then it formats the data such that the first 2 columns are percentages with 2 d.p.s and the next 5 of the copied range are thousand seperated.

the rest of your 12 columns are not formatted.
could this be where the problem lies.

David

can i ask you to do me a favour im still trying to figure out whats going wrong

can you update the bit of code that i provided to say

and see which columns are colored red

this should see if the formatting is taking hold or if the type of formatting isnt right

David

Sorry it took me so long to reply.
I looked at it again this morning and realized that I was making a mistake: I never renamed selection as rngMain.
So Now everything works as you said it should.
Thanks you so much.