Macro problem, after inserting new row, paste special

Hi there,

I've got myself in a really sticky situation here. I've been trying to make this work for the past few days now at any given moment I got, but it seems like nothing I do solves the problem.

Here's what I'm trying to do (using a hair salon database system as example):

Through the interface of a user-friendly worksheet capture form, the user enters in various values for inputting a new customer's details - title, name, date of birth, address, telephone numbers etc. These are then referenced via VLOOKUPs and "equal to" formulas on another worksheet, whereby they are placed in a row. This all works fine, the VLOOKUPs and "equal to"s are simple enough.

The newly inputted data then needs to be tranferred into a permanent 'database' on another worksheet. It needs to be added to the bottom of the table in order to keep the customers in chronological order, as they are each assigned an individual ID upon input (which is used when making an appointment).

However, I am unable to do this. The method I've conjured up so far involves this set of actions (performed whilst recording a macro):
1) Select 'Customers' worksheet.
2) CTRL+F - find 'end.' (I placed this in a cell at the beginning of another row underneath the last row of data)
3) Right-click cell, 'Insert...', 'Shift cells down'
4) Move up one cell - B7
5) Formula = '=*one cell up - B6*+1', Enter
6) Select 'workbook operation data' worksheet, highlight new customer info
7) Copy
8) Select 'Customers' worksheet
9) Select 'B7', Right-click, 'Paste special..', 'Values'

Then of course clear the capture form of the inputted data.

The only part of the above method that works, is the creation of a new ID (steps 3, 4, 5). Everything else is wrong, because each time I run the macro it pastes the new customer's data into row 7.. I think this is because I am pasting it there when recording it. Therefore I tried not physically selecting that cell with my mouse, but rather 'CTRL+F'ing 'end.' again and navigating to the correct cell by pressing 'right' then 'up' keys.

As you can probably tell, I'm quite new to programming macros and using VBA.. And my deadline for completing the project is tomorrow, so I'm getting quite stressed out about it!

If the solution is a matter of using different VBA code, then I know how to access that code and replace it if need be.

Thanks to anyone who helps me out with this, you would be my saviour!


(I can upload screenshots of my project if it would better document my steps..)

Hi, try:

Please Login or Register  to view this content.

This inserts the row first and then copies and pastes the customer data

mccreaso

I wasn't sure how the formula was to be set. In this example, I set it as the first column and offset the range to copy by one column. It also uses a dynamic method to determine the range to copy. It also includes a routine where you don't select or activate the sheets.

Please Login or Register  to view this content.

thankyou very much for your replies! got it working correctly now