|
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 |
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 |
Macro problem
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 |
Macro problem
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 |
| All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com