Excel & Macros - Moving data between sheets

  MoggieX 16:44 02 Sep 03
Locked

Hi,

This is going to be either really easy or really hard I'm not sure which, lol.

Basically I am using excel (XP version) to store and maintain orders and customer detials, once an order is ready for despatch I manually copy and paste the information into a word document to create the final despatch note. Although long winded, it works.

But I would like for a macro to take the information in one row and put it into another excel sheet in the format of the despatch note and then for me to be able to print it and to save it as a new file.

I have tried to record macros, but they always go back to the row that I stared recording from, not the new line with the next customers detials.

Ideally I would it to run from which ever cell I have selected for example A1 then it takes the information form the next cells B1, C1, D1 etc... and places them into the new sheet. Then if I select A2 as the starting cell, to go through B2, C2, D2 etc... on to the same sheet (so it can be saved with a different name for my records.

I'm sure that one of yu guys (and girlies) will be able to let me know if this is possible or I asking too much of excel.

I'm open to all suggestions on wha I can do and thanks in advance.

Kind regards,

Matt

  VoG II 17:05 02 Sep 03

This is possible using a macro. Can you confirm that it is just Columns B, C and D that you want to copy to the new sheet.

Is it supposed to just transfer 1 row at a time or start on your current row and work its way down, row by row, until the data "runs out"?

  tbh72 17:56 02 Sep 03

It sounds as if you are moving the data to a list, if this is the case the very first line of your macro should be to insert an empty line at the top of the list ready to receive the new data.

  MoggieX 19:02 02 Sep 03

Hi,

It's just the one row, I'll try to explain a little better below:

Input Data:

A B C
1 X Y Z
2 X Y Z


Ouput data on a new sheet:

A B
1 Customer name: X
2 Customer Address: Y
3 Item Decription: Z

If the macro can take the value of X from A1 and put it into a new cell in another sheet, so that it then reads 'Customer name: X' where X is the imported value from the first sheet where all the data is kept.

Once the macro has transfered the values in A1, B1 and C1 it's to stop.
But when I want to do the same for A2, B2 and C2 the macro just goes back to A1 and starst there not at A2.

I hopw thats clearer than mud :-)

  MoggieX 19:04 02 Sep 03

Input Data:

A B C

1 X Y Z

2 X Y Z

Ourput data:

A B
1 Customer name: X

2 Customer Address: Y

3 Item Decription: Z

Hope this time it's formatted properly

  VoG II 19:12 02 Sep 03

Slightly clearer!

It sounds like you've recorded a macro to do this. ALT+F11 to open the Visual Basic Editor, If necessary click in the left hand pane to display the Modules and double click on the module. Then copy the code and paste it here (double-spaced) and we can try modifying it.

Alternatively, you could try modifying the following:

Sub MoggieX2()

Dim myRow As Integer

Dim InSheet As Worksheet, OutSheet As Worksheet

Set InSheet = ActiveSheet

Set OutSheet = Sheets("Sheet2")

myRow = ActiveCell.Row

OutSheet.Range("A1").Value = InSheet.Range("A" & myRow).Value

OutSheet.Range("B1").Value = InSheet.Range("B" & myRow).Value

OutSheet.Range("C1").Value = InSheet.Range("C" & myRow).Value

OutSheet.Range("D1").Value = InSheet.Range("D" & myRow).Value

OutSheet.Range("E1").Value = InSheet.Range("E" & myRow).Value

OutSheet.Range("F1").Value = InSheet.Range("F" & myRow).Value

'etc.

End Sub

To insert the above code, copy it, open the VBE, Insert/Module and paste it in (or paste it at the bottom of an existing module).

This thread is now locked and can not be replied to.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…