How can I copy and paste info from one sheet to another via macro

**jamie.c** · 06-04-2009, 07:04 AM

Hello Everyone!

I am Hoping there is a simple solution to my problem. Basically I have to worksheets, the first is filled out and at the end Ive place a button that I would like to open the second sheet and fill in cells that have duplicate info from the first, so just copy & paste but kind of automated for speed, is this possible and would anyone be kind enough to help me out please?

I have only got as far as opening the second document with the macro, after that im stuck!

The Info I would like to be copied across from the "service Diary" to the "Service Report" would be As follows:
*CUSTOMER
*CONTACT
*MACHINE TYPE
*SERIAL NUMBER
*QUOTE NUMBER
*ORDER NUMBER

Many Thanks

Jamie C

**D_Rennie** · 06-04-2009, 11:57 AM

Try
For the customer feild
if the Service Visit Report MASTER.xls is opened

    "Selection.Copy
    Windows("Service Visit Report MASTER.xls").Activate
    Range("B16:E16").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False"

**D_Rennie** · 06-04-2009, 12:05 PM

I Noticed that you want the file saved as the S/N

You could try

ActiveWorkbook.SaveAs "C:\" & Sheet1.Range("RaNgE").Value & "_" & Sheet1.Range("RaNgE").Value & "_" & Format$(Date, "dd-mm-yy") & ".xlsm", FileFormat:=xlNormal

This will allow you to save the file as the S/N and another range like Customer if you wanted and will also add the date to it.

**jamie.c** · 06-05-2009, 03:58 AM

Good morning,

Thank you for your reply, Ive just added your code to the macro and I doesnt do anything, do you have any ideas?

Thanks again for your time

Jamie.c

**D_Rennie** · 06-06-2009, 08:20 AM

You could run the code on buttion click or when the workbook is closed.

This Code Needs Both Workbooks Open, Ive Used Paste Special To Not Change The Differances Layout Betweent The Two Sheets. Cell Ranges Need To Be Changes For *CONTACT*MACHINE TYPE*SERIAL NUMBER*QUOTE ECT

    Range("D7:E7").Select
    Selection.Copy
    Windows("Service Visit Report MASTER-1.xls").Activate
    Range("B16:E16").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

This Will Open The WorkBook For You, The Workbooks.Open Filename:= Need To Be Changes To Suit Your Setup.

    Range("D7:E7").Select
    Selection.Copy
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\1 1\Desktop\Service Visit Report MASTER-1.xls"
    Range("B16:E16").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

You Would Need To Change Cell Range's, Im Not Sure If It Will Work With Merges Cells.

ActiveWorkbook.SaveAs "C:\" & Sheet1.Range("RaNgE").Value & "_" & Sheet1.Range("RaNgE").Value & "_" & Format$(Date, "dd-mm-yy") & ".xlsm", FileFormat:=xlNormal

**jamie.c** · 06-08-2009, 11:49 AM

Hello Everyone,

Im wondering if anyone would be able to explain line by line how the reply from D-Rennie works, I hoping Il be able to understand what changes need making to enable it to work on my worksheet.

Any Help would be much appreciated

Jamie.c

**D_Rennie** · 06-09-2009, 07:35 AM

Select the following code

Private TheTestBtn_Click()
    Range("D7:E7").Select
    Selection.Copy
    Windows("Service Visit Report MASTER-1.xls").Activate
    Range("B16:E16").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
end sub

Copy Code
open workbook service Diary
Open visual basic editor
double click this workbook.
paste the code
close visual basic editor
insert buttion on sheet and assign macro to TheTestBtn
open Service Report since the code will need it open
go back to service Diary click button.

Now play with the other codes.
Let me know how it goes-
Cheers

**jamie.c** · 06-09-2009, 08:48 AM

Hello,

Thank you so much for your reply.

Ive had a play and something is happening!! not quite how I wanted it but never the less.

I have got the report opening with the "creat report" button In cell U7 of the service dairy and I have added your code which seems to be taking part of the address off of the service report sheet (A7) Brindley close....................... and adding it in to cell B16. of the service report.

Do you know where it is going wrong please? I have very little knowledge of code, and I struggle to know what I am looking for!

Thank you in advance

Jamie.c