Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
So I'm comfortble with Excel in general but never really used VBA other than small tweeks to existing macros.
But I'm stumped with this one. What I have is a Master file which needs to copy data from some 55 individual files. The below code works at coying over the data I want BUT in my Master file I have formula's and it does seem to copy over the top of them.
So what I need is something that looks at a list of file names and then copies say rows 1,7,25:26 & 60 for example, from those files and puts them in the corrisponding tab name in a particular row, so that the formula's inbetween remain intact.
Think I'll get myself a VBA for dummies book to understand the code a bit further also...
Sub Consol()
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
Sheets("Last").Select
Range("A1").Select
Do
If ActiveCell = "" Then
Sheets(1).Select
Calculate
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End
Else
Sheet = ActiveCell.Value
RNAME = ActiveCell.Offset(1, 0)
Worksheets(Sheet).Range("J15").Consolidate _
sources:=RNAME, _
Function:=xlSum
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
So what I need is something that looks at a list of file names and then copies say rows 1,7,25:26 & 60 for example, from those files and puts them in the corrisponding tab name in a particular row, so that the formula's inbetween remain intact.
Can't say I really understand what you're trying to do eg
When you say filenames, I assume you just mean text strings in cells in a worksheet?
When you say 'files' do you mean worksheets or actual files on the HD?
Actual files on the HD. We get sent a file from other offices (55 in total) and I'm trying to collate them into one document.
As its amonthly process I didn't really want to link them all together as some months we'd only get say 40 returns so can only update the 40 leaving the 15 as per last month. With linking this would get messy pretty quickly.
What is the file format? Eg Excel workbook, txt, csv....
we do need a bit more info. but afaik consolidate_sources will just overwrite. I would be tempted to add a bit of code to the end to insert back the formulae that you want - seems easier assuming that they aren't too complicated
Yes all files are .xls
leffeboy, I had the same thought this morning, going to give that a go.