Need To Paste Into First Blank In Range

Seems like this has been answered before. But i've spent a few hours trying to retrofit, and keep getting compile errors. Long story short: when the macro activates it needs to allow the user to navigate to a workbook, open that workbook, copy the data available, and paste into the first empty cell in cell A95 on the "Balance Check Paste" tab of the original workbook. Ideally, it closes the file it opened once complete (i haven't gotten that far yet).

The code i have will do everything up to pasting it into the workbook. I found a solution that could do the last blank cell in column A, but i have hidden cells that help hold places in a pivot sheet, and it would just overwrite those. So i went to a range and have hit a brick wall.

My code:

Sub Daily_Update()'
'###-Import data from PMS export sheets-###
'
 'Import Balance Check Data
 
Application.ScreenUpdating = False
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook




Set CurrentBook = ActiveWorkbook
MsgBox ("Please select file with Balance Check data for import")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
    Range("A2:E2" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
End With








Set CurrentBook = ActiveWorkbook
With CurrentBook.Sheets("Balance Check Entry").Range("a95").CurrentRegion
     LR_wbSelectNew = .Rows(.Rows.Count).Row
 End With
  












End Sub

Re: Need To Paste Into First Blank In Range

THis code will do everything except find the last cell and paste whats on the clipboard:

Sub Daily_Update()'
'###-Import data from PMS export sheets-###
'
   Application.ScreenUpdating = False
   'Import Balance Check Data
   
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook




Set CurrentBook = ActiveWorkbook
MsgBox ("Please select file with Balance Check data for import")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
    Range("A2:E2" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
End With




Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True




Set CurrentBook = ActiveWorkbook
Worksheets("Balance Check Paste").Activate
ActiveSheet.Range("A95").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste








End Sub

Re: Need To Paste Into First Blank In Range

Quote from sktneer;789209

It should work except your copy line of code is not correct and it should be like this....

Code

Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Copy

That code is visually identical to what I had.

The issue I think im having is with this:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
 
Set CurrentBook = ActiveWorkbook
Worksheets("Balance Check Paste").Activate
ActiveSheet.Range("A95").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

I error on

ActiveSheet.Range("A95").End(xlDown).Offset(1, 0).Select

However I can simply click in the cell I want and paste the data that was copied in the workbook active in the macro.

Re: Need To Paste Into First Blank In Range

You know, i had that solution but tried to find a work around. I have "dummy data" that takes up the first 94 rows (to allow a pivot sheet to display a full calendar month for linking purposes...its too much data for sumproduct arrays), and i prefer to keep them hidden. That command will not account for hidden rows, and it seems the loops required to do that is more that im willing to invest.

Anyway, in retrospect it doesn't matter. Once the macros are in place I can try to do some hide or unhide functions, or just not worry about it as its more about cosmetic than function.

Thank you for your help.