Activesheet.Paste does OK manually between worksheets but not
Hi again Billy,
Obviously both workbooks were open at the correct worksheets when you were
recording the macro. For a number of reasons, the activesheet of the workbook
might not be the one you need. Therefore add lines to select the correct
worksheet both for the source and the output otherwise it will select cells
in whatever is the active sheet.
Sample below showing where to place them. Replace Sheet1 and Sheet2 with the
name of the worksheets where you are copying from and pasting to.
Sub Macro16()
'
Windows("2008 Bank Statements.xls").Activate
Sheets("Sheet1").Select
Range("A21:D59").Select
Selection.Copy
Windows("Bank Statement Import Worksheet.xls").Activate
Sheets("Sheet2").Select
Range("C4").Select
ActiveSheet.Paste
End Sub
Also, does the data to be copied always start at A21 and do you only have
blank space below the data to be copied. if so, the following code will
select the range of varying number of rows.
Sub Macro1()
Sheets("Sheet1").Select
Range(Cells(21, "A"), Cells(Rows.Count, "D").End(xlUp)).Select
End Sub
Cells(Rows.Count, "D").End(xlUp) is like placing the cursor on the very last
cell in column D and then holding the Ctrl key down and pressing up arrow and
it selects the first cell it comes to with data in it.
Also another way when recording a macro you can select a range where there
is no blank cells by first selecting the start cell then holding the Ctrl and
Shift keys down and pressing first the down arrow and then the right arrow.
Of course if there is data to the right or below the required range then it
will include it so it depends on how your data is on the worksheet. Looks
like this.
Range("A21").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Don't confuse any of this with good programming. It is not how I would do it
but it works and it will get you started on the road to programming.
--
Regards,
OssieMac
|