Copy specific data from one excel sheet to another using VBA Macro

[REUBEN]

Hi There,

I have a smilar need to this thread in the forum but require the macro to perform more functions.

The current macro in this thread is:

[/COLOR][COLOR=#333333]Option Explicit[/COLOR]

Sub CopySpecial()Dim wbDEST As Workbook    Set wbDEST = Workbooks.Open("C\Book2.xls", , , , "password")        If ActiveWorkbook.ReadOnly = True Then        MsgBox "Book2 is being used. Please wait....", , "Save Record"        wbDEST.Close False        Exit Sub    Else        ThisWorkbook.Sheets("Sheet1").Range("a2:a7").Copy        wbDEST.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues        wbDEST.Close True    End If </pre>[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

The additional functions I need the code to perform are:

1) The data to be copied will of course be a range but this range could have at times one two or more rows of data to be copied. Would a input box be a good option to select the data in this case? If so could you please help me with the code.

2) The second excel sheet (shared with other users to edit at the same time) is on a shared drive in on a internal server. That must be opened and the data from first sheet be copied and saved (with values only -of course).

3) After the data from one sheet is copied on to the other sheet, I want the copied range in the first sheet to be highlighted in green, indicating that data has been copied to the 2nd sheet.

I'm quite a newbie with the macros so cannot figure this out myself.

Thank you.
Reuben

 

[REUBEN]

Hi All,

Could anyone help me with the above please?

Thank you.