Copy Partial Rows Btw Chosen Excel Worksheets

Microsoft Excel 2003 (full product)
February 5, 2011 at 01:17:48
Specs: Windows XP Pro, P4 / 2GB
I am currently using Excel 2003 and need to move data automatically between worksheets in a workbook as described below. For purposes of this example, let’s assume the workbook contains worksheets named #1, #2, #3 and #4. I need to automatically move data from worksheet #1 to the others based on values entered in columns A and B of each row.

For discussion purposes, let's assume that Worksheet #1, Row 12, is as follows:
Cell A12 – 1st Specified Worksheet
Cell B12 – 2nd Specified Worksheet
Cell C12 – Description
Cell D12 – Number
Cell E12 – Other Info
Cell F12 – Other Info

When a specific worksheet (#2, #3 or #4) is entered into cell A12 of worksheet #1, the information in cells A12 – D12 will be copied to the next available row in the specified worksheet. As part of the data copy, the value in cell D12 must be changed from positive to negative in the receiving worksheet. The data in columns E and F will not be copied. Also, it is possible that cell A12 is left blank at which point, no command is executed.

When a specific worksheet (#2, #3 or #4) is entered into cell B12 of worksheet #1, the information in cells A12 – D12 will be copied to the next available row in the specified worksheet. The sign (+ / - ) of the value in cell D12 will remain the same in the receiving worksheet. Again, the data in columns E and F will not be copied.

As each additional row in worksheet #1 is filled in, the data would be moved in the same manner. Each row will function independently.

Any help on the most effective way to accomplish this would be appreciated.


See More: Copy Partial Rows Btw Chosen Excel Worksheets

Report •


#1
February 5, 2011 at 14:49:52
I'm a little confused. Here's the gist of what I see, disregarding the sign change of D12 for now.

If a worksheet name is entered in A12, you want A12:D12 copied to the worksheet named in A12.

If a worksheet name is entered in B12, you want A12:D12 copied to the worksheet named in B12.

If A12 is left blank, no command is executed. (I assume that means that nothing should be copied.)

Does that mean that if a worksheet name is entered in B12 but A12 is blank, nothing should happen?

Does that mean that if a worksheet name is entered in A12 and B12 the range would be copied twice?

Will anything other that a valid sheet name ever be entered in A12 or B12?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 6, 2011 at 02:31:33
You understood most of what I tried to describe. Here's a more detailed explanation.

If a worksheet name is entered in A12, I want A12:D12 copied to the next available line in the worksheet named in A12.

If a worksheet name is entered in B12, I want A12:C12 and –D12 copied to the next available line in the worksheet named in B12.

If A12 is left blank but B12 is populated, then cells should be copied to B12 as specified above.

If A12 is populated but B12 is blank, then cells should be copied to A12 as specified above.

If A12 and B12 are both populated, then cells should be copied to both workbooks as specified above.

So, basically, as information is entered in the main worksheet, it is automatically copied to the others as noted.

Thanks!


Report •

#3
February 11, 2011 at 11:28:41
Did you receive my response post on Feb 6 ?

Report •

Related Solutions

#4
February 11, 2011 at 11:54:00
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
    wsname = Target
     nxtRw = Sheets(wsname).Range("A" & Rows.Count).End(xlUp).Row + 1
     Range("A" & Target.Row & ":D" & Target.Row).Copy _
       Destination:=Sheets(wsname).Range("A" & nxtRw)
     Sheets(wsname).Range("D" & nxtRw) = Sheets(wsname).Range("D" & nxtRw) * -1
End If
  If Target.Column = 2 Then
    wsname = Target
     nxtRw = Sheets(wsname).Range("A" & Rows.Count).End(xlUp).Row + 1
     Range("A" & Target.Row & ":D" & Target.Row).Copy _
       Destination:=Sheets(wsname).Range("A" & nxtRw)
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
February 11, 2011 at 13:29:46
Thank you ! I'll try it over the weekend and let you know.

Report •

#6
February 12, 2011 at 01:12:42
I'm having difficulty getting the macro to work. More than likely, it's my lack of familiarity with macros. Would you be willing to provide some detailed assistance via a more direct communicaiton method? If so, I can provide you with an email address and we can take if from there.

Report •

#7
February 12, 2011 at 05:47:43
I'm not sure what steps you took to try the macro. This is what should be done:

Right click the sheet tab for the sheet in which you want this to work.
Choose View Code
Paste the code into the window that opens.

When you make a change to Column A or B the code will fire.

If you'll describe the problem you are having, I'll see if I can help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 14, 2011 at 03:38:08
I was able to get the macro to run, thank you. However, not all of the information is copied as noted below.

The worksheets are named ACT, P and T, and data is entered in worksheet ACT where the macro is running. The test setup in worksheet ACT is shown below.
Column A: T
Column B: P
Column C: text
Column D: 50

After entering P in column A, the data in columns A through D is copied to worksheet P, however, the value for column B has not yet been entered and therefore cannot be copied. When T is entered in column B, all the proper information is copied to worksheet T and worksheet P remains unchanged.

The base assumption is the data in columns C and D of the ACT worksheet are already in place before entries are made in columns A and B.

The macros should wait for entries in both columns A and B before firing. However, that will create a problem when one of the columns needs to be blank (copying only to one worksheet). If the macro could ignore the entry of the current worksheet name, that should work. Here's the three scenarios:

Example 1
Column A: ACT
Column B: T
Column C: text
Column D: 50
* No copying takes place until values are entered in columns A and B.
* Columns A - D would be copied to worksheet T only.
* The value 'ACT' is ignored (nothing copied to worksheet ACT)

Example 2
Column A: T
Column B: ACT
Column C: text
Column D: 50
* No copying takes place until values are entered in columns A and B.
* Columns A - C and minus column D would be copied to worksheet T only.
* The value 'ACT' is ignored (nothing copied to worksheet ACT)

Example 3
Column A: T
Column B: P
Column C: text
Column D: 50
* No copying takes place until values are entered in columns A and B.
* Columns A - C and minus column D would be copied to worksheet T.
* Columns A - D would be copied to worksheet P

I have one last question. Is there a way to have the macro run in only one section of the ACT worksheet such as between rows 125 and 250?

Thank you!!


Report •

#9
February 20, 2011 at 10:34:52
I'm just following up since I haven't seen a response from you. Thanks.

Report •

#10
February 20, 2011 at 14:33:06
re: "The macros should wait for entries in both columns A and B before firing"

That's not how Worksheet_Change macros work. Worksheet_Change events fire whenever a change (with certain exceptions) is made to the sheet. Whether or not the code actually does anything is a different story.

For example, let's look at a simple Worksheet_Change macro:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
    MsgBox "You Changed Something In Column 1"
  End If
End Sub

"ByVal Target As Range" means that it is going to use the cell (the "Target") as a Range.

"If Target.Column = 1 Then" means that it is going to determine if the Target was in Column 1, known in Excel as Column A

If you make a change to Column 1 (Column A), the code is going to fire and display the message because the If statement is TRUE.

If you make a change to Column 2 (Column B), the code is still going to fire but it won't display the message because the If statement is FALSE.

In other words, the code doesn't "wait" for a change to a particular column or cell or range, it fires on the change and then simply executes the lines in the code.

If you only want the code to "do something" once entries are made to both cells, then you'll need to come with some logic for the code to follow so that it reads what is in both cells and copies what you want where.

It can't "ignore" a change, it can only do, or not do, something when a change is made based on how the code is written.

So based on that, if you can come up with some logic that the code will follow each time you make a change to either Column 1 or 2, then maybe we can get it to work.

The other option is to use a "standard" (manual) macro with a button that you click after you have made the entries you want.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question