Deselect Tabs after VBA

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
269
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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

Active Member
Joined
Apr 14, 2018
Messages
269
Office Version
  1. 365
Platform
  1. Windows
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 . . .

VBA Code:
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,527
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 14, 2018
Messages
269
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 14, 2018
Messages
269
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
         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

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,527
Office Version
  1. 365
Platform
  1. Windows
Sure, so we use Column C to determine the next available row, and then enter the values accordingly...

VBA Code:
        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
 

Forum statistics

Threads
1,176,500
Messages
5,903,415
Members
435,028
Latest member
excelvbanoob420

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top