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


wrote in message oups.com...
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


" 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


wrote in message oups.com...
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


wrote in message oups.com...
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


wrote in message oups.com...
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

Try this

If LCase(sh.Name) = "summary" Then
str = Mid(mybook.Sheets(2).Name, 8, 9)

You can also use Mid for the other part instead of Right en Len

--

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


wrote in message ups.com...
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

Ron,

It worked like a charm! Thank you so- much!! That was a huge piece of
the puzzle!!

Thanks to you, I've now started to work on the next piece: automating
an advanced filter to pull data from one of the worksheets in the
master workbook and place it in each worksheet according to the task
name (as it appears in the tab for each worksheet).

So far, I'm not able to get the same results I have achieved for a
single case scenario. I thought it might work to simply change the
variable name to match yours and add in a "For Each" statement:
For Each sh In basebook.Worksheets
Sheets(str).Select
Range("A1").Select
Sheets("invoice_tracking").Range("a1:h500").Advanc edFilter
Action:= _
xlFilterCopy, CriteriaRange:=Sheets(str).Range("A17:A18"), _
CopyToRange:=Range("B20"), Unique:=False
Next sh

but it didn't. I will keep trying and post back if I'm able to figure
it out.

Any ideas would, of course, be greatly appreciated but regardless,
thanks again for solving the original problem I posted.

Best,
Amy

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



--

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


wrote in message oups.com...
Ron,

It worked like a charm! Thank you so- much!! That was a huge piece of
the puzzle!!

Thanks to you, I've now started to work on the next piece: automating
an advanced filter to pull data from one of the worksheets in the
master workbook and place it in each worksheet according to the task
name (as it appears in the tab for each worksheet).

So far, I'm not able to get the same results I have achieved for a
single case scenario. I thought it might work to simply change the
variable name to match yours and add in a "For Each" statement:
For Each sh In basebook.Worksheets
Sheets(str).Select
Range("A1").Select
Sheets("invoice_tracking").Range("a1:h500").Advanc edFilter
Action:= _
xlFilterCopy, CriteriaRange:=Sheets(str).Range("A17:A18"), _
CopyToRange:=Range("B20"), Unique:=False
Next sh

but it didn't. I will keep trying and post back if I'm able to figure
it out.

Any ideas would, of course, be greatly appreciated but regardless,
thanks again for solving the original problem I posted.

Best,
Amy

Hey Ron, My plan for altering your code is not working. I thought I
might be able to change this bit of code:

Set WS1 = Sheets("sheet1") '<<< Change
Set WS2 = Sheets("Netherlands") '<<< Change
'A1 is the top left cell of your filter range and the header of
the first column
Set rng1 = WS1.Range("A1").CurrentRegion '<<< Change
Str = "Netherlands" '<<< Change

To this:
For Each Ws in Worksheets
Set WS1 = Sheets("invoice_tracking")
Set WS2 = Sheets(Str)
St rng1 = WS1.Range("A1").CurrentRegion '<<< Change

(where Str = the worksheet name as defined earlier on in the code and
Ws is defined as "Dim Ws as Worksheet" at the top of the procedure)
....

Next Ws
End Sub

But, it doesn't work at all... and I can't see how else I could change
the code so that it would pull specific data from the
"invoice_tracking" worksheet within the master workbook, where all the
worksheets with specific ranges were just copied (and where the code
resides).

I'll keep poking around but, for the record, just thought I'd let you
know, I'm lost. Any hints would be accepted with much gratitude.

Best,
Amy

Hi Amy

Ok you have a workbook with the "invoice_tracking" sheet and all other worksheets you create with the other macro.
You want to copy data from the "invoice_tracking" sheet to the correct sheet in the workbook

Am I correct ?

Do you have one column in "invoice_tracking" with the names of the sheets ?

--

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


wrote in message oups.com...
Hey Ron, My plan for altering your code is not working. I thought I
might be able to change this bit of code:

Set WS1 = Sheets("sheet1") '<<< Change
Set WS2 = Sheets("Netherlands") '<<< Change
'A1 is the top left cell of your filter range and the header of
the first column
Set rng1 = WS1.Range("A1").CurrentRegion '<<< Change
Str = "Netherlands" '<<< Change

To this:
For Each Ws in Worksheets
Set WS1 = Sheets("invoice_tracking")
Set WS2 = Sheets(Str)
St rng1 = WS1.Range("A1").CurrentRegion '<<< Change

(where Str = the worksheet name as defined earlier on in the code and
Ws is defined as "Dim Ws as Worksheet" at the top of the procedure)
...

Next Ws
End Sub

But, it doesn't work at all... and I can't see how else I could change
the code so that it would pull specific data from the
"invoice_tracking" worksheet within the master workbook, where all the
worksheets with specific ranges were just copied (and where the code
resides).

I'll keep poking around but, for the record, just thought I'd let you
know, I'm lost. Any hints would be accepted with much gratitude.

Best,
Amy

Ron! So good to hear from you.

Yes, I have a workbook with the "invoice_tracking" sheet (all other
worksheets are created with the other macro) and one column (column G)
in "invoice_tracking" has the names of the sheets.


Amy

Hi Amy

Then we can use this macro
http://www.rondebruin.nl/copy5.htm#existing


Change the sheet name to invoice_tracking
Set ws1 = Sheets("Sheet1") '<<< Change

And
rng.Columns(1).AdvancedFilter _
To
rng.Columns(7).AdvancedFilter _

