Macro problem
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
|