Macros in Excel - last row of data

Hello everyone

I hope this is the right place to post this...

Basically, I have a folder in which I am storing some survey responses. The data collected from each survey workbook is going into a worksheet (in the same workbook) as rows of data.

Some of the returned surveys will only have one row of data, others will have multiple rows of data.

My "Master" workbook runs a couple of macros -
1. to go through a specified folder and open up each workbook, and then run my "CallData" macro (see below)
2. my "CallData" macro, which basically copies over the rows of data, from each workbook, into the "Master".

So, that is some background, I hope it helps.

My "CallData" macro is as follows:

Code:

Sub CallData()
'
' CallData Macro
'

'
    Sheets("Ignore-this-sheet").Select
    FinalRow = Range("A65536").End(xlUp).Row
    Range("A1:DP & FinalRow").Select
    Selection.Copy
    Windows("Master Template 2.xls").Activate
    Sheets("extracts").Select
    Rows("3:3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
 
    
End Sub

But I get an error msg:

Run-time error '1004':

Method 'Range' of object '_Global' failed

and the debugger points to the line:

Code:

Range("A1:DO & FinalRow").Select

If anyone has any suggestions on how I can fix this, or perhaps a more efficient way to do this, I would be most grateful...

If you need any more info, let me know.

Many thanks!!

DP

 

Hi guys,

sorry to be a pain but I was just wondering if anyone had any ideas for the above? I still haven't managed to fix my issues

Many thanks!!

DP

 

Might want to post in programming for this one. I may be up to the challenge tomorrow, but, it could be doubtful.

I assume you have changed the FinalRow range to something else?

 

Hi theefool, thanks for the response!

Yes, I've now done this:

Code:

Sub CallData()
'
' CallData Macro
'

'
    Sheets("Ignore-this-sheet").Select
    Rows("36:159").Select
    Selection.Delete Shift:=xlUp
    Range(Selection, Cells(1)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("Master Template.xls").Activate
    Sheets("extracts").Select
    Rows("3:3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
  
End Sub

so I think I'm on the right track now.

I discovered this morning, however, that my last row of data is not where I think it is - the spreadsheet was designed by someone else, and they introduced some reference cells in rows 37:39.

If I was to readjust the macro, I would now be looking to write it such that -
- go to cell A1
- from there, select all (adjacent, I suppose) rows and columns (perhaps just 'cells' really) that aren't blank
- copy, etc etc (rest of code)

So if you know a smart way of doing that, that would be a great help.

 

I'm not sure, but you could try something like that

Code:

Sub last()

Dim used As String, i As Long, adr As String, lastCell As Range, _
  lastRow As Long, lastColumn As Long

used = ActiveSheet.UsedRange.Address
i = InStr(used, ":")
adr = Right(used, Len(used) - i)
Set lastCell = Range(adr)
lastRow = lastCell.Row
lastColumn = lastCell.Column

Call MsgBox("last row: " & lastRow & vbCrLf & _
  "last column: " & lastColumn, vbInformation, "Last Cell In Used Range")

End Sub

Regards
GermanOne

 

Hello GermanOne

Thank you for your response.

I tried running my macro with your code, but unfortunately I come up with a "Compile error: Sub or Function not defined".

I'm not really sure why this is coming up, I am running the following macro:

Code:

Sub ProcessAll()
    Dim Wb As Workbook, sFile As String, sPath As String
    Dim itm As Variant
    Dim strFileNames  As String
     
    sPath = "C:\Test1\"
     
     '    Retrieve the current xl files in directory
    sFile = Dir("C:\Test1\" & "*.xls")
    Do While sFile <> ""
        strFileNames = strFileNames & "," & sFile
        sFile = Dir()
     Loop
     
     '  Open each file found
    For Each itm In Split(strFileNames, ",")
        If itm <> "" Then
            Set Wb = Workbooks.Open(sPath & itm)
            Call last 'this runs my macro from above
            Wb.Close True
        End If
    Next itm
     
End Sub

This is the code I have to open all files in a specified directory and then to run a macro on them - in this case, the "last" macro as written by you below.

I might have to just cut my losses at this point, as I can't see a solution to the current problem - as I see it, if I copy over some data which fits into a 'rectangle' of cells, then when it is pasted into my Master Template it will be deleted by the next set of copied data. However, if I copy over a whole row then this problem is averted because I can insert that selection at the top of the spreadsheet continuously, and hence no other data gets lost. But I am not sure if there is a way to write some code that can copy over (complete) rows of data x such that x is the number of rows used - and copy it over in such a way that it is not pasted into my Master, but inserted, so as to avoid losing other data.

Anyway, many thanks for everyone's help with this, it is much appreciated.

DP

 

Well, I'm sure you could solve your problems. You have to define exactly which Workbook and which Worksheet must be activated / selected on which part of your code. I also think you have to switch between the opened workbook and your master more than once, because Range objects of one Worksheet are not available if another Worksheet is selected etc.
Maybe if I would have your files on my screen I could help you more.

At first declare Wb as Module-wide variable and try something like that

Code:

'...
Wb.Activate
Set Ws = Wb.Sheets("NameX")
Ws.Select
used = Ws.UsedRange.Address
'...

Regards
GermanOne