Try this first and if you have problems post back
Do not forget to copy the two functions in the module

--

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


wrote in message oups.com...
Ron! So good to hear from you.

Yes, I have a workbook with the "invoice_tracking" sheet (all other
worksheets are created with the other macro) and one column (column G)
in "invoice_tracking" has the names of the sheets.


Amy

Thank you so much Ron.

I was able to copy the macro, make the changes you suggested and run
the macro. There are a couple of problems, the first of which I think
I solved by changing this:

If SheetExists(cell.Value) = False Then
Set ws2 = Sheets.Add
On Error Resume Next
ws2.Name = cell.Value
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=ws2.Range("A1"), _
Unique:=False
ws2.Columns.AutoFit
Else

to this:
If SheetExists(cell.Value) = False Then
Else

That change caused the macro to no longer create unnecessary
worksheets in my workbook (ie any worksheets not already copied from
my hard drive in the previous macro). Because column G has all task
numbers in my company, not just the ones I need to know about, there
are a ton of tasks in that column I don't need to see.

But, the second problem remains: it still doesn't place the pertinent
filtered data below the existing text in the existing worksheets.

Any ideas?

Thanks,
Amy

Hi Amy

No need to change the code

Send me your workbook private and I take a look at it tomorrow after work


--

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


wrote in message oups.com...
Thank you so much Ron.

I was able to copy the macro, make the changes you suggested and run
the macro. There are a couple of problems, the first of which I think
I solved by changing this:

If SheetExists(cell.Value) = False Then
Set ws2 = Sheets.Add
On Error Resume Next
ws2.Name = cell.Value
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=ws2.Range("A1"), _
Unique:=False
ws2.Columns.AutoFit
Else

to this:
If SheetExists(cell.Value) = False Then
Else

That change caused the macro to no longer create unnecessary
worksheets in my workbook (ie any worksheets not already copied from
my hard drive in the previous macro). Because column G has all task
numbers in my company, not just the ones I need to know about, there
are a ton of tasks in that column I don't need to see.

But, the second problem remains: it still doesn't place the pertinent
filtered data below the existing text in the existing worksheets.

Any ideas?

Thanks,
Amy

Ron, Just want to make sure you got my message last week including the
code from my workbook. If you did get my message and there is a better
way for you to receive the code, please let me know. I'm new to the
usenet group format so I apologize if either you didn't receive the
message or if it came but not in the form you'd expected.

Best,
Amy

Hi Amy

I not see it

Mail me at the address on my site
http://www.rondebruin.nl/


--

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


wrote in message oups.com...
Ron, Just want to make sure you got my message last week including the
code from my workbook. If you did get my message and there is a better
way for you to receive the code, please let me know. I'm new to the
usenet group format so I apologize if either you didn't receive the
message or if it came but not in the form you'd expected.

Best,
Amy

Oh, that's too bad. Sorry for any confusion. Ok, I will send you a
message using the address from your page.

Thanks!!
Amy

Hi Ron,

I just want to make sure you got my email yesterday including my
workbook. I sent it to from
. Please let me know if you need me to send it
again.

Thanks,
Amy

I send a reply


--

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


wrote in message ups.com...
Hi Ron,

I just want to make sure you got my email yesterday including my
workbook. I sent it to from
. Please let me know if you need me to send it
again.

Thanks,
Amy

Ron,

I just realized I made a navigation error in flipping through the
usenet pages: I posted to you about a response you made to Lisa before
I saw your post above "I send a reply". Sorry for any confusion. It's
late in the day (and taxes are due today) over here and I guess I'm
just tired.

I'm assuming that by the above you mean you'll send a reply to my
email account. I will look for it there.

Thanks again,
Amy

Yep, I send another one after reading your mail

--

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


wrote in message oups.com...
Ron,

I just realized I made a navigation error in flipping through the
usenet pages: I posted to you about a response you made to Lisa before
I saw your post above "I send a reply". Sorry for any confusion. It's
late in the day (and taxes are due today) over here and I guess I'm
just tired.

I'm assuming that by the above you mean you'll send a reply to my
email account. I will look for it there.

Thanks again,
Amy

Ok, Thank you.

So sorry about that.

If you have sent me any messages to , I may need to
change my email preferences or something and ask you to send them
again. I haven't received an email from you at that address so far.

Best,
Amy

Ron,

I figured out what the problem is. As you say in your comment "the
last two columns of the worksheet are used to make a Unique list and
add the CriteriaRange (you can't use this macro if you use the
columns)."

I use those columns. They get filled in with the data retrieved from
other worksheets in the first macro.

I figured out a work-around but it is not very efficient. I rearranged
the order of the "invoice_tracking" worksheet columns, then moved that
data around so that it ends on the same column as the first macro data
and finally, I filled in the blank columns with non-sense text so it
would recognize that there's data there.

Do you know of a better way?

Amy

Ron,

I just wanted to say thanks for helping me get started in the right
direction --since I haven't heard from you in a while I guess it's
safe to assume you've moved on.

I appreciated you taking the time out of your day to point me to that
helpful bit of code of yours.

Best,
Amy

hi Amy

You can copy the criteria on another worksheet and use that range in the filter

--

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


wrote in message oups.com...
Ron,

I just wanted to say thanks for helping me get started in the right
direction --since I haven't heard from you in a while I guess it's
safe to assume you've moved on.

I appreciated you taking the time out of your day to point me to that
helpful bit of code of yours.

Best,
Amy