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

 

[Glasgowsmile]

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!

It looks like this would put the data vertically, is there a solution that puts the data horizontally? Would it be nextColumn instead?

 

[Domenic]

Actually, if you try it you'll see that it does put the data horizontally, in Columns C, D, and E. Of course, you can change the columns as desired.

 

[Glasgowsmile]

Actually, if you try it you'll see that it does put the data horizontally, in Columns C, D, and E. Of course, you can change the columns as desired.

Ah I see what you mean. I was reading it incorrectly. Thanks again for the help on this.

 

[Glasgowsmile]

Actually, if you try it you'll see that it does put the data horizontally, in Columns C, D, and E. Of course, you can change the columns as desired.

Last question (hopefully), based on the new solution... could I just merge all of this into a single With End statement like the nextRow example? I'm thinking just a .Cells for each one?

         With Sheet4
            .Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Glance").Range("B6").Value
         End With
        
        With Sheet4
           nextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
           .Cells(nextRow, "C").Value = Sheets("Glance").Range("Z10").Value
           .Cells(nextRow, "D").Value = Sheets("Glance").Range("Z14").Value
           .Cells(nextRow, "E").Value = Sheets("Glance").Range("Z18").Value
           .Cells(nextRow, "F").Value = Sheets("Glance").Range("Z20").Value
        End With
         
         With Sheet4
            .Range("J" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Glance").Range("T14").Value
         End With
         
         With Sheet4
            .Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Glance").Range("W14").Value
         End With
         
         With Sheet4
            .Range("G" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Glance").Range("AF54").Value
         End With

 

[Domenic]

Sure, so we use Column C to determine the next available row, and then enter the values accordingly...

        With Sheet4
           nextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
           .Cells(nextRow, "C").Value = Sheets("Glance").Range("Z10").Value
           .Cells(nextRow, "D").Value = Sheets("Glance").Range("Z14").Value
           .Cells(nextRow, "E").Value = Sheets("Glance").Range("Z18").Value
           .Cells(nextRow, "F").Value = Sheets("Glance").Range("Z20").Value
           .Cells(nextRow, "G").Value = Sheets("Glance").Range("AF54").Value
           .Cells(nextRow, "J").Value = Sheets("Glance").Range("T14").Value
           .Cells(nextRow, "K").Value = Sheets("Glance").Range("W14").Value
           .Cells(nextRow, "M").Value = Sheets("Glance").Range("B6").Value
        End With