Copy&paste values only from one worksheet to another

[steve2115]

Looking for some help with a piece of code I am trying to modify.. The code below copies & pastes the values on a separate worksheet ("Report") within the same workbook. What I would like to do is copy & paste the values onto a completely separate workbook.


Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Report")
'find first row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Range("A" & irow) = Requisition_No.Value
.Range("B" & irow) = Suppliers.Value
.Range("C" & irow) = IndCode.Value
.Range("D" & irow) = UserName.Value
.Range("E" & irow) = DateBox.Value
.Range("F" & irow) = Justification.Value

 

[revcanon]

Are you trying to make a new workbook for your "Report" sheet, or do you have an existing template you want to open and use?

 

[revcanon]

This one assumes you have another workbook with a "Report" tab you want to move data to.

If this isn't exactly what you need, maybe you can augment it for your use:

Dim irow As Long
Dim NewWS As Worksheet
Dim NewWB As Workbook
Dim FileToOpen As String
        'Use to have user choose file:
        FileToOpen = Application.GetOpenFilename(Title:="Select the report", _
        FileFilter:="ERREPAY Inventory Report *.xl* (*.xl*),")
        'Or call out the specific file like this:
        'FileToOpen = "Y:\our\Path\Here\AndFilename.xl*"
' If user does not choose a file, the process will stop.
            If FileToOpen = False Then
                MsgBox "No file selected. Process will end."
            End
            Else
'If user chooses a file, the file will open.
                Application.EnableEvents = False
'Open the chosen file
                Set NewWB = Workbooks.Open(FileToOpen)
                Application.EnableEvents = True
            End If
        Set NewWS = NewWB.Sheets("Report")
'find first row in database
irow = NewWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
NewWS.Range("A" & irow).Value = Requisition_No.Value ' I assume your values are predetermined in your code
NewWS.Range("B" & irow).Value = Suppliers.Value
NewWS.Range("C" & irow).Value = IndCode.Value
NewWS.Range("D" & irow).Value = UserName.Value
NewWS.Range("E" & irow).Value = DateBox.Value
NewWS.Range("F" & irow).Value = Justification.Value

 

[steve2115]

Thanks for the reply... I have a workbook named "Report" with Sheet1 named "Report". Looking at your code above it looks like you are having the user select the file. What I am trying to accomplish is have the data automatically paste the values into the workbook. The code is for a userform, once the user selects the Submit button I would like this piece of the code to copy&paste the data onto workbook "Report" sheet "Report"

 

[revcanon]

You use add the path/file name for the FileToOpen variable

Dim irow As Long
Dim NewWS As Worksheet
Dim NewWB As Workbook
Dim FileToOpen As String
Application.ScreenUpdating = False
        FileToOpen = "Y:\our\Path\Here\AndFilename.xl*" 'Add path and file name of your workbook here
'Open the chosen file
                Set NewWB = Workbooks.Open(FileToOpen)
        Set NewWS = NewWB.Sheets("Report")
'find first row in database

irow = NewWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
NewWS.Range("A" & irow).Value = Requisition_No.Value ' I assume your values are predetermined in your code
NewWS.Range("B" & irow).Value = Suppliers.Value
NewWS.Range("C" & irow).Value = IndCode.Value
NewWS.Range("D" & irow).Value = UserName.Value
NewWS.Range("E" & irow).Value = DateBox.Value
NewWS.Range("F" & irow).Value = Justification.Value

 

[steve2115]

Just tried this out... I am getting a VB error message "Compile error in hidden module". I tested it with the Report file both opened and closed. Same error.????

 

[revcanon]

Unfortunately, this means you may have code in a protected module that isn't playing well.

Something to look at:

http://www.mrexcel.com/forum/excel-questions/566811-compile-error-hidden-module-module-1-a.html

 

[steve2115]

I got your code to work. There was a "End With" further down in my code that was causing the error. Removed it and your code worked. However, I do not want the "Report" workbook to actually Open. How would I go about changing the code so the "Report" wb stays closed but keeps the data passed to it? My issue is both of these files will be on a sharepoint site that multiple users will be accessing.
Thanks Again!