Hello, I've searched high and low and can't find the exact code I'm
looking for... and unfortunately, I'm unable to figure how to string
together bits of code to achieve the desired result.

What I'd like to be able to do is have VBA copy and paste a specific
range from all worksheets in all workbooks in a folder to be selected
by the user.

Anybody got any ideas?

Any help at all would be greatly appreciated.

Hi ?

See
http://www.rondebruin.nl/copy3tip.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hello, I've searched high and low and can't find the exact code I'm
> looking for... and unfortunately, I'm unable to figure how to string
> together bits of code to achieve the desired result.
>
> What I'd like to be able to do is have VBA copy and paste a specific
> range from all worksheets in all workbooks in a folder to be selected
> by the user.
>
> Anybody got any ideas?
>
> Any help at all would be greatly appreciated.
>

I've found this to be very useful

http://www.rondebruin.nl/copy3.htm


"(E-Mail Removed)" wrote:

> Hello, I've searched high and low and can't find the exact code I'm
> looking for... and unfortunately, I'm unable to figure how to string
> together bits of code to achieve the desired result.
>
> What I'd like to be able to do is have VBA copy and paste a specific
> range from all worksheets in all workbooks in a folder to be selected
> by the user.
>
> Anybody got any ideas?
>
> Any help at all would be greatly appreciated.
>
>

Hello,

I tried to post a response thanking both of you for your help and
adding detail to the description of what I'm trying to accomplish. But
I don't see it on here so I'll summarize just in case:

Basically, the only thing I need to do now --that is not already
achieved by the code Ron so graciously posted-- is to have the range
from each worksheet paste into a new worksheet in the master workbook
by a name derived from the original sheet (i.e. An original sheet name
is "Task # 78-000102-CC" and I'd like the name to be "78-000102-CC",
etc).

Thanks again for your input Ron and Barb.

I really appreciate it!

Amy

Hi amy

Which code example do you use now ?
Is only tje naming of the sheet your problem now ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hello,
>
> I tried to post a response thanking both of you for your help and
> adding detail to the description of what I'm trying to accomplish. But
> I don't see it on here so I'll summarize just in case:
>
> Basically, the only thing I need to do now --that is not already
> achieved by the code Ron so graciously posted-- is to have the range
> from each worksheet paste into a new worksheet in the master workbook
> by a name derived from the original sheet (i.e. An original sheet name
> is "Task # 78-000102-CC" and I'd like the name to be "78-000102-CC",
> etc).
>
> Thanks again for your input Ron and Barb.
>
> I really appreciate it!
>
> Amy
>

Hey Ron,

I'm using the code you recommended above: http://www.rondebruin.nl/copy3tip.htm
....and the problem remaining to be solved is two fold.

I need to have:

1) the range from each worksheet pasted to a new worksheet in the
master workbook

and

2) the new worksheet to be named something similar to the original
(i.e. from "Task # 78-000102-CA" to "78-000102-CA").

Thanks,
Amy

Are you sure that there are no duplicate sheet names then in all the workbooks ??

Bedtime for me now but I will make a example for you tomorrow after work

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hey Ron,
>
> I'm using the code you recommended above: http://www.rondebruin.nl/copy3tip.htm
> ...and the problem remaining to be solved is two fold.
>
> I need to have:
>
> 1) the range from each worksheet pasted to a new worksheet in the
> master workbook
>
> and
>
> 2) the new worksheet to be named something similar to the original
> (i.e. from "Task # 78-000102-CA" to "78-000102-CA").
>
> Thanks,
> Amy
>

Ron,

Actually, there is a duplicate sheet name --the first sheet of every
worksheet is called "Summary". It would be nice to call this sheet by
the task number. For example, the other sheets, the sub-element
sheets, are named like this: 78-000102-CA, 78-000102-CB, 78-000102-01,
78-000102-02, and so on. It would be ideal if the "Summary" sheet
could be named without the suffix (simply, 78-000102 in this example).

Earlier on, I made up some variables and a formula to pull the name
off the top of each sheet where it appears in the original. But, I
never could quite get that to work ...and I'm not sure if that's the
best way to go.

Hope you slept well. Thanks again for your help.

Amy

Ok, try this one first
Change
MyPath = "C:\Users\Ron\test"

> It would be ideal if the "Summary" sheet
> could be named without the suffix (simply, 78-000102 in this example).

It will add a number now to the summary sheet
I think about a good sulution for what you want, is the summary sheet always the first sheet in the workbook ?

Sub Example2_More_sheets()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim sh As Worksheet
Dim NewSh As Worksheet
Dim str As String

'Fill in the path\folder where the files are
MyPath = "C:\Users\Ron\test"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
For Each sh In mybook.Worksheets
Set sourceRange = sh.Range("A1:J10")
Set NewSh = basebook.Worksheets.Add
If LCase(sh.Name) = "summary" Then
str = sh.Name & " " & Fnum
Else
On Error Resume Next
str = Right(sh.Name, Len(sh.Name) - 7)
On Error GoTo 0
End If
On Error Resume Next
NewSh.Name = str
If Err.Number > 0 Then
MsgBox "Change the name of : " & NewSh.Name & " manually"
Err.Clear
End If
On Error GoTo 0

Set destrange = NewSh.Cells(1, "A")

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only the values
' With sourceRange
' Set destrange = NewSh.Cells(1, "A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

Next sh

mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Ron,
>
> Actually, there is a duplicate sheet name --the first sheet of every
> worksheet is called "Summary". It would be nice to call this sheet by
> the task number. For example, the other sheets, the sub-element
> sheets, are named like this: 78-000102-CA, 78-000102-CB, 78-000102-01,
> 78-000102-02, and so on. It would be ideal if the "Summary" sheet
> could be named without the suffix (simply, 78-000102 in this example).
>
> Earlier on, I made up some variables and a formula to pull the name
> off the top of each sheet where it appears in the original. But, I
> never could quite get that to work ...and I'm not sure if that's the
> best way to go.
>
> Hope you slept well. Thanks again for your help.
>
> Amy
>

Thank you sooo-- much!! Your code works great!!! My quality of life is
dramatically improved.

In answer to your question, yes, the summary sheet is always the first
sheet in the workbook.

Thanks again! I really appreciate it.

:-)

Amy