Open File then Set as
 

Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
Set xlBook = Workbook.Open(sFile)
End If


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MacroLearning" wrote in message
...
Hi all,
I have a macro that works as I want. When I first started building I
included the original filename to copy from. That file will change every
month. So I want the user to open the file, when the file is opened, it
will
become "xlBook1". following is the code. I'm getting a debug at that
point,
but am not sure how to get past it.

Any help will be very much appreciated!

'Book 1 is Headcount by Detail Report
Dim xlBook1 As Excel.Workbook
'Book 2 is Quirk (all except SSGA)
Dim xlBook2 As Excel.Workbook
'Sheet7 is HC Summary worksheet from HC Detail Report
Dim xlSheet7 As Excel.Worksheet
'Sheet8 is HC Summary worksheet for Quirk
Dim xlSheet8 As Excel.Worksheet

'Copy and Paste from Headcount Detail to Quirk
Set xlBook1 = Application.GetOpenFilename <=====This is the problem here
Set xlSheet1 = xlBook1.Worksheets("Headcount Detail")
Set xlBook2 = Workbooks.Open( _
"C:\\Distribution File Cuts\Headcount by Title - Quirk.xls")
Set xlSheet2 = xlBook2.Worksheets("Headcount Detail")
Set xlSheet7 = xlBook1.Worksheets("Headcount Summary")
Set xlSheet8 = xlBook2.Worksheets("Headcount Summary")
xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1")
xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1")
xlSheet2.Rows("40:47").Delete

Open File then Set as
 

Do you have ann. application object variable?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MacroLearning" wrote in message
...
Thank you for posting an answer to my question.

I am still getting a "debug" when I try to execute the macro. It won't
set
the opened file as xlbook. I get a 424 error code. I changed it to
include
your suggestion.

Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
Set xlBook = Workbook.Open(sFile) <<<========== this is where it hangs up
at
Set xlSheet1 = xlBook.Worksheets("Headcount Detail")
Set xlBook2 = Workbooks.Open( _
"W:\\Distribution File Cuts\Headcount by Bank Title - Quirk.xls")
Set xlSheet2 = xlBook2.Worksheets("Headcount Detail")
Set xlSheet7 = xlBook1.Worksheets("Headcount Summary")
Set xlSheet8 = xlBook2.Worksheets("Headcount Summary")
xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1")
xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1")
xlSheet2.Rows("40:47").Delete

Any help would be greatly appreciated.


"Bob Phillips" wrote:

Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
Set xlBook = Workbook.Open(sFile)
End If


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MacroLearning" wrote in
message
...
Hi all,
I have a macro that works as I want. When I first started building I
included the original filename to copy from. That file will change
every
month. So I want the user to open the file, when the file is opened,
it
will
become "xlBook1". following is the code. I'm getting a debug at that
point,
but am not sure how to get past it.

Any help will be very much appreciated!

'Book 1 is Headcount by Detail Report
Dim xlBook1 As Excel.Workbook
'Book 2 is Quirk (all except SSGA)
Dim xlBook2 As Excel.Workbook
'Sheet7 is HC Summary worksheet from HC Detail Report
Dim xlSheet7 As Excel.Worksheet
'Sheet8 is HC Summary worksheet for Quirk
Dim xlSheet8 As Excel.Worksheet

'Copy and Paste from Headcount Detail to Quirk
Set xlBook1 = Application.GetOpenFilename <=====This is the problem
here
Set xlSheet1 = xlBook1.Worksheets("Headcount Detail")
Set xlBook2 = Workbooks.Open( _
"C:\\Distribution File Cuts\Headcount by Title - Quirk.xls")
Set xlSheet2 = xlBook2.Worksheets("Headcount Detail")
Set xlSheet7 = xlBook1.Worksheets("Headcount Summary")
Set xlSheet8 = xlBook2.Worksheets("Headcount Summary")
xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1")
xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1")
xlSheet2.Rows("40:47").Delete

Open File then Set as
 

Set xlBook = Workbook.Open(sFile)
should be plural
Set xlBook = Workbooks.Open(sFile) ' plural workbookS

Don't feel bad. I did something almost identical to that this morning, but
fortunately kept it to a private audience.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"MacroLearning" wrote in message
...
Thank you for posting an answer to my question.

I am still getting a "debug" when I try to execute the macro. It won't
set
the opened file as xlbook. I get a 424 error code. I changed it to
include
your suggestion.

Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
Set xlBook = Workbook.Open(sFile) <<<========== this is where it hangs up
at
Set xlSheet1 = xlBook.Worksheets("Headcount Detail")
Set xlBook2 = Workbooks.Open( _
"W:\\Distribution File Cuts\Headcount by Bank Title - Quirk.xls")
Set xlSheet2 = xlBook2.Worksheets("Headcount Detail")
Set xlSheet7 = xlBook1.Worksheets("Headcount Summary")
Set xlSheet8 = xlBook2.Worksheets("Headcount Summary")
xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1")
xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1")
xlSheet2.Rows("40:47").Delete

