Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Require fine tune to Macro for copy, paste and than delete

0

Hi Teachexcel Team,

Hope everyone is doing great.

I have made a macro on copy, paste and than delete in attached sheet.

1. Here, I want to copy the entire filled row from "sheet1" i.e. from A3:C6 than

2. Paste at end below to "Report" and than

3. Delete the previous Rows from Report.

Require your help to fine tune the same, as i am facing some minor errors in running the macro.

 Would appreciate your support for the same.

Regards,

Revised Question:

Below Time detail highlighted in bold to understand the difference.
1. Below Row to delete from Report:

Name | Stock production time | Current day Stock of Factory until time

a 2:00:00 2

b 2:00:00 3

c 2:00:00 4

d 2:00:00 5

2. Than, below Rows to copy from Sheet 1 

a 5:00:00 2

b 5:00:00 3

c 5:00:00 4

d 5:00:00 5

3. and than paste to Report below past available Row and upgrade:

Name | Stock production time | Current day Stock of Factory until time

a 3:00:00 2

b 3:00:00 3

c 3:00:00 4

d 3:00:00 5

a 4:00:00 2

b 4:00:00 3

c 4:00:00 4

d 4:00:00 5

a 5:00:00 2

b 5:00:00 3

c 5:00:00 4

d 5:00:00 5

As you see, only below deleted rows are not available in final Report, rest is available:

Name | Stock production time | Current day Stock of Factory until time

a 2:00:00 2

b 2:00:00 3

c 2:00:00 4

d 2:00:00 5

Same I wanted and tried to run the macro in excel.
Hope you understand the problem.
Request you to Please help for solution.
Regards,
 

Answer
Discuss

Discussion

Sunil. I've provided an answer but it would help other users if your question was fuller e.g.  explained the issues you faced. Please bear in mind for future questions.
John_Ru (rep: 6102) Jul 16, '21 at 9:37 am
Add to Discussion

Answers

0
Selected Answer

Sunil

IFrom your revised question, I see that you want to remove the first 4 rows of the rEport sheets and add 4 new rows to the  end,

I still think it's easier to delete the Report range first.

Also it's not necessary to (and better not to) activate sheets or select cells. That can be done with the revised code below and the Range.PasteSpecial method. I've added some comments below to help you see what's happening:

Option Explicit

Sub Macro13()
'
' Macro13 Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim LstRw As Long, RepLst As Long 'varialble for last in Sheet 1 and Report

'Delete 4 rows after row 2 in Report first:

With Sheets("Report")
    .Range("A3:A6").EntireRow.Delete
    RepLst = WorksheetFunction.Max(.Range("A" & .Rows.Count).End(xlUp).Row, 3) 'pick row 3 or more

    ' copy from Sheet 1
    Sheets("Sheet1").Range("A3:C6").Copy

    ' paste to same rows in Report
    .Range("A" & (RepLst + 1)).PasteSpecial

    Application.CutCopyMode = False

End With

MsgBox "Data added to Report"

ActiveWorkbook.Save
End Sub
Hope this fixes your issues.
Discuss

Discussion

BTW your column C title in Sheet1 refers to Factory 1 but Report refers only to Faxctory.
John_Ru (rep: 6102) Jul 16, '21 at 10:00 am
Hi John,
Thank you for your response and solution given.
I am really sorry for the miscommunication in words at 1st place on deleting the previous rows. It was meant to delete the previous 1st 4 rows of Report, which you can see in the coding which i was trying to run in excel.
I have run the macro with your coding, but the macro is deleting the entire past filled Rows, which were available in Report.
I wanted to delete only the 1st 4 past Row available in Report and than new Rows to copy from Sheet 1 and paste below available Row and Not to replace the past all rows.
Same I wanted at 1st place and tried to run the same macro in excel for the same.
I will try to make understand with Revised Question in the said excel sheet.
Request you to Please go through the revised question mention above.
Regards,
SunilA (rep: 56) Jul 17, '21 at 3:39 am
Sunil,   Please  see revised answer.   Kindly state your future questions carefully and fully. E,g,. if you say "Delete the previous Rows from Report", don't expect us to guess and delete just four!
John_Ru (rep: 6102) Jul 17, '21 at 6:54 am
Thank you so much John for your kind understanding and providing solution.
Regards,
SunilA (rep: 56) Jul 17, '21 at 7:13 am
Glad I was able to help Sunil. Thanks for selecting my answer
John_Ru (rep: 6102) Jul 17, '21 at 7:20 am
Glad I was able to help Sunil. Thanks for selecting my answer
John_Ru (rep: 6102) Jul 17, '21 at 7:20 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login