VBA macro Copy/Paste/Save/Repeat

[Meller]

Hi,

So I'm using a code (I'll post below) to copy line item info from one excel sheet to another excel sheet. (Pulling information from a list of some few thousand lines of data with about 9 columns over all.

The code I'll show you, pulls the data from my "Source" sheet just fine and pastes it in my other template perfectly.

So my question is, how can I get it to auto save my Template (the sheet where data is being pasted) after each successful run of my macro and repeat with the next line down until it reaches either the end of the line items or I tell it what line is the last? *Edited* Guess I should also ask about the saving - naming of the .xls. Is it possible to have it save each sheet to an whatever data is in one of the assigned blocks on my template sheet (I'm copying over serial numbers for items onto a template and I'd just assign it to save as that block on the template...)?

Also, is there a way to copy my data from my "Source" excel sheet and paste it in my template without it also pasting the format/borders/alignment of the cell it copied? I tried playing around with .pastespecial xlpastevalue but I could get it to work for some reason.

Thanks

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
    LR = ActiveCell.Row
    
'move Standards data
    Worksheets("Source").Range("J7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("B41").Select
    ActiveSheet.Paste
'move Location data
    Worksheets("Source").Range("H7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("H10").Select
    ActiveSheet.Paste
'move contract # data
    Worksheets("Source").Range("J3").Copy
    
    Worksheets("Cert_Temp").Activate
    Range("D7").Select
    ActiveSheet.Paste
'move Ship Name data
    Worksheets("Source").Range("J4").Copy
    
    Worksheets("Cert_Temp").Activate
    Range("H7").Select
    ActiveSheet.Paste
'move serial# data
    Worksheets("Source").Range("A7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("D8").Select
    ActiveSheet.Paste
'move Range data
    Worksheets("Source").Range("B7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("D9").Select
    ActiveSheet.Paste
'move Manufacture data
    Worksheets("Source").Range("C7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("D10").Select
    ActiveSheet.Paste
'move Date Cal data
    Worksheets("Source").Range("D7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("H8").Select
    ActiveSheet.Paste
'move Next Due data
    Worksheets("Source").Range("E7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("H9").Select
    ActiveSheet.Paste
'move Tech data
    Worksheets("Source").Range("I7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("H11").Select
    ActiveSheet.Paste
'move Nomenclature data
    Worksheets("Source").Range("F7").Copy
    
    Worksheets("Cert_Temp").Activate
    'Where on Cert_Temp the data will be put
    Range("D11").Select
    ActiveSheet.Paste
End Sub

 

[Meller]

Turns out copying without taking the cell's format isn't really all that important since it remains uniform through out the entire "source" sheet.

Just need a way to save it and start with the next until it's copied each line on it's only template and saved them.

Any ideas?

 

[Meller]

Yes, I realize I'm talking to myself... but I'm basically just documenting what I have for whenever someone else pops in here.

I got the SaveAs function working with my dictated file name from a cell using:

ThisFile = Sheets("Cert_Temp").Range("D8").Value
ActiveWorkbook.SaveAs Filename:="C:\Test\" & ThisFile, FileFormat:=xlNormal

That code is (for obvious reasons) sitting at the very bottom of my macro.

Only I have left to add is a loop function that after it saves, it'll re-start the entire macro but on the next line down.

Any Advice?