Macro to copy and paste range of rows based on a row range in a fixed cells. Create and save a excel workbook in a predefined folder.

[nbuddhi]

Dear Team,

Please refer the attached origin & destination files for further clarity. There are multiple data ranges, which include testing results. When click particular "Add a Sample" button set of Rows to be insert add additional sample test results: For fabric count test; Row 111:112 to be copied and insert after Row 112 / Fabric Weight Test; only single Row 119 to be copied and insert after row 119 and for Perspiration test: Row 125:132 to be copied and insert under row 132 as per current status, but above said rows are variables when add more rows.

I'll be able to get final copy row range as content in cell J105, K105 & L105 and shift cell as M105, N105 & O105, kidly help to create macro to assing with buttons next to particular test to copy the above set rows and insert at predefined insert cells.

Finlay need to open a excel workbook and name as cell A107, copy A:H range & paste with all the format including pictures and header/footer,but without formula and save in folder E:\Macro. This origin sheet will be password protected and only limited cell are allowed to edit. Password is $LkJO@20.

Thank you.

 

[vletm]

nbuddhi
You have written to Ask an Excel Question Your thread,
but You would like to get a macro ... then this thread belongs to VBA Macros.
Please, reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

In this time ... this thread has moved to needed Forum.

 

[nbuddhi]

Hi Vletm,

Noted and I'll make sure to adhere with site rules. Thanks.

 

[vletm]

nbuddhi
Notes:
1) You have used 'somewhere' ActiveX-components - those do not work with me - means Read-Only -file.
2) (CFAI20)240... -file's picture; seems that someone has ... with its scales - do not look normal.

3) Where is/are particular "Add a Sample" button?
4) ... which file do You refer with Your writing?
5) Have You tried to record Your needed macro?

 

[nbuddhi]

Dear Vletm,

1) Source file is "MAP Test Excel Format" and destination destination file is (CFAI20)240-11511-SN. I have already unprotected the source file.
2) I have corrected the image
3) Attached a screen shot called "SS" displaying locations of buttons for your easy reference.
4) MAP Test Excel Format
5) I tried to record a macro to add samples as below, but there is an error as attached screen shot called "Error"

I didn't try with second part; saving of excel file as it's complicate due to password protected sheet and need to keep header and footer as well.

Thank you.

>>> use code - tags <<<

Sub Count()
    Range("J103").Select
    Selection.Copy
    Application.Goto Reference:="R112C1"
    Rows("112:113").Select
    Range("C112").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=-12
    Rows("1:1").Select
    ActiveSheet.Paste
    Sheets("Test Report").Select
    Range("M103").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.Goto Reference:="R114C1"
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Test Report").Select
    Rows("114:114").Select
    Selection.Insert Shift:=xlDown
    Sheets("Test Methods").Select
    Range("A3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A3").Select
    ActiveSheet.Paste
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF('Test Report'!R[-2]C="""","""",'Test Report'!R[-2]C)"
    Range("A4").Select
    Range("Table4[A1]").FormulaR1C1 = _
        "=IF('Test Report'!R[-2]C="""","""",'Test Report'!R[-2]C)"
    Sheets("Sheet2").Select
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.UnMerge
    Selection.ClearContents
    Sheets("Test Report").Select
End Sub

 

[vletm]

nbuddhi
1) Read-Only is different than Protected. ... I wrote about [some] ActiveX-component - which are those?
3) As You have written "Add a Sample" button ... then ... there would be "Add a Sample" -button. ... You have marked two buttons - yes.
5) After recording ... those would need to 'clean'.
I would try to check later ... if I could get a clear idea ... what do there really need to do?

 

[nbuddhi]

Dear vletm

1) There are 5 active-x buttons as per attached screen shot.
3) Individual buttons are allocated for each test as Count test "Add a Count Sample", button, Weight test "Add a Weight Sample" Button and Perspiration test "Add a Perspiration Sample" Button.
4) Can you advise me how I can complete the below task, based on your advise I'll try to recreate the macro?

a) Need to copy row 112 & 113 ( Since these rows always changing with adding extra rows or removing them; Row number will be counted and located in a fix cell example; J103) above copied rows will be paste at row 114 (This also will be counted and will be located in fix cell M103). I I'm not clear how to give above said row reference in a macro.

Thank you.