Code to Copy Data From one Worksheet to Another

[Gos-C]

Hi all,

I need your help writing some VBA codes.

I have two workbooks: Workbook1 has two sheets – Cash Register and Suppliers; and Workbook2 has one sheet – Clients. The users of Workbook2 do not have access to Workbook1, and information entered in Workbook2 is required (and has to be manually re-entered) in Workbook1.

I tried IFs and VLOOKUPs to pull the data, but due to the large number of cells, the workbook is awfully slow – taking a very long time to save (even with Automatic Calculation/Recalculate before save turned off).

I am using Excel 2007.

When a user enters an ID number, which begins with either “S” (e.g., SB-00010) or “P” (e.g., PADA-012-034-0567), in column E of Cash Register:

(1)

If that ID number begins with “S”, I want the code to look up that ID number in column A of Suppliers and copy the corresponding cell from each of the following columns:

B
C
D
E
F
G

and then paste them in the following columns of the Cash Register in the same row as the ID number looked up:

F (paste B)
G (paste C)
H (paste D)
I (paste E)
J (paste F)
K (paste G)

OR

(2)

If the ID number begins with “P”, I want the code to look up that ID number (e.g., PADA-012-034-0001) in column Q of Clients, and copy the corresponding cell from each of the following columns:

R
Z
AA
AB
AC
AD

and then paste them in the following columns of the Cash Register in the same row as the ID number looked up:

F - paste R;
H - paste AA (if it is not blank); add a hyphen (if AA is not blank); paste Z (e.g., 1625-123 Yonge Street)
I - paste AB
J - paste AC
K - paste AD

Thanks in advance for your help. I really appreciate it.

Gos-C

[rylo]

Hi

1) can you build a couple of example files - that way anything used in testing will at least match your structure and data format. Don't use any real data, but make sure the formats are consistent.

2) If you can use formulas to get the correct results, perhaps a macro to create the relevant formulas, then value paste the results. Or even leave 1 instance of each formula that can be used to copy to the relevant areas, then value paste the results.


rylo