CODEWITHSUNDEEP
vbaexcel
otus
otus

Reputation: 385

Write from a worksheet to another using a macro added programmatically

I actually create worksheets programmatically and add button with a macro associated to these worksheets programmatically. What I want to do is that when I click on the button the content of the worksheet containing the button is copied to another worksheet.

There are actually two things that confuse me : Firstly I don't understand if the macro I associate to the button is located (I mean its code is located) in the file creating the worksheet or in the created worksheet itself.

Here is the code I create to add a button with an associated macro : 

 With newWorkBook.Worksheets(1).Buttons
        .Add 350, 75, 173.25, 41.25
        .OnAction = "'" & ThisWorkbook.FullName & "'!export_Click"
        .Caption = "Exporter la fiche"
      End With

      newWorkBook.Worksheets("Feuil1").Name = "Valeurs"

The checkPVC_Click Sub is in a module located in the Excel file used to generate the worksheets.

Secondly, within the macro that is supposed to copy the content of a worksheet to another, I don't know how to refer differently to the two worksheets (source and target) in the code.

In the code below :

Dim newWorkBook As Workbook
Dim createdSheetColumnsTab(100) As String
Dim col As Integer
col = Cells(1, 8).Value

Set newWorkBook1 = Workbooks.Add

newWorkBook1.Worksheets("Feuil1").Cells(1, 1).Value = "Stat"

newWorkBook1.Worksheets("Feuil1").Cells(2, 1) = ActiveWorkbook.Worksheets("Valeurs").Cells(12, 1)

Here in the line col = Cells(1, 8).Value I access to the content of the worksheet which content I want to copy, and in the line newWorkBook1.Worksheets("Feuil1").Cells(1, 1).Value = "Stat" I access to the content of the "target" worksheet and I don't know how to refer to the content of the first worksheet in the following of the code in order to copy the content.

I hope I was clear, and I can add more precisions if necessary, sorry I don't master English so it's hard for me to explain the issue.

Upvotes: 2

Views: 480

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

You can leave the Sub checkPVC_Click in the original workbook. Just ensure that you give full path and name of the file which has that macro. For example. Please ensure that the file from where you are running this macro is saved at least once.

Sub Sample()
    Dim NewWorkbook As Workbook

    Set NewWorkbook = Workbooks.Add

    With NewWorkbook.Worksheets(1).Buttons
        .Add 350, 15, 173.25, 41.25
        .OnAction = "'" & ThisWorkbook.FullName & "'!checkPVC_Click"
    End With
End Sub

Sub checkPVC_Click()
    MsgBox "a"
End Sub

Regarding your Second question, you need to fully qualify the Cells object so that it know which cells are you referring to

ThisWorkbook will refer to the cell from the workbook which hosts the code.

Activeworkbook will refer to the cell from the workbook which is currently active.

Edit: Followup from comments. Is this what you are trying?

Sub Sample()
    Dim newWorkBook As Workbook
    Dim ws As Worksheet

    Set newWorkBook = Workbooks.Add
    Set ws = newWorkBook.Sheets(1)
    ws.Name = "Valeurs"

    With ws.Buttons
        .Add 350, 15, 173.25, 41.25
        .OnAction = "'" & ThisWorkbook.FullName & "'!checkPVC_Click"
    End With
End Sub

Sub checkPVC_Click()
    Dim OldWorkbook As Workbook, newWorkBook As Workbook
    Dim createdSheetColumnsTab(100) As String

    Set OldWorkbook = ActiveWorkbook

    Set newWorkBook1 = Workbooks.Add

    newWorkBook1.Worksheets("Feuil1").Cells(1, 1).Value = "Stat"

    newWorkBook1.Worksheets("Feuil1").Cells(2, 1) = OldWorkbook.Worksheets("Valeurs").Cells(12, 1)
End Sub

Upvotes: 1

KKowalczyk
KKowalczyk

Reputation: 433

To access the content of the first worksheet of workbook containing macro you need to use:

Thisworkbook.sheets(1).range("a1:a100") ' range as an example you can input anything

Hope it helped.

Upvotes: 0

Related Questions