Cut/Copy in VBA [Solved/Closed]

Chamberlyn 4 Posts Sunday November 25, 2012Registration date December 19, 2013 Last seen - Nov 25, 2012 at 09:47 PM - Latest reply: Chamberlyn 4 Posts Sunday November 25, 2012Registration date December 19, 2013 Last seen
- Nov 26, 2012 at 08:27 PM
Hello,


I am fairly new to Macros but I am trying to write vba code in Excel 2003 version that will enable me to cut/copy a table from one worksheet to another worksheet in the same work book where the data is increased in rows everytime it is updated.

I have tried the below resize but this only copies the first cell in the range

Worksheets("Sheet3").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows, numColumns).Select
Selection.Copy
Sheets("SAP Report").Activate
Range("A6").Select
ActiveSheet.Paste
Range("A6").Select
Application.CutCopyMode = False
Selection.Copy

End Sub

I would be greatful for any assistance, thanks in advance
See more 

3 replies

TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Nov 26, 2012 at 09:34 AM
0
Thank you
Hi Chamberlyn,

So you would like to copy your manual selected range and paste it to the next sheet in the first available row.

If this is true then the following code should suffice:
Sub test()
Sheets("Sheet3").Activate
Selection.Copy Sheets("SAP Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

Best regards,
Trowa
Chamberlyn 4 Posts Sunday November 25, 2012Registration date December 19, 2013 Last seen - Nov 26, 2012 at 04:19 PM
0
Thank you
Good Morning TrowaD,

Thank you so much for you reply. I actually need to copy a table which increases each update, to a second worksheet in the same workbook. The copy from the first sheet (sheet 3) will over write any data existing in the second worksheet (SAP Report) but the first line of the copied data will start in row A6:K6 in SAP Reports worksheet.

The actual table data range in ("sheet3") to be copied starts in columns A2:K? as I do not want to copy the headers. In the "SAP Report" worksheet where the data is going the table must start as mentioned above into row A6. I can't do a sheet cut and paste because there are 3 more (L,M,N)columns in the (SAP Report) worksheet which contain formula that I don't want to overwrite. Hope this clarifies the issue.

Kind Regards
Chamberlyn 4 Posts Sunday November 25, 2012Registration date December 19, 2013 Last seen - Nov 26, 2012 at 08:27 PM
0
Thank you
For anybody who is interested, I have worked out a solution to my issue

Set tbl = ActiveCell.CurrentRegion
Worksheets("Sheet3").Activate
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
Selection.Copy
Sheets("SAP Report").Select
Range("A6").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Application.CutCopyMode = False

Thanks for the help though TrowaD, Regards