I came across your article "copy multiple cells (not in range) from one sheet to another sheet". I copied the VBA Macro and pasted it, referenced the cells in a hidden row and assigned a button to execute. it works fantastic from one sheet to another sheet in the same workbook. My problem is I need to copy from one workbook sheet1 (invoice) to another workbook sheet1 (accounts receivable with aging). Can the existing VBA code be adjusted to do that?

These are the changes I did to the VBA Macro to suit my ranges

wks1.Cells(use_row,1) = wks.cells (50,1)
wks1.Cells(use_row,2) = wks.cells (50,2)
wks1.Cells(use_row,3) = wks.cells (50,3)
wks1.Cells(use_row,4) = wks.cells (50,4)
wks1.Cells(use_row,5) = wks.cells (50,5)
wks1.Cells(use_row,6) = wks.cells (50,6)

Certainly. All that needs to be done is create a reference (variable in the sub) to the other workbook, and choose the receiving sheet on that end.

Which is achieved here by replacing the first few rows, upon which the rest of the code relies (with your own modifications ensuing):

Dim wkb As Workbook,wkb2 as workbook
Dim wks as worksheet, wks1 As Worksheet
Set wkb = ThisWorkbook ' Or workbooks("SourceWorkbook.xlsm") if it's saved.
set wkb2 = Workbooks("OtherWorkbook.xlsx")
Set wks = wkb.Sheets("Sheet1") ' Name references are a little clearer.
Set wks1 = wkb2.Sheets("Sheet1")

You could also add another "set wkb2 = nothing" at the end, but that's more of a safety precaution than actually doing much.

At any rate, I'd suggest you read up a little on basic vba if you find yourself in need of relatively complex functions. If not for writing whole routines, then at least making passable modifications yourself. For example, that bit you wrote could be done in a loop instead of copypasta, since the columns are sequential.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.