Gathering data from multiple, non-contiguous ranges

[Arlen]

Hello, Everyone!

I know this question has been asked many times and dealt with in many ways,
but I don't know how to modify it for my personal dilemma. I found this code
in another forum for copying a single range from all sheets of all books in a
folder to a single new master sheet.

Sub Open_All_Files2()
Dim oWbk As Workbook
Dim w As Worksheet
Dim sFil As String
Dim sPath As String
Dim k As Long, n As Long
sPath = "C:\Documents and Settings\gl1b\Desktop\Tacoma 2007\"
ChDir sPath
sFil = Dir("*.xls") 'change or add formats
Application.DisplayAlerts = False
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Do While sFil <> ""
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
Range("A13:J27").Copy
Set w = ThisWorkbook.Sheets(1)
ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial
oWbk.Close True
sFil = Dir
Loop
Application.DisplayAlerts = True
End Sub

I ran it and it seems to open, copy and close everything in the folder I
specified. I just don't see the pasted data anywhere.

My specifics are these:

I need to copy everything from B4 to B?? <-- wherever the data in col B runs
out, C4 to C?? and N4 to N??, paste it continuously down Cols A, B and C of a
new master sheet.

I have puzzled over this code for 2 days, changing things and changing them
back when it didn't work. If anyone could make the necessary tweaks, I would
appreciate it tremendously.

Thanks for your time.

Arlen

 

[JLGWhiz]

At a quick glance, I would think you need to add something to this line:

ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial

Like:

ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial Paste:=xlPasteValues

 

[JLGWhiz]

If you didn't have the alerts turned off, you would probably be getting an
error message there.

 

[Arlen]

JLGWhiz,

Thanks for responding. I figured out where my pasted data was going...so
that's a big relief. Now, my only issue is matching up the data copied from
Cols B, C and N to the ranges where it will be pasted.

In the example, it was pasting a single block to a single block.

Mine has two blocks: B4:C1000 and N4:N1000

The first block, Range("B4:C1000").Copy

successfully gets pasted into the master sheet Cols A and B here:

ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial

But the second, N4:N1000 causes problems. Do you know how to sync up the
..Copy and .PasteSpecial lines?

I sure appreciate it.

Arlen

If you didn't have the alerts turned off, you would probably be getting an
error message there.

Hello, Everyone!

I know this question has been asked many times and dealt with in many ways,
but I don't know how to modify it for my personal dilemma. I found this code
in another forum for copying a single range from all sheets of all books in a
folder to a single new master sheet.

Sub Open_All_Files2()
Dim oWbk As Workbook
Dim w As Worksheet
Dim sFil As String
Dim sPath As String
Dim k As Long, n As Long
sPath = "C:\Documents and Settings\gl1b\Desktop\Tacoma 2007\"
ChDir sPath
sFil = Dir("*.xls") 'change or add formats
Application.DisplayAlerts = False
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Do While sFil <> ""
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
n = k + 1
Set oWbk = Workbooks.Open(sPath & "\" & sFil)
Range("A13:J27").Copy
Set w = ThisWorkbook.Sheets(1)
ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial
oWbk.Close True
sFil = Dir
Loop
Application.DisplayAlerts = True
End Sub

I ran it and it seems to open, copy and close everything in the folder I
specified. I just don't see the pasted data anywhere.

My specifics are these:

I need to copy everything from B4 to B?? <-- wherever the data in col B runs
out, C4 to C?? and N4 to N??, paste it continuously down Cols A, B and C of a
new master sheet.

I have puzzled over this code for 2 days, changing things and changing them
back when it didn't work. If anyone could make the necessary tweaks, I would
appreciate it tremendously.

Thanks for your time.

Arlen