Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering data from multiple, non-contiguous ranges
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. Code:
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering data from multiple, non-contiguous ranges
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 "Arlen" wrote: 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. Code:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering data from multiple, non-contiguous ranges
If you didn't have the alerts turned off, you would probably be getting an
error message there. "Arlen" wrote: 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. Code:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering data from multiple, non-contiguous ranges
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 he 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 "JLGWhiz" wrote: If you didn't have the alerts turned off, you would probably be getting an error message there. "Arlen" wrote: 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. Code:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Gathering rows of data from multiple wrkbks to single column | Excel Worksheet Functions | |||
Gathering data from multiple worksheets | Excel Discussion (Misc queries) | |||
Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column | Excel Programming | |||
Multiple Selection of Non-Contiguous Data | Excel Programming | |||
Combine non-contiguous data from multiple worksheets | Excel Programming |