Macro to copy set fields from one workbook to another?

[Sharkey90]

Hi All,

Hoping someone may be able to advise me...

Background:

Users enter data in an excel workbook which we use as a template for checking our work. Once they complete the 'ticksheet' the file is saved as by the user into a folder on an L:Drive. In the workbook, there are 11 fields which are completed (Y/N). I want users to be able to use a macro which will take the information in these fields and copy it into a new workbook (which will be saved and shared). From this, I have created a range of formulas which will calculate a risk rating and will provide monthly stats from the data of each individual ticksheet - i.e. creating an overall view of the work we do.

Requirement:

I need help creating a macro which will take the data from Workbook A (cells A1:A10) and paste it into Workbook B (cells C1:C10) then save workbook B so that the next person who updates Workbook A can then run the macro and repeat the process adding their data to workbook B.

Steps:

User updates workbook A
User runs macro
Macro takes info in cells A1:A10 and copies into cells C1:C10 in workbook B
Macro saves workbook B
User will proceed to save workbook A as per usual.

Next user updates workbook A
process repeats.

The issue I foresee are that my boss won't accept a macro which is slow and adds time to our process so it needs to be slick and be very easy to use i.e. click a button on the ribbon.

This is my first post so apologies if I haven't been clear/you need more information. If you do, please let me know

Thanks in advance!

 

[JLGWhiz]

Try this

Sub saveData()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Add
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1)
sh1.Range("A1:A10").Copy sh2.Range("C1")
End Sub