VBA code for copying data from external archive and range(d6:d70)=range(g6:g70)

[fher9728]

Hi,

I have a table were I need to paste numbers from external archives, not from my workbook, the thing that I have these columns:

# -B	UPC - C	Cant. -D	Descripción - E	Cod. SAP -F	Cantidad -G

I need two things, when i paste data to upc if I only paste one upc for example I want that in column #-B shows the first number - 1, and if I paste two values of upc I want it to show in Colum #-B 1, 2 and that successively, the other thing i want to do is that if i a paste a value in Column Cant. -D I want it to show the same exact value in column cantidad-F in a consecutive way, I can do it with formulas but I need to be with vba code, and I've been trying and I can't get a solution to this, this is an example of what i want,

please if you can help me,

thanks

Cross post right in here:
https://www.mrexcel.com/board/threads/copying-data-from-external-archive.1130163/

 

[BobBridges]

Where is your program pasting these values from, fher? Do you have them in three variables, or in other cells, or in an array, or what? And how do you know when you have 1, or 2, or 3 values? If you have three variables with values 1, 23 and 0, does that constitute two values (the 0 indicating that there is nothing to go in D)?

 

[fher9728]

Where is your program pasting these values from, fher? Do you have them in three variables, or in other cells, or in an array, or what? And how do you know when you have 1, or 2, or 3 values? If you have three variables with values 1, 23 and 0, does that constitute two values (the 0 indicating that there is nothing to go in D)?

I pasted them exactly from pdfs so, for example,

i have a look a like purchase order from the format that i put right above, the thing is that the purchase orders have these columns:

UPC	Quantity
upc1	1
upc2	2
upc	3
upc4	4
upc5	5

the process I do is to select with ALT and select all UPC column values, and the same process with quantity column, the thing that I want, is when i paste those values in my worksheet in column Cant-D, I want to automatically show the same value in column Cantidad-G

 

[fher9728]

Where is your program pasting these values from, fher? Do you have them in three variables, or in other cells, or in an array, or what? And how do you know when you have 1, or 2, or 3 values? If you have three variables with values 1, 23 and 0, does that constitute two values (the 0 indicating that there is nothing to go in D)?

Hi @BobBridges could you please help me??

 

[BobBridges]

Ok, I'm back. So you're saying you're doing a manual cut-and-paste from a .pdf document? You highlight a bit of the pdf, do a copy, then switch to Excel and do a paste? If so, I don't think you can control how the paste works directly; you'd have to paste the data into a worksheet and then run a VBA that'll read the data from there and rearrange it, either in that worksheet or into another one. We can talk about how your VBA program would do that, if you don't already know how to do that part yourself.

And from your description on Thursday, you're actually doing two cut-and-pastes, one for the UPC code(s) and a separate cut-and-paste for the quantity, which you paste into column D. If you simply want column G to be the same as column D, you don't need a VBA program to do that for you; just put "=D2" in G2.

 

[fher9728]

Ok, I'm back. So you're saying you're doing a manual cut-and-paste from a .pdf document? You highlight a bit of the pdf, do a copy, then switch to Excel and do a paste? If so, I don't think you can control how the paste works directly; you'd have to paste the data into a worksheet and then run a VBA that'll read the data from there and rearrange it, either in that worksheet or into another one. We can talk about how your VBA program would do that, if you don't already know how to do that part yourself.

And from your description on Thursday, you're actually doing two cut-and-pastes, one for the UPC code(s) and a separate cut-and-paste for the quantity, which you paste into column D. If you simply want column G to be the same as column D, you don't need a VBA program to do that for you; just put "=D2" in G2.

Hi BobBridges, I do exactly as you exposed before, I exactly want to do =d2 in g2 and forward, the problem with doing it with formula is that the worksheet its been manipulated by many people and protecting sheet won't result that great to me because that =d2 in cell g2 I need to be shown as a value,

For example

Cell D2=1

Cell G2= =D2

That is doing with formula, but I need, that when I put a value right in range d6:d70

It will be showed up as a value to automatically in range G6:G70,

Is there a way to do that?

 

[BobBridges]

If G6:G70 has "=D6" etc in in, then when you paste values into any part of D6:D70 those values will automatically show up in G6:70 also. But I get what you're saying about the sheet being used (and perhaps modified) by other users). And I think you're saying that in any case you want not "=D6" in G6, but the actual value. I guess I don't need to know why.

You can write a quick VBA program to copy the contents of D6:D70 to G6:G70, and run the program whenever you like. Such a program might look like this:

