Access copy/paste special

[kommanman]

I've run my queries in Access VBA and exported data into excel. I want to select Range("A4:AC4") (which are formulas reading from data just exported) and paste/values/formatting appending to bottom of same sheet. How do I find the bottom of data and place on next row.

Public Sub TEST()

Dim locationstring7 As String

locationstring7 = "J:\Reports.xlsx"


DoCmd.OpenQuery "QryMakeLoadID"
DoCmd.Close acQuery, "QryMakeLoadID"
DoCmd.OpenQuery "QryMakeLocAvailable"
DoCmd.Close acQuery, "QryMakeLocAvailable"

Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("QryCapacityReport")
Set excelapp = CreateObject("Excel.application", "")

excelapp.Visible = True
Set TargetWorkbook = excelapp.Workbooks.Open("J:\Reports")

TargetWorkbook.Worksheets("data").Range("A3", "K8000").Clear
TargetWorkbook.Worksheets("data").Range("A3").CopyFromRecordset rsQuery


TargetWorkbook.Worksheets("Daily Summary").Range("A4:AC4").Copy
TargetWorkbook.Worksheets("Daily Summary").[A1].Select


TargetWorkbook.Worksheets("Daily Summary").Cells("A1107").paste  

TargetWorkbook.Save
excelapp.Quit


End Sub

[NeoPa]

The Ctrl-End key combination puts you at the end (Bottom-Right) of the used range of cells. In Excel VBA this can be referenced using the SpecialCells(xl LastCell) method. I assume, from what you've said, that you would like to start in column A but from the Row starting immediately after the last used row.

That would be something like :

Range("A" & ActiveCell.SpecialCells(xlLastCell).Row + 1)

SpecialCells(xl LastCell) is a method of the Range class so needs a Range object to be called within, but the Range doesn't seem to matter or affect the result. ActiveCell is merely an example in this case.

[kommanman]

Compile error: Sub or Function not defined.

[NeoPa]

Kommanman:
Compile error: Sub or Function not defined.

What have you tried (not very hard) to tell me?

I'm not too impressed that you've saved yourself the effort of writing much while leaving us working to try to understand what you mean :-(

[kommanman]

I apologize for lack of details but I have been trying many different ways to Paste/special on the last row from internet suggestions.(ap pend new data) This is my first attempt to manipulate data using Access VBA in Excel spreadsheet. Code works fine up to paste. I can copy and paste special.
TargetWorkbook. Worksheets("Dai ly Summary").range ("A1111").paste special
but I don't want line "A1111" I need to append and pastespecial paste formulas and I want values/formats.

[kommanman]

Microsoft excel object library was not checked. Therefore all 'xl...'commands were not working. After change this worked for me.

TargetWorkbook.Worksheets("Daily Summary").Range("A100000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

thank you for your help and motivation.
Cheers!!!

[NeoPa]

Ah. It's starting to make more sense now - and by the way I'm pleased and impressed you managed to find the solution without further help.

You've used an alternative technique from the one I illustrated which is equivalent to jumping a long long way down, such that you know you're beyond the bottom of the data, then using Ctrl-Up to find the first cell (going upwards) in that column that contains data and from there going down one row. Complicated - but it works reliably as long as you can be sure the bottom Row of data in that Column will always have data.

Obviously, now we know that you're working on Excel Workbook from within Access code, we could advise about the Reference but you've got there already too. Well done.

Please understand we do try to help but that our ability to do so is heavily dependent on what it is you share with us.

[kommanman]

I do understand that this community helps and I'm grateful for all input. I'm used to working alone since I'm an Industrial Engineer and we don't have many friends. haha. Sorry for lack of detail but 'you don't know what you don't know" and I will work on my explanations in the future so as to not frustrate.
Thanks again. Cheers.

[NeoPa]

My intention explaining that is to allow you to perceive, not only how important it is for us, but also how important it is for you. The amount of time & effort you will save yourself is also considerable. Sure, it requires a little effort up front, but I don't recall many situations where the OP didn't end up having to put the effort in eventually anyway, and so just ended up with unnecessary delays (Compared to getting answers quickly when all the relevant details are known).

So, I have no wish to get on your back. Just to help you help yourself. Oh, and you're absolutely right about not knowing what you don't know. That comes up a lot. If you think about it though, you generally find you know at least a little more than you thought you did.

Anyway - I'm in danger of lecturing again so I'll stop there with the hope that we are able to help again in the future.