How to selecting a range with data on excel with vba

Question

How can i modify the code below to select data from any worksheets and copy they to another worksheet for example select and copy data from Worksheets("uno") and paste they to Worksheets("duo"). Because the code below selects data only on activesheet

Set tbl = ActiveCell.CurrentRegion 
tbl.Resize(tbl.Rows.Count, tbl.Columns.Count).Select

I have a code to copy data from any sheet to another for example

Worksheets("uno").Range("A5:T5,A7:T56,W5,Y5,W7:W56,Y7:Y56").Copy _
Worksheets("duo").Range("B4")

But i want to copy a range with data and ignore blank cells because the range A5:T5 it doesn't have always all cells with data concretely the last cells of this range, two or three of those, and also the same on range A7:T56.

My problem is how to select a range with data and ignore the blank cells inside the range A7:T56 concretely the last rows and the last columns which haves blank cells

Answer 1

Well, for the first part, where "the code selects data only on the activesheet", you just need to activate the correct sheet (for example: "Worksheets("uno").Activate") before executing "Set tbl = ActiveCell.CurrentRegion".

Answer 2

I am not really sure if I understand you correctly, but these are my thoughts:

If you don't want to activate worksheet "uno" you need to create a reference to that worksheet to have a direct access to it:

Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set wkb = Excel.Application.Workbooks("<name of your workbook>")
Set wks = wkb.Worksheets("uno")

If you now use the following code:

wks.Range("<your range>").Copy

you have just copied your selected cells, now you can paste it wherever you want.

As for the part with avoiding empty cells:

Generally speaking, you need to create a method of checking whether relevant cells are empty or not before you add them to your range.

Personally, I would avoid trying to copy the whole range as such. Instead I would: 1) loop through all relevant cells in your range one by one 2) for each cell check if it's empty 3) if empty, go to next cell 4) if not empty, copy that cell and paste to the target worksheet 5) jump to next relevant cell 6) when you reach the cell which is just after your last cell, quit looping

I would use the above defined wks object. Note that a Range object can be treated as a collection of strings, so you can iterate using For... Next loop (For Each loop does not guarantee the index order). Something like this should do:

Dim rng As Range
Set rng = wks.Range("<your range>")

Dim numOfItems As Integer, itm, i As Integer
numOfItems = rng.Count

For i = 1 To numOfItems

    itm = rng.item(i)

    If itm <> "" Then

        'set value of the corresponding cell in your target worksheet to itm
        '<relevant cell>.Value = itm
    Else
        'do nothing
    End If
Next i

I hope it's at least a little bit helpful.