Pasting in an Excel Macro
 

I have a file that I'm trying to perform some calculations in (via a macro)
and then a macro file to simply store the macro. All I'm trying to do is to
copy a cell, then highlight a area and then paste. Instead of pasting in my
working file, it is pasting in my Macro file!

Any idea how I force it to paste in my working file? (i.e. the same place
from which it copies the value) Here's my code:

ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False

Thanks.

Pasting in an Excel Macro
 

Hi,

You need to activate the correct workbook and worksheet. Your code is not
the best way to achieve your desired result but if it works then I won't
confuse you at this stage by altering it. I just added code at the start to
ensure correct workbook and worksheet is activated. I have assigned the
workbooks and worksheet to variables that can be used in lieu of the workbook
and worksheet names.

Dim wbThis As Workbook
Dim wbData As Workbook
Dim wsData As Worksheet

Set wbThis = ThisWorkbook 'Probably not required

'Edit workbook name to name of your data workbook
Set wbData = Workbooks("Book3.xlsm")

'Edit worksheet name to your data worksheet name
Set wsData = wbData.Sheets("Sheet1")

wbData.Activate 'Ensure that data workbook is the active workbook

wsData.Activate 'Ensure that correct worksheet is activated

ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select

'Following code will return you to the workbook with the macro
'wbThis.Activate 'Commented out at the moment


--
Regards,

OssieMac


"KiplingHfx22" wrote:

I have a file that I'm trying to perform some calculations in (via a macro)
and then a macro file to simply store the macro. All I'm trying to do is to
copy a cell, then highlight a area and then paste. Instead of pasting in my
working file, it is pasting in my Macro file!

Any idea how I force it to paste in my working file? (i.e. the same place
from which it copies the value) Here's my code:

ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False

Thanks.

Pasting in an Excel Macro
 

Thanks OssieMac. Is there any way that this can be written so that I don't
need to specify a file name? Since the filename will always be different, I
would like it so that it will be able to work without renaming my file each
time I run it.

"OssieMac" wrote:

Hi,

You need to activate the correct workbook and worksheet. Your code is not
the best way to achieve your desired result but if it works then I won't
confuse you at this stage by altering it. I just added code at the start to
ensure correct workbook and worksheet is activated. I have assigned the
workbooks and worksheet to variables that can be used in lieu of the workbook
and worksheet names.

Dim wbThis As Workbook
Dim wbData As Workbook
Dim wsData As Worksheet

Set wbThis = ThisWorkbook 'Probably not required

'Edit workbook name to name of your data workbook
Set wbData = Workbooks("Book3.xlsm")

'Edit worksheet name to your data worksheet name
Set wsData = wbData.Sheets("Sheet1")

wbData.Activate 'Ensure that data workbook is the active workbook

wsData.Activate 'Ensure that correct worksheet is activated

ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select

'Following code will return you to the workbook with the macro
'wbThis.Activate 'Commented out at the moment


--
Regards,

OssieMac


"KiplingHfx22" wrote:

I have a file that I'm trying to perform some calculations in (via a macro)
and then a macro file to simply store the macro. All I'm trying to do is to
copy a cell, then highlight a area and then paste. Instead of pasting in my
working file, it is pasting in my Macro file!

Any idea how I force it to paste in my working file? (i.e. the same place
from which it copies the value) Here's my code:

ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False

Thanks.