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