Report

VBA for copying from data entry sheet to corresponding sheets

Ask a question OMCHABRIA 3Posts Monday October 9, 2017Registration date October 14, 2017 Last seen - Last answered on Oct 16, 2017 at 11:19 AM by TrowaD
Hi,

I am new to excel and would need some help. I wanted a vba for copying from data entry sheet to corresponding sheets automatically in the same workbook.

Thanks in Advance.
Helpful
+0
plus moins
Hi Omchabria,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("E")) Is Nothing Then Exit Sub
Target.EntireRow.Copy _
Sheets(target.value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


This is the basic code to do said request.

How it works:
When you confirm an entry (which will be a sheet name) in column E (as specified in the 2nd code line), that row will be copied to the corresponding sheet in the first available row.

How to use:
Right-click the entry sheets tab and select view code. Paste the code in the big white field.

Best regards,
Trowa

OMCHABRIA 3Posts Monday October 9, 2017Registration date October 14, 2017 Last seen - Oct 10, 2017 at 03:21 AM
Hello Trowa D,

Thanks very much for your quick reply.

my exact query is :
DATA
SNO.BUS/WINGER DATE DETAILS AMT REMARKS
1 R-1 28-05-2016 WHEEL ALIGNMENT 550 APEX AUTOMOBILES
11 R-2 24-05-2016 ELECTRIC FITTING 268 PREMIER ELECTRICALS
30 R-7 05-01-2017 NEW BATTERY 5000 SRI LAXMI BATTERIES

I have a excel having around 125 sheets, sheets named as per 2nd coloumn(eg R1, R2, R7). Need the entries to be copied automatically in the corresponding sheets whenever i enter the data(main) sheet.

Would be thankful if you can help

Omchabria
Reply
Leave a comment
Helpful
+0
plus moins
Hi Omchabria,

When you want to copy your row to the corresponding sheet when you enter/confirm the sheet name in column B then use:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
Target.EntireRow.Copy _
Sheets(target.value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


When you want this to happen when you enter the last data in the row, which is column F, right? Then use:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
Target.EntireRow.Copy _
Sheets(Target.Offset(0, -4).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


Best regards,
Trowa
Leave a comment
Helpful
+0
plus moins
Hi Trowa,

Thank you very much with the help, but I am facing a problem on the copied sheets. the old data is being replace by the new entry. I want the old data and the new data in the next line. Really apperiaciate your help

Thanks in advance.
TrowaD 2282Posts Sunday September 12, 2010Registration date ModeratorStatus October 19, 2017 Last seen - Oct 16, 2017 at 11:19 AM
Hi Omchabria,

In the provided codes, column A is used to determine the last row used. When this column doesn't hold any data, then the row is presumed empty.

Let's take a look a the 4th code line:
Sheets(target.value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

When you want the code to look at column B to determine the last used row, then we have to change the A to B. Now the copied row doesn't fit the destination, so we have to change the offset value from (1,0) to (1,-1). Which means 1 cell down and 1 cell to the left.

The 4th code line would then look like:
Sheets(target.value).Range("B" & Rows.Count).End(xlUp).Offset(1, -1)


Can you make it work now?

Best regards,
Trowa
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!