0

I have a worksheet on which I have consolidated various macros performing different actions into one macro. Basically the macro copies information from one tab to the active tab, then clean up the data, add new lines and copy data into those lines. This works perfectly. What I need to do to finalize the process is to copy all the data on the active sheet, create a new workbook and then paste the data into that workbook. I have recorded a macro which do all this but if I'm trying to use the code of that macro in my original macro I get the Run-time error 1004: Application-defined or Object-defined error. This is the code of the Macro that works:

Sub CreateFile()

    Dim rng As Range
Dim wbk As Workbook

Set rng = Sheets(Sheet7).Range("A:F")
rng.Cells.Copy

Set wbk = Workbooks.Add
ActiveSheet.Paste.rng


End Sub

When I use this code it does not create a new workbook and I end up getting a Type mismatch error.

Louw
  • 7
  • 5
  • This part of the macro should be right at the end. There will be no more actions after this – Louw Apr 03 '20 at 12:38
  • Checkout the link that I added above your question. Apply that technique to your whole code. Make sure you don't have any `Select` or `.Selection` in your code anymore and your code will work. • If you get stuck come back with your changed code. – Pᴇʜ Apr 03 '20 at 12:40
  • 1
    Thank you for the feedback. I will try to get rid of the select and selection and let you know if I continue struggling. – Louw Apr 03 '20 at 12:48
  • I have gone through the link you sent but cannot find a solution to my problem. I know how to declare variables for the source data range and the current worksheet but I have no idea how to use those variables when the new workbook is created. Something I also don't understand is why the code would work in a standalone macro but not when I copy it into an existing macro even though both are done on the same sheet – Louw Apr 03 '20 at 13:01
  • Because if you write `Rows("2:2")` it is not defined which worksheet these rows are in. Using select is very unreliable. Instead specify your workbooks and worksheets as in the link. For example if you `Set w = Workbooks.Add` then you can access it with `w.Worksheets(1).Cells.Paste` or `w.Worksheets(1).Rows(2).Delete Shift:=xlUp` If you get rid of all your `Select` and `Selections` your code is reliable and will work. • Give it a try and update your code in the question if you get stuck. – Pᴇʜ Apr 03 '20 at 13:06
  • I have tried this code but it does not work Dim rng As Range Dim wbk As Workbook Set rng = Sheets(Sheet7).Range("A:F") rng.Cells.Copy Set wbk = Workbooks.Add ActiveSheet.Paste.rng THis code generates a Type mismatch error – Louw Apr 03 '20 at 13:07
  • Show how you did that, and "does not work" is not an error description, you need to be more specific. Please update the code in the question with what you have tried. Code in comments is unreadable. – Pᴇʜ Apr 03 '20 at 13:09
  • I have updated the code above – Louw Apr 03 '20 at 13:13
  • `Sheets(Sheet7).Range("A:F")` should be either `Sheet7.Range("A:F")` if you mean the VBA name of the sheet or `ThisWorkbook.Worksheets("Sheet7").Range("A:F")` if you mean the tab name of the worksheet. `ActiveSheet.Paste.rng` is no valid syntax and should be `wbk.Worksheets(1).Paste`. – Pᴇʜ Apr 03 '20 at 13:28
  • 1
    Thank you for your help. That solved the problem. – Louw Apr 03 '20 at 13:57

0 Answers0