CODEWITHSUNDEEP
excelvba
bluesky
bluesky

Reputation: 17

Copy from one workbook to another workbook

Sub create_files_add_data(get_input)

lastrow1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row 'This is to get a count of rows in the source file.

' Sheet2.Range("A1:K1").Copy ' This copy of the header row works. But the copy of the data rows in the for loop doesn't work!!!

Dim target_workbook As Workbook
Dim target_worksheet As Worksheet
Set target_workbook = Workbooks.Add
Set target_worksheet = target_workbook.Worksheets("Sheet1")

       For r = 2 To lastrow1 ' This is where we use the row count to loop through all the rows to check the deptid and copy the rows
            
            If Worksheets("Sheet1").Range("B" & r).Value = get_input Then
            
               Worksheets("Sheet1").Rows(r).Copy
                 
                       ' lastrow2 = target_worksheet("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
                                  
                       ' target_worksheet("Sheet1").Range("A" & lastrow2 + 1).Select
              
               target_worksheet.PasteSpecial xlPasteValues ' pasting here into the target workbook (but doesn't work!!!)
               
               'ActiveCell.PasteSpecial xlPasteValues
                       
            End If
            
        Next r
        
 With target_workbook
    .SaveAs filename:="C:\Users\" & get_input & ".xlsx"
     target_workbook.Close
 End With

End Sub

Upvotes: 0

Views: 86

Answers (1)

FunThomas
FunThomas

Reputation: 29586

If the workbook is already open, you access the workbook from the workbooks-collection by name, but without path.

target_file = get_input & ".xlsx"
Set target_workbook = Workbooks(target_file)

If you want to open the workbook, you need to use the Open-method and specify the full path:

target_file = "C:\Users\" & bu_to_process & "\" & get_input & ".xlsx"
Set target_workbook = Workbooks.Open(target_file)

To create a brand new workbook, use the Add-method to create it and the SaveAs-method to save:

target_file = "C:\Users\" & bu_to_process & "\" & get_input & ".xlsx"
Set target_workbook = Workbooks.Add
' (do your work here)
' ...
target_workbook.SaveAs target_workbook, xlOpenXMLWorkbook

You can save it as CSV also, however, the file extension should reflect the file type:

target_file = "C:\Users\" & bu_to_process & "\" & get_input & ".csv"
target_workbook.SaveAs target_workbook, xlCSV

Upvotes: 1

Related Questions