Wrote a macro in visual basic editor, can't get it to repeat the action

[iomighty]

Hi all,

I built a worksheet that copied data from sheet 1 into sheet 2 using V
editor.

However now I want the line data I enter in sheet 1 to copy into shee
2 in perpetuity and it is not working.

I am fairly new to working in visual basic but have had tremendou
success until now. If anyone can offer any suggestion I would b
immensely thankful.

Matt in S

 

[Frank Kabel]

Hi
you may post your existing macro for checking

 

[AlfD]

Hi!

Obviously the best way to get Excel to copy data from one worksheet t
another is to put suitable entries in the second sheet which "call" th
data from the first. For sure, this works in perpetuity, too!

So: you have some particular reason for using VBA?

Al

 

[iomighty]

Hi Frank,

I am still working on making the macros work on additional entries.
think I am missing something really simple here. I am tryin
variations on inserting a continuous instruction in the code but
result in errors.

I have attached the macro (inside VBE) if you wanted to check. An
recomendations would be helpful. Thanks in advance Frank,

Mat

Attachment filename: test book excel project matt.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=57908

 

[Frank Kabel]

Hi
to be honest I won't open your file if it contains macros. Simply post
your code (or at least the relevant part) as plain text

 

[iomighty]

Hi Frank-

Now that I think about it, I would not open a file with macros either.


I posted the below from my VB editor. I have been playing around wit
it in various ways. I read in a reference book that I can make an
line function continuous but have not been successful with any of th
commands variations I have tried.

Any suggestions would be helpful. I do not mind experimenting wit
different ideas but so if you can point me in a direction that yo
think may work I can start there.

AS you can probably tell by my basic VB work that I am relatively ne
at this. However I am learning so much right now and hope to be mor
functional in VBA in the near future.

Once again Frank I appreciate any comments or suggestions.

cheers,
matt, sf



Sub Duplicate1()

Range("A2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("A4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("A5").Select

Range("B2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("B5").Select

Range("C2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("C5").Select

Range("D2").Select
ActiveCell = 100
Range("D3").Select
ActiveCell = 110
Range("D4").Select
ActiveCell = 120
Range("D5").Select
ActiveCell = 130
Range("D5").Select

Range("E2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("E5").Select

Range("F2").Select
ActiveCell.FormulaR1C1 = "No Sales Order"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("F4").Select
ActiveCell.FormulaR1C1 = "No Sales Order"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("F5").Select


Range("G2").Select
ActiveCell.FormulaR1C1 = "ARJE"
Range("G3").Select
ActiveCell.FormulaR1C1 = "REX_1"
Range("G4").Select
ActiveCell.FormulaR1C1 = "ARJE"
Range("G5").Select
ActiveCell.FormulaR1C1 = "REX_1"
Range("G5").Select

Range("H2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("H4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("H5").Select

Range("I2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C*-1"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C*1"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C*-1"
Range("I5").Select

Range("J2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("J4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("J5").Select

Range("K2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C*-1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C*-1"
Range("K5").Select

Range("L2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("L4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("L5").Select

Range("M2").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-0]C"
Range("M3").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-1]C"
Range("M4").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-2]C"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=Entry!R[-3]C"
Range("M5").Select

Range("N2").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!N2"
Range("N3").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!N2"
Range("N4").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!O2"
Range("N5").Select
Selection.NumberFormat = "dd-mmm-yy"
ActiveCell = "=Entry!O2"
Range("N5").Select

Range("O2").Select
ActiveCell = "=Entry!P2"
Range("O3").Select
ActiveCell = "=Entry!P2"
Range("O4").Select
ActiveCell = "=Entry!P2"
Range("O5").Select
ActiveCell = "=Entry!P2"
Range("O5").Select

Range("P2").Select
ActiveCell = "=Entry!Q2"
Range("P3").Select
ActiveCell = "=Entry!Q2"
Range("P4").Select
ActiveCell = "=Entry!Q2"
Range("P5").Select
ActiveCell = "=Entry!Q2"
Range("P5").Select




End Sub

 

[Frank Kabel]

Hi
this can definetly be shortened. But some things first:
- why are you using VBA at all for this. This could also be achieved
with simple formulas
- could you explain how you want to copy your data. e.g. which cells
should go to which cells in your target sheet.

 

[iomighty]

Hi Frank,

I decided to do this in VBA becasue, 1) I am trying to learn more abou
it, and 2) I thought it would be easier.

The cells I am copying from my master sheet do not correspond to th
same columns in my second sheet.

My idea when I started this was to enter one line of data on my maste
sheet. And for this one line of data to copy into my second sheet in
Journal Entry format to avoid having to manually retype all of th
data.

As you may be able to get an idea from the extract in my VB editor
the one line of data I entered in my first sheet reflects into fou
lines in my second sheet.

What I have written in the VB Editor translates exactly how want it.
The problem lies in getting this to work the same as I enter more line
of data on my master sheet.

Did I go about this in the wrong way

 

[Matt G]

Hi Nick, Sorry I don't have you email. But I wanted to thank you fo
your tips. It definately shortened my commands by 1/3, and I learned
little more about VB programming. Unfortunately I still have not bee
able to to get my macro to work on my coresponding data entries fro
"sheet1".

I just read about a sheet)array function that I am going to tinke
with. Thanks again,
cheer