Copying from multiple sheets to 1 sheet (cell range only)

[thorwood]

Hi there

I'm a relative newcomer to VBA Excel. I wondered if anyone knew how to do the following:

I have several sheets, numbered 1.xls and 2.xls and 3.xls etc which each contain the same field data, data contained in cells B10:O29. Basically, cells B10:O10 contains the data of 1 record, B11:O11 another, etc.

I need to write a VBA script which transfers the data on cell range B10:O29 on 1.xls to a new workbook (let's call it collated.xls) and then transfers the data on cells B10:O29 on 2.xls to follow on, on the same workbook, cells B10:O29 on 3.xls to follow on, etc etc.

As an added bonus, if I could omit copying across any blank lines (as in whole lines from column B to O) then that would be great, although not essential.

Any help would be appreciated.



Thanks
Tim

 

[Andrzejek]

Looks to me like you have separate Excel Files named 1.xls and 2.xls and 3.xls etc, not sheets. Unless I misunderstood you and you have separate (work)sheets in the same Excel file named 1.xls and 2.xls and 3.xls etc (if so, why would you name worksheets with the extension .xls?)

How you have it organized now makes a difference to the solution of your request.


Have fun.

---- Andy

 

[thorwood]

Hi Andy

Thanks for the reply. Yes, sorry, they are separate EXCEL FILES! Each one has just one worksheet in them. I need to put them all into a single worksheet in a new .xls file which I'll call "Collated.xls". My biggest issue is that I ONLY want to copy a particular range of cells (B10:O29). It'll always be the same range of cells for each file.

All files will be under the same drive, which we'll call the C drive.

Thanks
Tim

 

[Andrzejek]

Simple macro recorder gave me this code to Copy range from one Excel file on C:\ named 1.xls to your worksheet:

    Workbooks.Open Filename:="C:\1.xls"
    Range("B10:O29").Copy
    Windows("Book1").Activate
    ActiveSheet.Paste
    Windows("1.xls").Activate
    ActiveWindow.Close

You just need to loop thru your files and paste the info at the buttom(?) of the previous data.

Have fun.

---- Andy

 

[thorwood]

Thanks Andy. I have a number of files to loop through....1.xls, 2.xls, 3.xls etc up to any number. Can you tell me how I can incorporate a loop into the above code? Never done it before!
Thankyou very much
Tim

 

[xlbo]

Sub FileLooper()
[COLOR=green]'Need to set a reference to Microsoft Scripting Runtime (Tools>References)[/color]
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Dim wb as workbook
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder("C:\Home")
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
       set wb = workbooks.open(FileItem.Name)
           [COLOR=green]'copy code goes here - use r to increment rows and THISWORKBOOK to reference the workbook that the code resides in[/color]
       wb.close (false)
       set wb = nothing
    Next
    set SourceFolder = nothing
    set FSO = nothing
end sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Why not simply use the Dir function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thorwood]

Sorry, whats the DIR function? As I mentioned, I'm a novice! Can you show me the code? Many thanks, Tim

 

[xlbo]

You may be a novice but that doesn't mean you can't search google or excel vba help for details....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question