Using Excel 2000:

I used the macro recorder to design the follwoing macro then I went in with
help from books and modified it to try and attempt what I want it to do. I
want ed to try and repeat a process just using a different file and it
doesn't seem to work. Let me show you the macro and show the part that
doesn't work:

Private Sub CommandButton1_Click()



Dim Controls(20)



Sheets("Population Download").Cells.Clear

Sheets("Demographics Download").Cells.Clear

ChDir "c:\Documents and settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenPop = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenPop

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("population download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False

ChDir "c:\Documents and settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenDem = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenDem

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("demographics download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False



Up to this point everything works the next section is the first part of
copying and pasting info to another file and this part works



' Gets date from population datasheet

Sheets("population db").Select

ThisWorkbook.Sheets("population db").Range("A2").Select

ThisWorkbook.Sheets("population db").Range("a2:dk2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\populationdb.xls"

Sheets("sheet1").Range("A1").Select

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("populationdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False





This is the section where I want to do the same procedure from the section
above just to a different file. It seems to work up to the line:
ActiveCell.End(xlDown)(2).Select





' Get data from demographic datasheet

Sheets("demographic db").Select

ThisWorkbook.Sheets("demographic db").Range("A2").Select

ThisWorkbook.Sheets("demographic db").Range("a2:bx2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\demographicdb.xls"

Sheets("sheet1").Range("A1").Select

Range("A1").Activate

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("demographicdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False

' ActiveWorkbook.Close False





End Sub



Any help will be appreciated.



Pete Provencher

Pete,

Two thoughts:

You may not have enough cells filled in column A, in which case this

Sheets("sheet1").Range("A1").Select

Range("A1").Activate

ActiveCell.End(xlDown)(2).Select

would try to take you off the sheet.

Try changing that to this:

Sheets("Sheet1").Select
Range("A65536").End(xlUp)(2).Select

Also, opening a workbook will clear the copy buffer. If your paste is
failing, you need to open the workbook, go back to the workbook from where
you wan to copy, do the copy, return to the newly opened workbook, and then
do the paste.

HTH,
Bernie
MS Excel MVP

" Pete Provencher" wrote in message
...
Using Excel 2000:

I used the macro recorder to design the follwoing macro then I went in
with
help from books and modified it to try and attempt what I want it to do. I
want ed to try and repeat a process just using a different file and it
doesn't seem to work. Let me show you the macro and show the part that
doesn't work:

Private Sub CommandButton1_Click()



Dim Controls(20)



Sheets("Population Download").Cells.Clear

Sheets("Demographics Download").Cells.Clear

ChDir "c:\Documents and settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenPop = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenPop

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("population download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False

ChDir "c:\Documents and settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenDem = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenDem

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("demographics download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False



Up to this point everything works the next section is the first part of
copying and pasting info to another file and this part works



' Gets date from population datasheet

Sheets("population db").Select

ThisWorkbook.Sheets("population db").Range("A2").Select

ThisWorkbook.Sheets("population db").Range("a2:dk2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\populationdb.xls"

Sheets("sheet1").Range("A1").Select

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("populationdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False





This is the section where I want to do the same procedure from the section
above just to a different file. It seems to work up to the line:
ActiveCell.End(xlDown)(2).Select





' Get data from demographic datasheet

Sheets("demographic db").Select

ThisWorkbook.Sheets("demographic db").Range("A2").Select

ThisWorkbook.Sheets("demographic db").Range("a2:bx2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\demographicdb.xls"

Sheets("sheet1").Range("A1").Select

Range("A1").Activate

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("demographicdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False

' ActiveWorkbook.Close False





End Sub



Any help will be appreciated.



Pete Provencher

I'm afraid that didn't work. It still goes to A1 and doesn't go to the first
empty row which is A2. I have column names in row 1. I don't get an error
wit your changes but it still doesn't work. I don't think it is pasting as
it doesn't go to the first blank row. The data that is copied is in the
clipboard. I can't figure out why it works with the populationdb file.

Pete Provencher

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Pete,

Two thoughts:

You may not have enough cells filled in column A, in which case this

Sheets("sheet1").Range("A1").Select

Range("A1").Activate

ActiveCell.End(xlDown)(2).Select

would try to take you off the sheet.

Try changing that to this:

Sheets("Sheet1").Select
Range("A65536").End(xlUp)(2).Select

Also, opening a workbook will clear the copy buffer. If your paste is
failing, you need to open the workbook, go back to the workbook from where
you wan to copy, do the copy, return to the newly opened workbook, and
then
do the paste.

HTH,
Bernie
MS Excel MVP

" Pete Provencher" wrote in message
...
Using Excel 2000:

I used the macro recorder to design the follwoing macro then I went in
with
help from books and modified it to try and attempt what I want it to do.
I
want ed to try and repeat a process just using a different file and it
doesn't seem to work. Let me show you the macro and show the part that
doesn't work:

Private Sub CommandButton1_Click()



Dim Controls(20)



Sheets("Population Download").Cells.Clear

Sheets("Demographics Download").Cells.Clear

ChDir "c:\Documents and settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenPop = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenPop

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("population download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False

ChDir "c:\Documents and settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenDem = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenDem

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("demographics
download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False



Up to this point everything works the next section is the first part of
copying and pasting info to another file and this part works



' Gets date from population datasheet

Sheets("population db").Select

ThisWorkbook.Sheets("population db").Range("A2").Select

ThisWorkbook.Sheets("population db").Range("a2:dk2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\populationdb.xls"

Sheets("sheet1").Range("A1").Select

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("populationdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False





This is the section where I want to do the same procedure from the
section
above just to a different file. It seems to work up to the line:
ActiveCell.End(xlDown)(2).Select





' Get data from demographic datasheet

Sheets("demographic db").Select

ThisWorkbook.Sheets("demographic db").Range("A2").Select

ThisWorkbook.Sheets("demographic db").Range("a2:bx2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\demographicdb.xls"

Sheets("sheet1").Range("A1").Select

Range("A1").Activate

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("demographicdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False

' ActiveWorkbook.Close False





End Sub



Any help will be appreciated.



Pete Provencher

It did work. I must have done something wqrong the first time and I wrote it
on row 65536. Once I cleared that out it worked as you said. Thanks alot. It
was starting to bug my brain.

Pete Provencher

" Pete Provencher" wrote in message
...
I'm afraid that didn't work. It still goes to A1 and doesn't go to the
first
empty row which is A2. I have column names in row 1. I don't get an error
wit your changes but it still doesn't work. I don't think it is pasting as
it doesn't go to the first blank row. The data that is copied is in the
clipboard. I can't figure out why it works with the populationdb file.

Pete Provencher

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Pete,

Two thoughts:

You may not have enough cells filled in column A, in which case this

Sheets("sheet1").Range("A1").Select

Range("A1").Activate

ActiveCell.End(xlDown)(2).Select

would try to take you off the sheet.

Try changing that to this:

Sheets("Sheet1").Select
Range("A65536").End(xlUp)(2).Select

Also, opening a workbook will clear the copy buffer. If your paste is
failing, you need to open the workbook, go back to the workbook from
where
you wan to copy, do the copy, return to the newly opened workbook, and
then
do the paste.

HTH,
Bernie
MS Excel MVP

" Pete Provencher" wrote in message
...
Using Excel 2000:

I used the macro recorder to design the follwoing macro then I went
in
with
help from books and modified it to try and attempt what I want it to
do.
I
want ed to try and repeat a process just using a different file and it
doesn't seem to work. Let me show you the macro and show the part that
doesn't work:

Private Sub CommandButton1_Click()



Dim Controls(20)



Sheets("Population Download").Cells.Clear

Sheets("Demographics Download").Cells.Clear

ChDir "c:\Documents and
settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenPop = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenPop

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("population
download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False

ChDir "c:\Documents and
settings\pete.sitetech03\desktop\Demographic
Downloads"

FileToOpenDem = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")

Workbooks.Open FileToOpenDem

Worksheets(1).Range("A1:J80").Copy

ActiveWorkbook.Close False

Windows("Quick Facts 2004.xlt").Activate

ThisWorkbook.Sheets("demographics
download").Range("A1").PasteSpecial
Paste:=xlPasteAll

Application.CutCopyMode = False



Up to this point everything works the next section is the first part
of
copying and pasting info to another file and this part works



' Gets date from population datasheet

Sheets("population db").Select

ThisWorkbook.Sheets("population db").Range("A2").Select

ThisWorkbook.Sheets("population db").Range("a2:dk2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\populationdb.xls"

Sheets("sheet1").Range("A1").Select

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("populationdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False





This is the section where I want to do the same procedure from the
section
above just to a different file. It seems to work up to the line:
ActiveCell.End(xlDown)(2).Select





' Get data from demographic datasheet

Sheets("demographic db").Select

ThisWorkbook.Sheets("demographic db").Range("A2").Select

ThisWorkbook.Sheets("demographic db").Range("a2:bx2").Select

Selection.Copy

ChDir "c:\Documents and settings\pete.sitetech03\my documents"

Workbooks.Open "c:\Documents and settings\pete.sitetech03\my
documents\demographicdb.xls"

Sheets("sheet1").Range("A1").Select

Range("A1").Activate

ActiveCell.End(xlDown)(2).Select

Selection.PasteSpecial Paste:=xlValues

Selection.PasteSpecial Paste:=xlFormats

Sheets("sheet1").Range("A1").Select

Workbooks("demographicdb.XLS").Close SaveChanges:=True

Application.CutCopyMode = False

' ActiveWorkbook.Close False





End Sub



Any help will be appreciated.



Pete Provencher