Hello. I have 20 workbooks into which different offices data from Access is hous

Question

Hello. I have 20 workbooks into which different offices data from Access is housed. There are six columns, A-F, and varying number of rows.

Here is the simple copy and paste code from the first three workbooks:

Windows("FOD By Office Tables.xlsm").Activate
    Range("A2:F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Statewide By Office Tables .xlsm").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Maintenance By Office Tables.xlsm").Activate
    Range("A2:F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Statewide By Office Tables .xlsm").Activate
    Range("A19").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=12
    Windows("Q&C By Office Tables.xlsm").Activate
    Range("A2:F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Statewide By Office Tables .xlsm").Activate
    Range("A39").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
   

This works fine when copying and pasting the first time but the number of rows will change each time in each worksheet so the 'Range("A19").Select' command, for instance, will not work the second time because there would be more rows of data in the first worksheet than there were the first time., The same for each subsequent worksheet.

Basically, I need the macro to say to look for the first blank column A cell, then paste beginning there. This command would have to be shown throughout the macro at each 'paste' point.

Thanks in advance!:-)

Explanation / Answer

Change the line

    Range("A19").Select

to

    Range("A" & Rows.Count).End(xlUp).Offset(1).Select

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.