Link to home
Create AccountLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

VBA identify the correct worksheet in existing code

My code works perfectly if I run from active sheet. 
However, I need to place a button to run this on another sheet. 
It then pastes the data to the sheet with the button - not the correct sheet ("Sheet1")

In the line  Set r = sh.Range("A90000").End(xlUp).Offset(1, 0)

I need to specify the correct sheet in the workbook. 
Can anyone help?? I would really appreciate---THANK YOU!


Sub CombineOther()

Dim r As Range

Dim wb As Workbook

Dim sh As Worksheet

Set wb = ActiveWorkbook

Set sh = wb.ActiveSheet

 

 

    FilePth = "mypath"

    FileNm = Dir(FilePth & "*.xlsx")

    Do While FileNm <> ""

        Workbooks.Open FilePth & FileNm


        Sheets("Sheet1").Range("A5:CX50000").Copy

        Set r = sh.Range("A90000").End(xlUp).Offset(1, 0)

        r.PasteSpecial xlPasteAll

                Application.DisplayAlerts = False

        Workbooks(FileNm).Close SaveChanges:=False

                Application.DisplayAlerts = True

        FileNm = Dir

    Loop

   Cells.Select

        Cells.EntireColumn.AutoFit

    Range("A1").Select

    End Sub


ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Euro5

ASKER

MARTIN!! Thank you so much!
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
        Experts Exchange Most Valuable Expert (MVE) 2015 and 2017
        Experts Exchange Distinguished Expert in Excel 2018, 2021, 2022
        Experts Exchange Distinguished Expert in Microsoft Office 2022
        Experts Exchange Distinguished Expert in VBA 2022
        Experts Exchange Top Expert VBA 2018 to 2022
        Experts Exchange Top Expert Visual Basic Classic 2012 to 2022