[EXCEL 97] Using VBA script to copy

[ereborn]

Hi there

I would like to please some1 to help me with usin VBA scripts in Excel to copy value from one sheet to another.

Due to I can't programm in VBA I tried to set up a makro to do it and then I copied it to ONBUTTONCLICK event.

But it does not work ... because it writes some error.

Sub Makro1()
'
' Makro1 Makro
' Makro set up 10.2.2006 by ereborn
'
' Shortcut keys: Ctrl+h
'
	Range("B3").Select
	Selection.Copy
	Sheets("Sheet2").Select
	Range("B3").Select
	ActiveSheet.Paste
	Application.CutCopyMode = False
	Sheets("Sheet1").Select
	Range("B4").Select
	Selection.Copy
	Sheets("Sheet2").Select
	Range("B4").Select
	ActiveSheet.Paste
	Application.CutCopyMode = False
End Sub

This one above works perfect ... BUT

Private Sub CommandButton1_Click()
Range("B3").Select
	Selection.Copy
	Sheets("Sheet2").Select
	Range("B3").Select
	ActiveSheet.Paste
	Application.CutCopyMode = False
	Sheets("Sheet1").Select
	Range("B4").Select
	Selection.Copy
	Sheets("Sheet2").Select
	Range("B4").Select
	ActiveSheet.Paste
	Application.CutCopyMode = False
End Sub

This one doesn't ...

It is same as makro in CTRL+H but this writes error :

Error during processing '1004'

Method SELECT of class RANGE failed

I don't know how to solve this problem ...

Problem is only if I need to copy something between 2 sheets.

In one sheet it works perfect.

Could some1 help ?

Thanx in advance

[jaclaz]

Mind you I don't have right now a copy of Excel, so I am just trying to remember things.....

...but there are several objects in Excel Visual Basic (in order of "importance"):

Workbook

Worksheet

Sheets

Range

You should add a "Select" statement for "Workbook" and "Sheet" BEFORE the "Range" one.

This is not necessary on the recorded macro as a Workbook AND a Sheets is already selected.

These might be of help:

http://j-walk.com/ss/excel/faqs/xl95faq4.htm

http://www.excel-vba.com/v-microsoft-excel-range.htm

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

http://www.anthony-vba.kefra.com/vba/vbabasic2.htm

jaclaz

[ereborn]

Thanks a lot for advice ... I try links you sent and I try to solve my problem.

Bye

[ereborn]

Thanks so much ... I solve it

Your tutorials are good.

I found everything what I needed

Thank again.