• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Member
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.
 

Attachments

  • (CFAI20)240-11511-SN.xlsx
    121.6 KB · Views: 2
  • MAP Test Excel Format.xlsx
    552.1 KB · Views: 2
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
In this time ... this thread has moved to needed Forum.
 
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.
Screenshot 2020-09-13 at 10.45.21.png
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?
 
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 <<<
Code:
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
 

Attachments

  • (CFAI20)240-11511-SN.xlsx
    122.1 KB · Views: 3
  • MAP Test Excel Format.xlsx
    539.3 KB · Views: 2
  • SS.jpg
    SS.jpg
    132.5 KB · Views: 3
  • Error.jpg
    Error.jpg
    143.1 KB · Views: 3
Last edited by a moderator:
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?
 
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.
 

Attachments

  • ActiveX.jpg
    ActiveX.jpg
    163.4 KB · Views: 3
Back
Top