Excel 2003 Macro's with two excel Sheets

[Meller]

Hi guys,

First time posting... so hopefully this makes sense.

I work a company that does calibration. At times we have calibration jobs where we calibrate 2400+ pieces of equipment.

We currently have a master calibration list for every company we work for. On it are several columns containing Serial Numbers, Nomenclature, Location, Cal Tech, Cal Date, Due Date, etc...

I'd like to be able to make another cal sheet where I import our calibration certifications. Is there a way to set up some type of macro that with one push of a button, it will take my blank cal cert template and file in all the data on it from my master cal list?

To help with any confusion I'll attempt to make an example on here.

A B C D E
1 Serial # Cal Date Next Due Tech
2 001 01/01/11 01/01/12 John Smith
3 002 01/01/11 01/01/13 Jane Smith
4 003 01/02/11 01/02/12 John Smith
5
6

Then for my cal cert(s), I'd need it to pull info from all the said columns in line two to be put in certain spots, next cert would be line 3, next line 4... etc...

I'd like to be able to just have my blank cal cert template readily available and be able to just import data from any master cal list I have and for it to auto create a new cert for each line item.

Is this possible? Hopefully I didn't confuse anyone too much.

Thank you,
Mike

 

[andiemac]

Welcome to the board!

I would say that yes, it is possible to make a button that with one push will move data from one worksheet to another worksheet.

In order to get some suggestions you will need to give more detail. Does your target cert template have a similar columns structure, in that data from Column A will end up in Column F? Or are there individual cells that will do a calculation on each record and strore those calculated results? What do you forsee the end result looking like?

 

[Meller]

No, my Cal Cert template is not in the same format. There is a lot more that goes with my cert as it's what we give our customers...

Basically in the Cal Cert (for example... i don't know the exact location because I've yet to recreate my cert in excel)

While Serial Numbers are in Column B on the Master Cal List, they might be in column D, line 20 on the cal cert... but the cal cert will start consistent on where the data will go...

 

[andiemac]

I would suggest completing some of the development work of your destination worksheet first. Knowing the destination ranges would be requried for writing the code.

Generally if you intend to copy ranges like A2:A100 (or whatever the end is, which can be a dynamic range) and paste it into a cert template starting points like D22 or H17 and down as far as long as the data range is, that can be done. But you need the source location and destination locations to write the code.

 

[andiemac]

Here is a very basic sample of what you have described. The destination ranges are just made up at this point, but it will give you a starting place.

Use the controls menu to create a button and assign this macro to it.

Sub cmdMoveToCert_Click()
Dim LR As Long
'determine last row in range of data
'assuming your serial# are in A and in a contiguous range
    Worksheets("Source").Activate
    Range("A1").End(xlDown).Select
    LR = ActiveCell.Row
    
'move serial# data
    Worksheets("Source").Range("A2:A" & LR).Copy
    
    Worksheets("Cert_Temp").Activate
    Range("C11").Select
    ActiveSheet.Paste
'move Cal Date data
    Worksheets("Source").Range("B2:B" & LR).Copy
    
    Worksheets("Cert_Temp").Activate
    Range("F11").Select
    ActiveSheet.Paste
    
'move Next Due data
    Worksheets("Source").Range("C2:C" & LR).Copy
    Worksheets("Cert_Temp").Activate
    Range("I11").Select
    ActiveSheet.Paste
'move Tech data
    Worksheets("Source").Range("D2:D" & LR).Copy
    
    Worksheets("Cert_Temp").Activate
    Range("O11").Select
    ActiveSheet.Paste
End Sub

 

[Meller]

Thank you... I'll play around with it and see what I can figure out. I'm sure I'll be posting more on this thread as time goes on.

 

[Meller]

So I've done some editting to the code, added in a few more and what not to make up for the actual location of spots on the Cert_Temp and the Master Cal sheet.

How can I set it up so that after it does one line, it saves that Cert_Temp and puts the next line on a fresh Cert_Temp (same template layout... just without the data from the previous line)?