Deselect Tabs after VBA

[Glasgowsmile]

Good day,

I've got the below VBA code which works but after it runs when I look at the tab it has the range use selected still. It's not a huge issue but I'm curious, how do I remove that selection on each tab?

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim crnt As Workbook
Dim source As Workbook

With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Files", "*.xlsm"
    .AllowMultiSelect = False
    .Show

If .SelectedItems.Count > 0 Then
    Workbooks.Open .SelectedItems(1)
    Set source = ActiveWorkbook
    
    Worksheets("Dtar").Range("A3:K365").Copy
    Sheet1.Range("A3").PasteSpecial xlPasteValues
    
    Worksheets("Mtar").Range("A3:A365").Copy
    Sheet2.Range("A3").PasteSpecial xlPasteValues

    Worksheets("Mtar").Range("C3:H365").Copy
    Sheet2.Range("C3").PasteSpecial xlPasteValues

source.Close savechanges:=False

End If
End With

Sheets("Import_old").Activate
Range("A1").Select
Application.CutCopyMode = False

 

[Domenic]

You can replace . . .

    Worksheets("Dtar").Range("A3:K365").Copy
    Sheet1.Range("A3").PasteSpecial xlPasteValues

with

    With Worksheets("Dtar").Range("A3:K365")
        Sheet1.Range("A3").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Same the with the other two.

Hope this helps!

 

[Glasgowsmile]

You can replace . . .

    Worksheets("Dtar").Range("A3:K365").Copy
    Sheet1.Range("A3").PasteSpecial xlPasteValues

with

    With Worksheets("Dtar").Range("A3:K365")
        Sheet1.Range("A3").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Same the with the other two.

Hope this helps!

My apologies, just getting back to this.

Not only did this work but wow, it is fast. I've never seen this approach but I think it will be my go-to moving forward.

 

[Domenic]

That's great, glad I could help. And thanks for the feedback.

Cheers!

 

[Glasgowsmile]

That's great, glad I could help. And thanks for the feedback.

Cheers!

Looking at this new approach, I'm wondering how to do offsets with it?

For example, I have this as the paste after copying data from another sheet.

Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

This is what I tried but it doesn't appear to work as expected though I get no errors.

With Sheets("Cover").Range("Q36")
                 Sheet3.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With

 

[Domenic]

What do you mean by "it doesn't appear to work as expected"? Can you please be specific?

 

[Glasgowsmile]

What do you mean by "it doesn't appear to work as expected"? Can you please be specific?

Well, the first few times I tried it, it didn't look like it was pulling the data correctly but it does appear to work. Are there any issues with that code above for the offset?

 

[Domenic]

The only issue I see is that you'll get an error if the active sheet is not a worksheet. You should qualify the Rows reference with the worksheet name . . .

Sheet3.Range("B" & Sheet3.Rows.Count). . .

However, since you are only interested in a single value, you can do it this way . . .

With Sheet3
    .Range("B" & .Rows.Count).End(xlUp).Offset(1).Value = Sheets("Cover").Range("Q36").Value
End With

 

[Glasgowsmile]

The only issue I see is that you'll get an error if the active sheet is not a worksheet. You should qualify the Rows reference with the worksheet name . . .

Sheet3.Range("B" & Sheet3.Rows.Count). . .

However, since you are only interested in a single value, you can do it this way . . .

With Sheet3
    .Range("B" & .Rows.Count).End(xlUp).Offset(1).Value = Sheets("Cover").Range("Q36").Value
End With

Hi again, you mentioned this works for a single value but what if I'm trying to get multiples, like below?

It pulls L11 but not Q11 or S11.

With Sheet5
            .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Glance").Range("L11, Q11, S11").Value
         End With

 

[Domenic]

Unfortunately, you won't be able to do it that way. Also, you should qualify your Rows reference. Otherwise, you'll get an error if the active sheet is not a worksheet. Try it like this . . .

Dim nextRow As Long

With Sheet5
   nextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
   .Cells(nextRow, "C").Value = Sheets("Glance").Range("L11").Value
   .Cells(nextRow, "D").Value = Sheets("Glance").Range("Q11").Value
   .Cells(nextRow, "E").Value = Sheets("Glance").Range("S11").Value
End With

Hope this helps!