Copy cells from another workbook

[SQUIDD]

Hi All

Im sure i have done this before, but i must be missing something, probably time for a break.

Im sure i can copy a rance of cells from one workbook to another? Without using range("a2:a50") as example

Sub DB()
    Set RNG = Workbooks(MYBOOK).Sheets("APP") 'MYBOOK IS A PUBLIC VAIRABLE ALREADY DEFINED AND IS GOOD
    WORD = RNG.DOG_SELECTOR.Value 'THERE IS A VALUE HERE, SO THIS IS FINE
    WORD = Mid(WORD, 10, 100)
    A = 1
    LR=50
    Set RNGC = Workbooks(CURRENT_DAY & ".xlsx").Sheets(WORD) 'CURRENT_DAY IS A PUBLIC VAIRABLE ALREADY DEFINED AND IS GOOD
    RNGC.Range(Cells(2, A), Cells(LR, A)).Copy 'BUGS OUT HERE, WONT COPY,


End Sub

 

[DanteAmor]

I don't understand the use of variables very well, perhaps because we don't use the same nomenclature, for example, we regularly use RNG for a range and SH or WS for a sheet. But they are each person's way of programming.


Your code is missing a reference to the sheet object in each cell object:

    Set RNGC = Workbooks(CURRENT_DAY & ".xlsx").Sheets(WORD) 'CURRENT_DAY IS A PUBLIC VAIRABLE ALREADY DEFINED AND IS GOOD
    RNGC.Range(RNGC.Cells(2, A), RNGC.Cells(LR, A)).Copy 'BUGS OUT HERE, WONT COPY,

 

[MARK858]

Just to say you can omit the 1st RNGC

Range(RNGC.Cells(2, A), RNGC.Cells(LR, A)).Copy

 

[SQUIDD]

I don't understand the use of variables very well, perhaps because we don't use the same nomenclature, for example, we regularly use RNG for a range and SH or WS for a sheet. But they are each person's way of programming.


Your code is missing a reference to the sheet object in each cell object:

    Set RNGC = Workbooks(CURRENT_DAY & ".xlsx").Sheets(WORD) 'CURRENT_DAY IS A PUBLIC VAIRABLE ALREADY DEFINED AND IS GOOD
    RNGC.Range(RNGC.Cells(2, A), RNGC.Cells(LR, A)).Copy 'BUGS OUT HERE, WONT COPY,

Worked spot on, now i look at it, its actually obvious.

Yeah, my code is on my little home projects and on my business systems. only i understand it

Thanks

Dave

 

[SQUIDD]

Just to say you can omit the 1st RNGC

Range(RNGC.Cells(2, A), RNGC.Cells(LR, A)).Copy

Hi Mark

Thanks

But actually it only worked with the RNGC at the beginning as well as per DanteAmor suggestion.

But thankyou both

Dave

 

[MARK858]

But actually it only worked with the RNGC at the beginning as well as per DanteAmor

Hmm, works for me fine copying from a different workbook without the leading RNGC with the code below with a workbook called myBook copying into the workbook that contains the code.

Sub test()
Dim A As Long, LR As Long, RNGC As Worksheet
    A = 1
    LR = 50
    Set RNGC = Workbooks("myBook" & ".xlsx").Sheets("Sheet1")
    Range(RNGC.Cells(2, A), RNGC.Cells(LR, A)).Copy
Sheets("Sheet2").Range("B1").PasteSpecial

Application.CutCopyMode = False
End Sub