Sub YourName
  Set ows = ActiveSheet
  For jr = 6 to 70
    ows.Cells(jr, 7).Value = ows.Cells(jr, 4).Value
    Next jr
  End Sub

This program loops from 6 through 70, and on each row copies the value of the 4th column (D) to the 7th column (G). You would run this program any time you pasted new values into that range of column D.

You can also tell Excel to run this program automatically whenever changes are made to that range. That's a little more complicated, though, and you may be unable to do it because most companies are (rightly) nervous about automatic Excel programs. (If they allow Excel programs to run automatically, it's much harder to be keep everyone from writing an automatic program that does harm instead of good. But we can talk about how to do that, if you need to know.

 

[fher9728]

If G6:G70 has "=D6" etc in in, then when you paste values into any part of D6:D70 those values will automatically show up in G6:70 also. But I get what you're saying about the sheet being used (and perhaps modified) by other users). And I think you're saying that in any case you want not "=D6" in G6, but the actual value. I guess I don't need to know why.

You can write a quick VBA program to copy the contents of D6:D70 to G6:G70, and run the program whenever you like. Such a program might look like this:

Sub YourName
  Set ows = ActiveSheet
  For jr = 6 to 70
    ows.Cells(jr, 7).Value = ows.Cells(jr, 4).Value
    Next jr
  End Sub

This program loops from 6 through 70, and on each row copies the value of the 4th column (D) to the 7th column (G). You would run this program any time you pasted new values into that range of column D.

You can also tell Excel to run this program automatically whenever changes are made to that range. That's a little more complicated, though, and you may be unable to do it because most companies are (rightly) nervous about automatic Excel programs. (If they allow Excel programs to run automatically, it's much harder to be keep everyone from writing an automatic program that does harm instead of good. But we can talk about how to do that, if you need to know.

I get it, but there isn't a way to do it with worksheet event change?? for everytime a put a value in the range d6:d70, the value copies itself automatically to range g6:g70?

 

[BobBridges]

Yes. At least think there is; I've never tested how WorksheetChange responds to pasting. Here is some documentation on the WorksheetChange event. Your version might look like this:

Private Sub Worksheet_Change(ByVal org as Range) 
  For Each oc In org
    If oc.Column <> 4 Then Goto IterateCell
    If oc.Row < 6 Then Goto IterateCell
    If oc.Row > 70 Then Goto IterateCell
    oc.Offset(0, 3).Value = oc.Value
IterateCell:
    Next jr
  End Sub

I haven't tested that, so there may be a bug in it somewhere, but here's how I mean it to work:

"Private Sub Worksheet_Change(ByVal org as Range)": To make this work, most of this statement has to be exactly as stated. You don't have to name the incoming range "org"; that's just my own naming standard. ('o' for an object, 'rg' for a range. Call that whatever you want.) But the sub has to be named Worksheet_Change, the argument has to be as Range and ByVal, and I'm pretty sure it has to be defined as a Private sub.

Then I set up a loop to look at each cell (named "oc") in the range:

"If oc.Column <> 4 Then Goto IterateCell": If the cell isn't column 4 (column D), then we don't want to do anything at all about this change. In that case, jump to the IterateCell label, to skip to the next cell.

If the cell is in column D then execution flows to one or both of the next two statements; they check to be sure the cell is in the proper row range. Again, if not, ignore the change.

"oc.Offset(0, 3).Value = oc.Value": If it passed all three tests, it gets to this statement. The Offset method points to a cell that is 0 rows down from oc, and 3 columns to the right; that's column G on the same row. It sets the value of that cell to this cell's value.

If the user is updating the worksheet manually, this sub will be executed every time he updates any single cell; but the sub will look at the cell, see whether it's out of range, and finish so fast the user will never notice anything happening (unless G changes). If he pastes a bunch of values into the worksheet, or selects a bunch of cells and hits <Delete>, it'll check each cell that got changed, ignore any that are outside of D6:D70, and copy the rest to column G. If you paste a lot of values to column D you might notice it taking a second or two to copy the changes to G, but not long.

As I said, I may have made a mistake here. But try it, and debug it if necessary, and see whether you understand how it works.

 

[BobBridges]

It occurs to me there's a way to make this a bit faster. It may not matter, because we're only talking about 65 cells. But if you ever want to do this for a larger paste area, for hundreds or even thousands of cells, you'll definitely want to speed things up.

One method involves telling Excel to hold off, while the Sub executes, on updating the screen:

