Problems Pasting in Macro
 

I just took over a job duty from one of my co-workers. On the spreadsheet he
uses there is a macro that copies an area of data, then opens an existing
workbook, then pastes the data just copied. This macro works fine on his
computer but not on mine. When I run the macro the area of data copies, but
after it opens the existing workbook the data is no longer in copy mode so it
can not paste the data in the new workbook and I get an error message. Is
there some kind of option that my excel does not have selected that makes the
data stay in copy mode after a new file is opened? I would greatly appreciate
any help anybody may have for me. The following is part of the macro where
the error message comes up as it does not make it past the paste part. I
could rewrite this part of the macro, but this exact problem happens like 30
times in the existing macro and I don't really want to try and change every
single one. Thanks.

Sub TestForAllFiles()
Application.ScreenUpdating = False
Master = ActiveWorkbook.Name
CompCount = Sheets(1).Range("B4")
CurYrEnd = Sheets(1).Range("E5")
PrYrEnd = Sheets(1).Range("E6")
CrWkTot = Sheets(1).Range("E7")
PrWKTot = Sheets(1).Range("E8")
Month = Sheets(1).Range("E4")
Sheets("ConBAL").Select
Cells.Select
Selection.Copy
Workbooks.Open ThisWorkbook.Path & "\HorseHair.xls"
Sheets(1).Select
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Cons Balance Sheet.xls"
Application.DisplayAlerts = True
Sheets(1).Range("A3") = CurYrEnd & " With Comparitive Totals For " & PrYrEnd
BalBook = ActiveWorkbook.Name
Windows(Master).Activate

Problems Pasting in Macro
 

I always try to minimize the commands between a copy and paste.

I would open the workbook first, then do the copy and paste in one command.


set bk = Workbooks.Open( ThisWorkbook.Path & "\HorseHair.xls")
thisworkbook.Worksheets("ConBAL").Cells.copy _
Destination:=bk.worksheets(1).Cells
Application.DisplayAlerts = False
bk.SaveAs ThisWorkbook.Path & "\Cons Balance Sheet.xls"
Application.DisplayAlerts = True

--
Regards,
Tom Ogilvy

"Hawkfan757" wrote:

I just took over a job duty from one of my co-workers. On the spreadsheet he
uses there is a macro that copies an area of data, then opens an existing
workbook, then pastes the data just copied. This macro works fine on his
computer but not on mine. When I run the macro the area of data copies, but
after it opens the existing workbook the data is no longer in copy mode so it
can not paste the data in the new workbook and I get an error message. Is
there some kind of option that my excel does not have selected that makes the
data stay in copy mode after a new file is opened? I would greatly appreciate
any help anybody may have for me. The following is part of the macro where
the error message comes up as it does not make it past the paste part. I
could rewrite this part of the macro, but this exact problem happens like 30
times in the existing macro and I don't really want to try and change every
single one. Thanks.

Sub TestForAllFiles()
Application.ScreenUpdating = False
Master = ActiveWorkbook.Name
CompCount = Sheets(1).Range("B4")
CurYrEnd = Sheets(1).Range("E5")
PrYrEnd = Sheets(1).Range("E6")
CrWkTot = Sheets(1).Range("E7")
PrWKTot = Sheets(1).Range("E8")
Month = Sheets(1).Range("E4")
Sheets("ConBAL").Select
Cells.Select
Selection.Copy
Workbooks.Open ThisWorkbook.Path & "\HorseHair.xls"
Sheets(1).Select
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Cons Balance Sheet.xls"
Application.DisplayAlerts = True
Sheets(1).Range("A3") = CurYrEnd & " With Comparitive Totals For " & PrYrEnd
BalBook = ActiveWorkbook.Name
Windows(Master).Activate