vba - copy data from one sheet to another

[uk747]

currently have book1 and book2.

Need to transfer data from book1 sheet1 cells a2:a7 to book2 sheet1, in the next available row then close and save changes in book 2 and go back to book1, currently have code below which worked before when I had a MAcro button on the sheet.

Now got a button in a userform, do I need to do anything different

NewName = ActiveWorkbook.Name
    
    Workbooks.Open "C\Book2.xls", , , , "password"
    
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "Book2 is being used. Please wait.... , , "Save Record"
        ActiveWindow.Close
        Exit Sub
              Else
                Workbooks(NewName).Activate
                Sheets("Sheet1").Activate
                Range("a2:a7").Copy
                Workbooks("Book2.xls").Activate
                Sheets("Sheet1").Activate
                Range("A3").Activate
                RowNo = 0
                
Start:
        If IsEmpty(ActiveCell.Offset(RowNo, 0)) Then
            ActiveCell.Offset(RowNo, 0).PasteSpecial xlPasteValues
            ActiveCell.Offset(RowNo, 2).Value = RowNo + 1
        Else
            RowNo = RowNo + 1
            GoTo Start
        End If
        
        Workbooks("Book2.xls").Close Savechanges:=True
        Workbooks(NewName).Activate
        CutCopyMode = False
        ThisWorkbook.Activate

 

[jbeaucaire]

This is how I would do that... no need to "activate" and "select"... bad technique derived from recorded code... be sure to edit that out and send your commands directly to the target sheets.

Option Explicit

Sub CopySpecial()
Dim wbDEST As Workbook

    Set wbDEST = Workbooks.Open("C\Book2.xls", , , , "password")
    
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "Book2 is being used. Please wait....", , "Save Record"
        wbDEST.Close False
        Exit Sub
    Else
        ThisWorkbook.Sheets("Sheet1").Range("a2:a7").Copy
        wbDEST.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        wbDEST.Close True
    End If

End Sub

 

[uk747]

Thanks Jerry

works great

 

[REUBEN]

This is how I would do that... no need to "activate" and "select"... bad technique derived from recorded code... be sure to edit that out and send your commands directly to the target sheets.

Option Explicit

Sub CopySpecial()
Dim wbDEST As Workbook

    Set wbDEST = Workbooks.Open("C\Book2.xls", , , , "password")
    
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "Book2 is being used. Please wait....", , "Save Record"
        wbDEST.Close False
        Exit Sub
    Else
        ThisWorkbook.Sheets("Sheet1").Range("a2:a7").Copy
        wbDEST.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        wbDEST.Close True
    End If

End Sub

Hi There,

I have a smilar need too but have more steps to be performed by the macro:

1) The data to be copied will of course be a range but this range could have at times one two or more rows of data to be copied. Would a input box be a good option to select the data in this case? If so could you please help me with the code.

2) The second excel sheet (shared with other users to edit at the same time) is on a shared drive in on a internal server. That must be opened and the data from first sheet be copied and saved (with values only -of course).

3) After the data from one sheet is copied on to the other sheet, I want the copied range in the first sheet to be highlighted in green, indicating that data has been copied to the 2nd sheet.

I'm quite a newbie with the macros so cannot figure this out myself.

Thank you.
Reuben

 

[pgspringer]

Just want to say thank you, I used your code to do this:

Sub CopySpecial()
Application.ScreenUpdating = False
Dim wbDEST As Workbook


    Set wbDEST = Workbooks.Open("C:\Users\philip\Desktop\WORKING Gulf Shores\Logs\WeeklyRatioLog.xlsm")
    
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "Book2 is being used. Please wait....", , "Save Record"
        wbDEST.Close False
        Exit Sub
    Else
        ThisWorkbook.Sheets("What_if_Sheet_GS").Range("Ratios").Copy
        wbDEST.Sheets("Ratios").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True
        ThisWorkbook.Sheets("What_if_Sheet_GS").Range("Ending").Copy
        wbDEST.Sheets("Endings").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True
        ThisWorkbook.Sheets("What_if_Sheet_GS").Range("Usage").Copy
        wbDEST.Sheets("Usages").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True
        ThisWorkbook.Sheets("What_if_Sheet_GS").Range("Purchases").Copy
        wbDEST.Sheets("Purchases").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True
        ThisWorkbook.Sheets("What_if_Sheet_GS").Range("Turn").Copy
        wbDEST.Sheets("Percent Turns").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Transpose:=True
        wbDEST.Close True
    End If
    
    Application.ScreenUpdating = True


    MsgBox "Stats have been Logged.", vbOKOnly


End Sub

you would think our gazillion dollar software program would do that but sadly no...

Anyway thank you very much!
Mine would have been super long and drawn out with needless steps. I appreciate that.