Help with VBA Macro

[braidp]

Hi Guys,

I was wondering if anyone can help with the below. I have this Macro working and it's returning the data needed from each of the files, however it only works if I keep the button to run the macro in the 'raw data' worksheet. Ideally I would like this button to sit on the sheet named 'overview' that would then run the macro and paste the data into the raw data sheet? When I've tried it it references this line as being the error on the macro.

Many thanks in advance

ActiveSheet.Paste Destination:=Worksheets("Raw Data").Range(Cells(erow, 31), Cells(erow, 1))

Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = Application.ActiveWorkbook.Path
MyFile = Dir(Filepath + "\*.*")
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents

Do While Len(MyFile) > 0
If MyFile = "ZMasterFileDTL.xlsm" Then
Exit Sub
End If

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Workbooks.Open (Filepath & "\" & MyFile)
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Range("A2:U900").Copy
Sheets("Sheet1").Visible = False
ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Raw Data").Range(Cells(erow, 31), Cells(erow, 1))

MyFile = Dir

Application.DisplayAlerts = True


Loop


End Sub

 

[jasonb75]

It's an error because you're not pasting to the active sheet, you need to use

Worksheets("Raw Data").Range(Cells(erow, 31), Cells(erow, 1)).Paste
Application.CutCopyMode = False

The second line is to clear the copied data from the clipboard after pasting.

 

[braidp]

Hi Jason,

I really appreciate your help with this, I've pasted your suggestion into the code as per below but that line is still returning an error, any ideas on what I can try next?

Many thanks!!

Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = Application.ActiveWorkbook.Path
MyFile = Dir(Filepath + "\*.*")
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents

Do While Len(MyFile) > 0
If MyFile = "ZMasterFileDTL.xlsm" Then
Exit Sub
End If

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Workbooks.Open (Filepath & "\" & MyFile)
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Range("A2:U900").Copy
Sheets("Sheet1").Visible = False
ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Worksheets("Raw Data").Range(Cells(erow, 31), Cells(erow, 1)).Paste
Application.CutCopyMode = False

MyFile = Dir

Application.DisplayAlerts = True


Loop


End Sub

 

[jasonb75]

What error are you getting?

 

[braidp]

If i try and run it from the page I want to run it from - I get Run Time Error 1004. Application-defined or object-defined error

If I now run it from the raw data page I now also get the error Run time error 438. Object doesn't support this property or method

Thanks Jason

 

[snjpverma]

Instead of Paste write pastespecial like this:

Worksheets("Raw Data").Range(Cells(erow, 31), Cells(erow, 1)).Pastespecial

 

[braidp]

Thanks snjpverma, I gave that a try but it's returning the - Run Time Error 1004. Application-defined or object-defined error.

Any other suggestions?

Many thanks

 

[jasonb75]

If i try and run it from the page I want to run it from - I get Run Time Error 1004. Application-defined or object-defined error

If I now run it from the raw data page I now also get the error Run time error 438. Object doesn't support this property or method

Are these errors both occurring on the same line?

 

[braidp]

yes Jason,

Depending on which sheet I place the button to run the macro.

If I place it where I want to place it I get the 1004 error.
Trying it where it previously worked in "raw data" I get the 438 error, I amended it to Pastespecial as recoomended by snj which fixed it on the "raw data" sheet but still errors 1004 on the sheet I would prefer the button.

 

[jasonb75]

Think I've just spotted the problem.

With Worksheets("Raw Data")
    .Range(.Cells(erow, 31), .Cells(erow, 1)).Paste
End With

Range was referring to 'Raw Data' but the 2 instances of Cells were both still linked to the activesheet.