vsu = Application.ScreenUpdating 'save the current value
Application.ScreenUpdating = False 'turn off screen updating
' You can also tell Excel to stop updating calculations temporarily, if it helps:
vc = Application.Calculation 'save the current value
Application.Calculation = xlCalculationManual 'turn off automatic calculation

' Now do your thing.  Afterward:

Application.ScreenUpdating = vsu 'restore updating
Application.Calculation = vc 'restore calculation mode

The other method that occurs to me—there's probably no need for both of them, one or the other should be sufficient—involves the INTERSECT function. INTERSECT takes two (or more) ranges and returns a range of cells that is common to both of them. So you might write the program like this:

Private Sub Worksheet_Change(ByVal org as Range)
  set orgD = Intersect(org, Range("D6:D70")) 'gives you just the updated cells that are in your range
  orgD.Offset(0, 3).Value = orgD.Value
  End Sub:

I've never used Intersect, and I never use A1 notation while writing Excel code, so you might find this doesn't work the way I think it will. But something like it should be right.

 

[fher9728]

Yes. At least think there is; I've never tested how WorksheetChange responds to pasting. Here is some documentation on the WorksheetChange event. Your version might look like this:

Private Sub Worksheet_Change(ByVal org as Range) 
  For Each oc In org
    If oc.Column <> 4 Then Goto IterateCell
    If oc.Row < 6 Then Goto IterateCell
    If oc.Row > 70 Then Goto IterateCell
    oc.Offset(0, 3).Value = oc.Value
IterateCell:
    Next jr
  End Sub

I haven't tested that, so there may be a bug in it somewhere, but here's how I mean it to work:

"Private Sub Worksheet_Change(ByVal org as Range)": To make this work, most of this statement has to be exactly as stated. You don't have to name the incoming range "org"; that's just my own naming standard. ('o' for an object, 'rg' for a range. Call that whatever you want.) But the sub has to be named Worksheet_Change, the argument has to be as Range and ByVal, and I'm pretty sure it has to be defined as a Private sub.

Then I set up a loop to look at each cell (named "oc") in the range:

"If oc.Column <> 4 Then Goto IterateCell": If the cell isn't column 4 (column D), then we don't want to do anything at all about this change. In that case, jump to the IterateCell label, to skip to the next cell.

If the cell is in column D then execution flows to one or both of the next two statements; they check to be sure the cell is in the proper row range. Again, if not, ignore the change.

"oc.Offset(0, 3).Value = oc.Value": If it passed all three tests, it gets to this statement. The Offset method points to a cell that is 0 rows down from oc, and 3 columns to the right; that's column G on the same row. It sets the value of that cell to this cell's value.

If the user is updating the worksheet manually, this sub will be executed every time he updates any single cell; but the sub will look at the cell, see whether it's out of range, and finish so fast the user will never notice anything happening (unless G changes). If he pastes a bunch of values into the worksheet, or selects a bunch of cells and hits <Delete>, it'll check each cell that got changed, ignore any that are outside of D6:D70, and copy the rest to column G. If you paste a lot of values to column D you might notice it taking a second or two to copy the changes to G, but not long.

As I said, I may have made a mistake here. But try it, and debug it if necessary, and see whether you understand how it works.

Thank you for taking your time to explain me, and helping me! It works perfect, thank you so much Bob!

 

[fher9728]

It occurs to me there's a way to make this a bit faster. It may not matter, because we're only talking about 65 cells. But if you ever want to do this for a larger paste area, for hundreds or even thousands of cells, you'll definitely want to speed things up.

One method involves telling Excel to hold off, while the Sub executes, on updating the screen:

vsu = Application.ScreenUpdating 'save the current value
Application.ScreenUpdating = False 'turn off screen updating
' You can also tell Excel to stop updating calculations temporarily, if it helps:
vc = Application.Calculation 'save the current value
Application.Calculation = xlCalculationManual 'turn off automatic calculation

' Now do your thing.  Afterward:

Application.ScreenUpdating = vsu 'restore updating
Application.Calculation = vc 'restore calculation mode

The other method that occurs to me—there's probably no need for both of them, one or the other should be sufficient—involves the INTERSECT function. INTERSECT takes two (or more) ranges and returns a range of cells that is common to both of them. So you might write the program like this:

Private Sub Worksheet_Change(ByVal org as Range)
  set orgD = Intersect(org, Range("D6:D70")) 'gives you just the updated cells that are in your range
  orgD.Offset(0, 3).Value = orgD.Value
  End Sub:

I've never used Intersect, and I never use A1 notation while writing Excel code, so you might find this doesn't work the way I think it will. But something like it should be right.

If I got a case like this, definitely I'll try it, Thank you Bob!!