Automatically copying rows from one sheet to another depending on a cells input

Question

Thank you for your quick response. Actually I¡¯m unable to understand the code that you have provided me since I don¡¯t have an in-depth knowledge on codes. I¡¯m finding it difficult to place the information ( like workbook names and sheet names etc) according to my scenario. I¡¯m providing you with an example which is close to my scenario. Could you please update the code which you have provided me with the below information in order for me to get a clearer view on the matter.

So basically I¡¯m working on a folder with the path name S:\Operational Risk\Projects\Plan. Under this folder I have the master workbook named ¡° MASTER¡± with a sheet named ¡° Master worksheet¡± and it consists of 60 columns and 400 rows. The folder also consists of sub workbooks ¡° Type A ¡±, ¡° Type B ¡±, ¡° Type C ¡±, ¡° Type D ¡±, ¡° Type E ¡± , ¡° Type F ¡±, ¡° Type G ¡± , ¡° Type H ¡± , ¡° Type I ¡± which are linked to ¡°Master worksheet¡± according to the relevant types. All the sub workbooks consist of the same headings as the Master Worksheet.

Following is a sample data present in ¡°Master worksheet¡± :-

TYPE FREQUENCY NAME BRANCH RATING COMMENTS A 2 Mobile North east 1 B 2 Television North east 2 C 4 Computer North west 3 D 3 Laptop North west 4 E 1 Computer South east 1 F 2 Stereo South west 2 G 4 Camera South east 3 H 3 Ipod South west 4 I 3 Refrigerator North west 1

I have only highlighted in this example 6 columns and 9 rows but the actual master workbook consists of 60 columns and 400 rows.

Type A workbook consists of the following data:-

TYPE FREQUENCY NAME BRANCH RATING COMMENTS A 2 Mobile North east 1

Type B workbook consists of the following data:- TYPE FREQUENCY NAME BRANCH RATING COMMENTS B 2 Television North east 2

Now say for example I have another detail which I input at the end of ¡°Master worksheet¡±:-

TYPE FREQUENCY NAME BRANCH RATING COMMENTS A 2 Washing Machine North east 1

I would like this data since it belongs to TYPE A to automatically get updated in TYPE A workbook at the end after the last data cell. The same goes for all the other Type workbooks. Could you please update the code accordingly with the relevant names that have been provided inorder for me to have a better understanding.

Theversion is 2003

Thank you for your time

Answer

Sorry, I don't save this stuff, I have no idea what code I gave you. Can you copy and paste what you want me to change? You really should be able to just change the path and sheet or workbook names. I also had the code commented for you so that once you pasted it in ancode module, you would see the comments.

Question

Since I do not have a detailed knowledge on many aspects of(especially on how to create formulas and codes) i thought I should seek your help on a issue that I have at hand.

Basically I'm working on a project wherein I have a master workbook which contains all the data and is classified in columns based on :

TYPE FREQUENCY NAME BRANCH RATINGS

Say for example the following is the data: TYPE FREQUENCY NAME BRANCH RATINGS A 1 Phone Abc 3 A 2 Television Def 5 B 3 Computer Ghi 7 B 4 Laptop Jkl 9 C 5 Stereo Mno 8 C 6 A/C Pqr 1 D 7 Camera stu 4

This master workbook is later subdivided into different workbooks depending on the TYPE wherein the master workbook is linked to the sub workbooks . So say for example a separate workbook for Type A and a separate workbook for TYPE B and these are linked to the master workbook.

Since the master workbook keeps getting updated every now and then I have decided to input all the details at the end after the last input but I have to update all the other linked workbooks as well. Could you provide me with a code wherein when I input the type in the master sheet the entire row with all details gets automatically updated to the other workbook to which the Type relates. Say for example there is another entry which I input in the master workbook

TYPE FREQUENCY NAME BRANCH RATINGS A 5 Mobile GHI 3

I would want this entire row to be automatically copied to the TYPE A workbook.

I hope I explained the issue well to your understanding.

Answer

I THINK I understand.........

Based on that, do this vba. And since I don't fully understand every detail of your situation, I think the best thing is that this code is triggered by a human (i.e., you) not automatically based on some cell change.

You are going to have to carefully look at all the things you'll have to adjust. for example, I constructed this code to work for my computer (paths, files, folders, workbook names, sheet names,) but you'll have to do it for yourself and change those parts in the code).

open visual basic editor in excel. Insert, Module. copy paste this.

whenever you have finished entering a new row in source workbook, make sure that the cell to the far left (column A) is selected. then run this macro:

Sub copy()

'this code is going to be based on the selection current. 'whenever you want to run it, make sure the cell in column A is selected. 'run it manually by going to macros.

'since you didn't tell me what the name(s) of your workSHEETS were, in either book, 'i'm going to assume they're called Sheet1

Dim wb As Workbook Dim ws As Worksheet Dim currentselection As Range Set currentselection = Selection

Set wb = Workbooks.Open("U:\" & currentselection.Value & ".xlsx") 'obviously you'll have to adjust all of this currentselection.EntireRow.copy wb.Worksheets("Sheet1").Range("A65000").End(xlUp).Offset(1, 0) wb.Close (True)

End Sub