How to copy data to another sheet of the same workbook and to a different workbook?

Question

When opening both workbooks (though not neccessarily), I want to run a macro that will copy the raw data from the first sheet into another sheet of the same workbook, and copy another set of raw data from the first sheet into the third sheet which comes from another workbook.

The first part (copy from 1 sheet to another sheet of the same workbook) is fine. But when I type new code for copying to another workbook, there is a problem. The current error is "run time error '13' Type mismatched"

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set rawData = Workbooks.Open("D:\Work\Framas\CrusherRoom_V10_Test.xlsm")
Set materialStock = Workbooks.Open("D:\Work\Framas\MaterialStock_V01_Test.xlsm")

Set copySheet = rawData.Worksheets("VIE Screen")
Set pasteSheet = rawData.Worksheets("Crusher tracking")
Set pasteSheet2 = materialStock.Worksheets("Sheet1")

copySheet.Select
If Range("F23").Value = "OK" Then

Range("B7").Select
Selection.Copy
Range("O9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Range("B7").Select
Selection.Copy
Range("O18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

copySheet.Range("O9:AR9").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

' Copy to another sheet begins from here

copySheet.Range("O18:AG18").Copy
pasteSheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End If

Harassed Dad Harassed Dad · Accepted Answer · 2019-08-28T11:45:19

The first rule of VBA is Never Use Select

Here's your code rewritten

Option Explicit
Sub ts()
Dim rawData As Workbook
Dim materialStock As Workbook

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim pasteSheet2 As Worksheet

Set rawData = Workbooks.Open("D:\Work\Framas\CrusherRoom_V10_Test.xlsm")
Set materialStock = Workbooks.Open("D:\Work\Framas\MaterialStock_V01_Test.xlsm")

Set copySheet = rawData.Worksheets("VIE Screen")
Set pasteSheet = rawData.Worksheets("Crusher tracking")
Set pasteSheet2 = materialStock.Worksheets("Sheet1")


If copySheet.Range("F23").Value = "OK" Then
'as written this code copies cells in whichever sheet is active - you should specify which sheet you mean
    Range("O9").Formula = Range("B7").Value
    Range("O18").Formula = Range("B7").Value
    ' Copy to another sheet begins from here
    copySheet.Range("O9:AR9").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    'copy to another workbook starts here
    copySheet.Range("O18:AG18").Copy
    pasteSheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial lPasteValues
End If

End Sub

I can't see anything wrong with it at present - but I don't have your files. A few points: Always use Option Explicit, always define your variables. If you step through this code using F8 you can see what line is throwing the error. It may be a typo in the name of a sheet - do the workbooks actually open?

How to copy data to another sheet of the same workbook and to a different workbook?

1 Answers