Copy values

[dwarek]

Hello i have assigned a task like copy the entire values of one excel to another excel file when clicking a macro button, is there any kind of vba code for that ???
thanks in advance

 

[dchaney]

Hello i have assigned a task like copy the entire values of one excel to another excel file when clicking a macro button, is there any kind of vba code for that ???
thanks in advance

Yes, there is

Are you createing a new workbook to paste the Values in? or is one already existant that you need to use?

 

[Muhammad_Usman]

Edit the workbook name as you want.!

Sub Macro2()
'
' Macro2 Macro
'


'
Cells.Select
Selection.Copy
Windows("workbook1.xlsx").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("workbook2.xlsx").Activate
End Sub

 

[dwarek]

i have excel file where i will be working for whole day and at the end of the i need copy all the data into another worksheet we call as master worksheet and many people will copying their data into that master worksheet as it is accessible to everyone so i need program like it should find the next empty row and copy all data from my worksheet to master worksheet

 

[dchaney]

Couple Questions...

What Columns do you use in your workbook? A -????

What is the sheet name on the Master File you have to paste your data into?

 

[dchaney]

Actually, my first question is irrelavant with the following... Make sure you test this on a COPY of the data... not the original Masterfile also, if the master file has multiple sheets, and it opens to the wrong one it will paste your data to the one it opens too... so if you know the sheet name within the master file, let me know and I can tell you what to change...

Sub CopymyWs()

Dim myWsLastColLet As String
Dim myWb As Workbook, masterWb As Workbook
Dim myWs As Worksheet, masterWs As Worksheet
Dim myWsLastRow As Long, masterWsLastRow As Long, myWsLastCol As Long

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Set myWb = ActiveWorkbook 'set my file as myWb
Set myWs = myWb.ActiveSheet 'set current active worksheet in myWb as myWs

    myWsLastRow = myWs.Range("A" & Rows.Count).End(xlUp).Row 'ID the last used Row on myWs
    myWsLastCol = myWs.Cells(1, Columns.Count).End(xlToLeft).Column 'ID the last used column on myWs
    
    If myWsLastCol > 26 Then
        myWsLastColLet = Chr(Int((myWsLastCol - 1) / 26) + 64) & Chr(((myWsLastCol - 1) Mod 26) + 65) 'change column number to letter if column higher then 26
    Else
        myWsLastColLet = Chr(myWsLastCol + 64) 'change column number to letter if column 1 - 26
    End If
    
    masterFile = Application.GetOpenFilename("Excel File (*.xls*),") 'Identify the file name and type of file to open
    
    If masterFile = False Then Exit Sub 'If no file is selected end the code

    Workbooks.Open masterFile 'Open master file
    
Set masterWb = ActiveWorkbook 'Set the master file as masterWb
Set masterWs = masterWb.ActiveSheet 'Set the sheet the master file opens to as the masterWs (this is where the data will be saved)

    masterWsLastRow = masterWs.Range("A" & Rows.Count).End(xlUp).Row + 1 'ID the last used row of the master file, add 1 to it

    myWs.Range("A2:" & myWsLastColLet & myWsLastRow).Copy 'Copy data from my file
    masterWs.Range("A" & masterWsLastRow).PasteSpecial 'Paste data to master file

masterWb.Close SaveChanges:=True 'Save and Close the master file

Set masterWs = Nothing
Set masterWb = Nothing
Set myWs = Nothing
Set myWb = Nothing

With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

 

[dwarek]

the master excel file name is dailymis.xlsx and the file am working will be mis.xlsm which is macro enabled file

 

[dwarek]

but anyways dchaney this works fine and thanks a lot if i have a doubt i will ask you

 

[dchaney]

My pleasure