macro / vba assistance

[Rameses]

Dear all

Worksheet 1 is a data set

Worksheet 2 is a form template with cells to be filled from row 2 of worksheet 1

Once that is filled in I want to be able to save that and open a new worksheet with the form template to fill in row 3 of worksheet 1 (this can all be saved in the same workbook)

I need a macro but dont know where to start

any assistance gratefully received

rameses

 

[jbeaucaire]

The last two syntax shown copy entire cells, formatting and all. The macro has no choice, it's not optional, it has to copy it.

 

[Rameses]

Hi Jerry

Im not sure whether you received copy of spreadsheet but i have tried it yet it keeps running through but missing out cell colour

I will try again as I can see the logic!

Rameses

 

[Rameses]

apologies wrong spreadsheet sent


correct one to follow!

 

[Rameses]

Jerry

For some wierd reason I can now move the colours across!!!

thankyou for that i am really grateful....

should it also copy the numbers accross?

ie in a few of the cououred boxes i have a number but only the colour is moving across?


i am really grateful ( i dont understand what i did different first and seventh time of ding this!!!)

Rameses

 

[Rameses]

Further information

The 2nd Syntax is the one which works copying over the cell colour(although missing out the number)

The first syntax does not copy over cell colour

 

[Rameses]

Apologies Jerry

my error.......... the second syntax is perfection... it works perfectly with no problems

Jerry thank you so much ... this means I can n0w provide the students with their own individualised sheets to help monitor work thank you so much I am really really grateful

many thanks once again

Rameses

 

[jbeaucaire]

Glad it worked out. I'm worried about the emails that never arrived. I get files from people every day, but not yours.

 

[jbeaucaire]

Hi Jerry,

I would like to amend the following instructions to tell it to only select records from the Employees list that have a specific code in Column D

Set dSht = Sheets("Employees") 'sheet with data on it starting in row2
Set tSht = Sheets("TimeCard") 'sheet to copy and fill out

Got any suggestions? Thank you.

Assuming the value wanted was always the same ("cat", for example), you could hardcode that into the macro like so:

    For Rw = 2 To LastRw
        If dsht.Range("D" & Rw) = "cat" Then
            tSht.Copy After:=Worksheets(Worksheets.Count)   'copy the template
            With ActiveSheet                                'fill out the form
                'edit these rows to fill out your form, add more as needed
                .Name = dsht.Range("A" & Rw)
                .Range("C2") = dsht.Range("A" & Rw)
                .Range("F2") = dsht.Range("B" & Rw)
                .Range("C6") = dsht.Range("C" & Rw)
                .Range("C5") = dsht.Range("D" & Rw)
                .Range("B12:E12") = dsht.Range("F" & Rw, "I" & Rw)
                .Range("F12:I12") = dsht.Range("K" & Rw, "N" & Rw)
                .Range("J12:M12") = dsht.Range("P" & Rw, "S" & Rw)
                .Range("B16:E16") = dsht.Range("U" & Rw, "X" & Rw)
                .Range("F16") = dsht.Range("Z" & Rw)
                .Range("G16:I16") = dsht.Range("AA" & Rw, "AC" & Rw)
                .Range("J16:M16") = dsht.Range("AE" & Rw, "AH" & Rw)
                .Range("B20:E20") = dsht.Range("AJ" & Rw, "AM" & Rw)
                .Range("F20:I20") = dsht.Range("AO" & Rw, "AR" & Rw)
                .Range("J20:M20") = dsht.Range("AT" & Rw, "AW" & Rw)
            End With
            
            If MakeBooks Then       'if making separate workbooks from filled out form
                ActiveSheet.Move
                ActiveWorkbook.SaveAs SavePath & Range("B3").Value, xlNormal
                ActiveWorkbook.Close False
            End If
            Cnt = Cnt + 1
        End If
    Next Rw