Any help would be greatly appreciated.


"Bob Phillips" wrote:

Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
Set xlBook = Workbook.Open(sFile)
End If


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MacroLearning" wrote in
message
...
Hi all,
I have a macro that works as I want. When I first started building I
included the original filename to copy from. That file will change
every
month. So I want the user to open the file, when the file is opened,
it
will
become "xlBook1". following is the code. I'm getting a debug at that
point,
but am not sure how to get past it.

Any help will be very much appreciated!

'Book 1 is Headcount by Detail Report
Dim xlBook1 As Excel.Workbook
'Book 2 is Quirk (all except SSGA)
Dim xlBook2 As Excel.Workbook
'Sheet7 is HC Summary worksheet from HC Detail Report
Dim xlSheet7 As Excel.Worksheet
'Sheet8 is HC Summary worksheet for Quirk
Dim xlSheet8 As Excel.Worksheet

'Copy and Paste from Headcount Detail to Quirk
Set xlBook1 = Application.GetOpenFilename <=====This is the problem
here
Set xlSheet1 = xlBook1.Worksheets("Headcount Detail")
Set xlBook2 = Workbooks.Open( _
"C:\\Distribution File Cuts\Headcount by Title - Quirk.xls")
Set xlSheet2 = xlBook2.Worksheets("Headcount Detail")
Set xlSheet7 = xlBook1.Worksheets("Headcount Summary")
Set xlSheet8 = xlBook2.Worksheets("Headcount Summary")
xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1")
xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1")
xlSheet2.Rows("40:47").Delete

Open File then Set as
 

Do you have some code such as

Set xlApp = New Excel.Application

or

Set xlApp = CreateObject("Excel.Application")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MacroLearning" wrote in message
...
I'm sorry - I don't know what that means...


"Bob Phillips" wrote:

Do you have ann. application object variable?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MacroLearning" wrote in
message
...
Thank you for posting an answer to my question.

I am still getting a "debug" when I try to execute the macro. It won't
set
the opened file as xlbook. I get a 424 error code. I changed it to
include
your suggestion.

Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
Set xlBook = Workbook.Open(sFile) <<<========== this is where it hangs
up
at
Set xlSheet1 = xlBook.Worksheets("Headcount Detail")
Set xlBook2 = Workbooks.Open( _
"W:\\Distribution File Cuts\Headcount by Bank Title - Quirk.xls")
Set xlSheet2 = xlBook2.Worksheets("Headcount Detail")
Set xlSheet7 = xlBook1.Worksheets("Headcount Summary")
Set xlSheet8 = xlBook2.Worksheets("Headcount Summary")
xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1")
xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1")
xlSheet2.Rows("40:47").Delete

Any help would be greatly appreciated.


"Bob Phillips" wrote:

Dim sFile
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
Set xlBook = Workbook.Open(sFile)
End If


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MacroLearning" wrote in
message
...
Hi all,
I have a macro that works as I want. When I first started building
I
included the original filename to copy from. That file will change
every
month. So I want the user to open the file, when the file is
opened,
it
will
become "xlBook1". following is the code. I'm getting a debug at
that
point,
but am not sure how to get past it.

Any help will be very much appreciated!

'Book 1 is Headcount by Detail Report
Dim xlBook1 As Excel.Workbook
'Book 2 is Quirk (all except SSGA)
Dim xlBook2 As Excel.Workbook
'Sheet7 is HC Summary worksheet from HC Detail Report
Dim xlSheet7 As Excel.Worksheet
'Sheet8 is HC Summary worksheet for Quirk
Dim xlSheet8 As Excel.Worksheet

'Copy and Paste from Headcount Detail to Quirk
Set xlBook1 = Application.GetOpenFilename <=====This is the problem
here
Set xlSheet1 = xlBook1.Worksheets("Headcount Detail")
Set xlBook2 = Workbooks.Open( _
"C:\\Distribution File Cuts\Headcount by Title - Quirk.xls")
Set xlSheet2 = xlBook2.Worksheets("Headcount Detail")
Set xlSheet7 = xlBook1.Worksheets("Headcount Summary")
Set xlSheet8 = xlBook2.Worksheets("Headcount Summary")
xlSheet1.Range("A1:H232").Copy xlSheet2.Range("A1")
xlSheet7.Range("A1:J21").Copy xlSheet8.Range("A1")
xlSheet2.Rows("40:47").Delete