Copy and Paste - variable window name - large data quantities

Hello

The core of the probelm is that I am trying to copy the contents about 65 files from lines 8 and below onto a new summary file. The child file name (from which I copy) will change but will always start with the same word. The size of the child files will vary greatly in length, some will be over 4000 rows long. The part down below is part of a loop that actually works, when the file is "only" about 400 rows long.

I tried using the Windows....Activate function (using the child window name as a variable) to be able to switch back and forth between windows but this didn't work. I also hear that this function represents a big "NoNo" amongst programmers... Any otehr suggestions?


Set WorkBk = Workbooks.Open(FolderPath & FileName)

If Range("A9") <> 0 And Range("A8") <> 0 Then
Rows("8:8").Select
Set SourceRange = WorkBk.Worksheets(1).Range(Selection, Selection.End(xlDown))
Set DestRange = SummarySheet.Range("A" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)

' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value

' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
End If

Perhaps something like this could be of help?

You need to change the "sPath" to suit your needs. The command "Copy.Offset(8)" will copy all data in source file from row 8 and dow to the last row of data. The file you add this code to can be activated by either using the file name or activating "ThisWorkbook" as the VBA code knows this to be the "target" book. Macro also assumes that when opening a source file the active worksheet is the one you wish to copy from.

Please Login or Register  to view this content.

Alf

Just to be sure there is no missunderstanding. If you have headings or comments starting at row 1 and the "real" data you wish to copy starts at row 8 then the proper command is

Please Login or Register  to view this content.

If on the other hand the first 7 rows are empty and data starts at row 8 you should use

Please Login or Register  to view this content.

Alf

Hi Alf

I was able to intergrate your input into the existing macro and it works just fine. Thank you very much! Just one thing: the command

ActiveSheet.UsedRange.Offset(8).Copy

lets the macro copy the rows 9 and below, so I had to change this to (7). I guess the "offset" means "jump by 8 rows" instead of "Move to the eigth row".

Thanks again!

Best regards,

FarOut

"offset" means "jump by 8 rows" instead of "Move to the eigth row".

So right you are, the "Offset" command is quite usefull in a number of cases. There are normaly two parameters, row offset and column offset so for instance

Please Login or Register  to view this content.

will move the pointer up from the bottom of column A untill it finds the first cell with a value in column A, then it jumps down one row and paste the value there (i.e. the first empty cell found in the A column)

In this case the column offset is 0. The Offset command is often rellative to "ActiveCell" or a range or something dimed as having range properties. Only when using the "Cells" expression this is an absolute.


Alf

Ps If this solves you probem don't forget to mark thread "Solved"

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Hi Alf

Alrighty, thanks again. Haven't been on the site for a few years - where do I mark the thread as "solved"?

FarOut

Ok I'll post it again

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Alf