Prev: SUMPRODUCT(ARRAY) RETURNING "VALUE#"
Next: Select number from leftmost column that has one
From: Babs on 28 Apr 2010 17:58
Hi Ron

I have run the RDB Merge Ad-in, to merge 95 Excel files. There is just 1
problem, it merged the files and data, but the fill colours of the cells are
now "no fill" and that is the most important part of my merge! How do I do
the merge and make sure that the cell fill colours are also duplicated?

Thanks
Babs

"Ron de Bruin" wrote:

> Look at this page for another way (see also the add-in)
> http://www.rondebruin.nl/copy3.htm
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Katie" <Katie(a)discussions.microsoft.com> wrote in message news:53ABF1F6-96D5-40CC-A3AB-D35C913B2110(a)microsoft.com...
> > Hello-
> >
> > I have used this code (trying to do my homework first!) and changed the
> > path- it looks as though when it runs it opens all of the files and closes
> > them but does not copy any data into the book1 file that I have opened - what
> > am I doing wrong here?
> >
> > "Bernie Deitrick" wrote:
> >
> >> Landa,
> >>
> >> Assumptions are: data starts in cell A1, the table is contiguous, is on the first sheet of the
> >> workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows
> >> of data.
> >>
> >> Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run
> >> it. When it is done, save the workbook.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> Sub Consolidate()
> >> Dim myBook As Workbook
> >> Dim myCalc As XlCalculation
> >> Dim myShtName As String
> >>
> >> With Application
> >> .EnableEvents = False
> >> .DisplayAlerts = False
> >> myCalc = .Calculation
> >> .Calculation = xlCalculationManual
> >> End With
> >>
> >> On Error Resume Next
> >> With Application.FileSearch
> >> .NewSearch
> >> 'Change this to your directory
> >> .LookIn = "C:\Excel\Files to combine"
> >> .SearchSubFolders = False
> >> .FileType = msoFileTypeExcelWorkbooks
> >> If .Execute() > 0 Then
> >> For i = 1 To .FoundFiles.Count
> >> Set myBook = Workbooks.Open(.FoundFiles(i))
> >> myBook.Worksheets(1).Range("A1").CurrentRegion.Copy _
> >> ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2)
> >> myBook.Close False
> >> Next i
> >> Else: MsgBox "There were no files found."
> >> End If
> >> End With
> >> With Application
> >> .EnableEvents = True
> >> .DisplayAlerts = True
> >> .Calculation = myCalc
> >> End With
> >>
> >> End Sub
> >>
> >>
> >> "Landa" <Landa(a)discussions.microsoft.com> wrote in message
> >> news:EC614FC0-CB73-4416-99FC-2FF7414F0BDD(a)microsoft.com...
> >> > Let say there is 3 excel files. Each file has one sheet and the same columns,
> >> > e.g. column A: product name
> >> > column B: description
> >> > Column C: Price
> >> >
> >> > How can I combine all the 3 sheets in different files into one sheet of a
> >> > new file?
> >> > I don't want to copy and paste, because in reality, I have more than 100
> >> > files like this.
> >> >
> >> > Thank you!
> >>
> >>
> >>
>
From: Faisal Ijaz on 28 May 2010 01:19
Dear Bernie,
Great work.
Please help me that how to save this macro, Do I have to paste again and
again..
--
With gratitude,

Faisal Ijaz


"Bernie Deitrick" wrote:

> Landa,
>
> Assumptions are: data starts in cell A1, the table is contiguous, is on the first sheet of the
> workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows
> of data.
>
> Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run
> it. When it is done, save the workbook.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> Sub Consolidate()
> Dim myBook As Workbook
> Dim myCalc As XlCalculation
> Dim myShtName As String
>
> With Application
> .EnableEvents = False
> .DisplayAlerts = False
> myCalc = .Calculation
> .Calculation = xlCalculationManual
> End With
>
> On Error Resume Next
> With Application.FileSearch
> .NewSearch
> 'Change this to your directory
> .LookIn = "C:\Excel\Files to combine"
> .SearchSubFolders = False
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute() > 0 Then
> For i = 1 To .FoundFiles.Count
> Set myBook = Workbooks.Open(.FoundFiles(i))
> myBook.Worksheets(1).Range("A1").CurrentRegion.Copy _
> ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2)
> myBook.Close False
> Next i
> Else: MsgBox "There were no files found."
> End If
> End With
> With Application
> .EnableEvents = True
> .DisplayAlerts = True
> .Calculation = myCalc
> End With
>
> End Sub
>
>
> "Landa" <Landa(a)discussions.microsoft.com> wrote in message
> news:EC614FC0-CB73-4416-99FC-2FF7414F0BDD(a)microsoft.com...
> > Let say there is 3 excel files. Each file has one sheet and the same columns,
> > e.g. column A: product name
> > column B: description
> > Column C: Price
> >
> > How can I combine all the 3 sheets in different files into one sheet of a
> > new file?
> > I don't want to copy and paste, because in reality, I have more than 100
> > files like this.
> >
> > Thank you!
>
>
>
 | 
Pages: 1
Prev: SUMPRODUCT(ARRAY) RETURNING "VALUE#"
Next: Select number from leftmost column that has one