Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro for pasting | Excel Discussion (Misc queries) | |||
Excel 1004 Error When Pasting Special W/ Macro | Excel Discussion (Misc queries) | |||
Pasting from Excel Clipboard with VBA Macro | Excel Programming | |||
Pasting text from Excel to Word (macro) | Excel Programming | |||
Excel Macro code help - re pasting rows | Excel Programming |