Close VBA and Return to last Data row of Data

[Ananthak275]

My code pulls data from one sheet to another. I'm not sure how I to close the srcSht (its bolded in the code) at the end. As well as return to the last row of data on the destSHT, since it goes to the very bottom once the data has been copied over.

Sub Macro()
    Dim destSht As Worksheet, srcSht As Worksheet
    Dim src_ColCnt As Integer, dest_ColCnt As Integer
   
    Set srcSht = Workbooks.Open("D:\data.xlsx").Sheets("Sheet1")
    Set destSht = Workbooks.Open("D:\report.xlsx").Sheets("Sheet1")
   
    dest_ColCnt = destSht.Range("A1").End(xlToRight).Column
    src_ColCnt = srcSht.Range("A1").End(xlToRight).Column
    src_RCnt = srcSht.Range("A1").End(xlDown).Row - 1
   
    For i = 1 To src_ColCnt
        Header = srcSht.Cells(1, i)
        For j = 1 To dest_ColCnt
            If destSht.Cells(1, j).Value = Header Then
                For r = 1 To src_RCnt
                    destSht.Cells(r + 1, j).Value = srcSht.Cells(r + 1, i).Value
                Next r
            End If
        Next j
    Next i
End Sub

 

[Joe4]

I'm not sure how I to close the srcSht (its bolded in the code)

You don't close sheets. You close workbooks.

So, you would close it like:

Windows("data.xlsx").Close

As well as return to the last row of data on the destSHT, since it goes to the very bottom once the data has been copied over.

You could do that like this:

destSHT.Activate
Range("A1").SpecialCells(xlLastCell).Select

 

[Ananthak275]

You don't close sheets. You close workbooks.

So, you would close it like:

Windows("data.xlsx").Close

You could do that like this:

destSHT.Activate
Range("A1").SpecialCells(xlLastCell).Select

Windows([B]"data.xlsx")[/B].Close

->I'm automating a process, so I'm trying to reduce as much manual work as possible. is there a way to close it automatically without inputting the name everytime?

 

[Joe4]

I'm automating a process, so I'm trying to reduce as much manual work as possible. is there a way to close it automatically without inputting the name everytime?

Create a workbook variable to store the name in, much like you have already done with the worksheet variables, i.e.

Dim wb as Workbook
Set wb = ....

Then you can close it later just like this:

wb.Close

See here for more information on workbook variables: Set Workbook

 

[Marc L]

Hi !​

is there a way to close it automatically without inputting the name everytime?

Yes obviously from your worksheet reference : srcSht.Parent.Close …​

 

[Ananthak275]

Hi !​

Yes obviously from your worksheet reference : srcSht.Parent.Close …​

Sorry, I'm new to VBA. You put the ellipsis after "srcSht.Parent.Close" , what would I have to write after?

 

[Marc L]

​

Try first as it is ! As if the source sheet is not modified this is enough. See also the VBA help for Workbook.Close …​