I am trying to copy all the information off of two different worksheets and apply them to a master worksheet

Question

This is the code I am trying to use and it isn't quite working. I am not sure what I did wrong since I am still learning VBA. Can anyone help me to get this to work?

Sub BringDataToMasterSheet()

    Dim WB As ThisWorkbook
    Dim x As WorkSheet
    Dim y As WorkSheet
    Dim z As WorkSheet

    '## Open Workbooks first:
    Set x = Worksheet.Open("Delinquent Promises Detail")
    Set y = Worksheet.Open("Delinquent Shipments Detail")
    Set z = Worksheet.Open("Master")



    'Now, copy what you want from x:
    x = Worksheet("Delinquent Promises Detail").Range("A2").lastrow.Copy
    z = Worksheet("Master").Range("A").lastrow.PasteSpecial



    y = Worksheet("Delinquent Shipments Detail").Range("A2").lastrow.Copy
    z = Worksheet("Master").Range("A").lastrow.PasteSpecial

End Sub

Try to declare the last 4 lines as `Range` somehow. If you paste the code, in the question, you would have more success. In general, take a look at the question here - http://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another — Vityata, Apr 19 '17 at 14:00
[Why accept an answer?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Accepting an answer helps other people with the same question. — M--, May 10 '17 at 22:16

score 1 · Answer 1 · edited Sep 21 '17 at 18:29

You cannot open worksheets. You open a workbook and it seems that these workbooks are in ThisWorkbook. You cannot define a worksheet to be a value (copied or paste). As you declare x, y, and z as Worksheet you need to go for defining ranges. However, as you just copy/paste them, there is no need for that declaration and you can directly do that. See below:

Sub BringDataToMasterSheet()

    Dim WB As ThisWorkbook
    Dim x As WorkSheet
    Dim y As WorkSheet
    Dim z As WorkSheet


    '## Set worksheets first: (You don't open worksheets)
    Set x = Worksheets("Delinquent Promises Detail")
    Set y = Worksheets("Delinquent Shipments Detail")
    Set z = Worksheets("Master")


    'Now, copy what you want:
    x.Range("A2").End(xlDown).Copy
    z.Range("A2").End(xlDown).Offset(1,0).PasteSpecial xlPasteAll

    y.Range("A2").End(xlDown).Copy
    z.Range("A2").End(xlDown).Offset(1,0).PasteSpecial xlPasteAll

End Sub

Here are some lines that you may use here depedn on what exactly you mean by opening a worksheet or copying a range while setting them to something:

This opens a workbook:

Set myWB = Workbooks.Open("Path/to/the/file.xlsx")

This set a worksheet within that workbook:

Set xyz = myWB.Worksheets("Sheet1")

This sets a range:

Dim myRange As Range
Set myRange = xyz.Range("A2")

This Copies that range and pastes it somewhere else in the same worksheet (except the borders of the original range):

myRange.Copy
xyz.Range("H50").PasteSpeciall xlPasteAllExceptBorders

I am trying to copy all the information off of two different worksheets and apply them to a master worksheet

1 Answers1