0

I'm trying to paste values using the activesheet. The code keeps throwing out the following error:

"To do this, all the merged cells need to be the same size"

But none of the cells I'm copying are merged. The error comes from the line: ActiveSheet.Range("C23").PasteSpecial...

Dim FM As Worksheet: Set FM = ThisWorkbook.Sheets("Closings Template")

fmPath = "G:\Finance Department\Banking Dashboard\"
FmFile = "Testing_Testing.xlsm"
fmRef = fmPath & FmFile
ToPath = "G:\Budgets and Financial\CLT Budget Templates\"
ToFile = "Belle Grove Manor.xlsx"
ToRef = ToPath & ToFile
CIWPath = "H:\02-CHARLOTTE\Land\zLand Worksheets\"
CIWFile = "Community Information Workbook_CLT.xlsm"
CIWRef = CIWPath & CIWFile

FM.Range("O2").Copy
Workbooks.Open(ToRef).Worksheets("Sheet1").Activate
ActiveSheet.Range("C11").PasteSpecial Paste:=xlPasteValues
FM.Range("P2").Copy
ActiveSheet.Range("C17").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("C15").Value = "14"
Application.CutCopyMode = False
Workbooks.Open(CIWRef).Worksheets("BLSRG.PS").Range("N23").Copy
ActiveSheet.Range("C23").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("C19").Copy
FM.Range("N2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Mesut Akcan
  • 899
  • 7
  • 19
B-Rye
  • 72
  • 1
  • 8
  • 3
    1) See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/9245853), specifically answers about avoiding using `ActiveSheet` and instead using `Workbook` and `Worksheet` variables. 2) Use a simple value transfer instead of copy / paste values, as noted in [this answer](https://stackoverflow.com/a/35864330/9245853). – BigBen Nov 29 '18 at 20:07
  • `Workbooks.Open(CIWRef).Worksheets("BLSRG.PS").Range("N23").Copy` is just evil - it isn't *just* copying N23 in that sheet, it's opening the workbook too. At this point, whatever was the `ActiveSheet` before is no longer the active sheet... I'd highly recommend reading the answer linked above and just starting over. – Comintern Nov 29 '18 at 23:28
  • ActiveSheet.Range("C23") My assumption is that the cell(ActiveSheet.Range("C23") ) will be merged. – Dy.Lee Nov 30 '18 at 01:36

1 Answers1

0

Using ActiveSheet can cause problems and setting up your variable properly will ensure errors are eliminated. Try to incorporate the simple code below.

Dim wbSrc As Workbook
Set wbSrc = Workbooks.Open(CIWRef) 'the source workbook

Dim wsSrc As Worksheet
wsSrc = wbSrc.Sheets("BLSRG.PS") 'the source worksheet

Dim wbDest As Workbook
Set wbDest = Workbooks.Open(ToRef) 'the destination workbook

Dim wsDest As Worksheet
wsDest = wbDest.Sheets("Sheet1") 'the destination worksheet

wsDest.Range("C23").Value = wsSrc.Range("N23").Value 'writes the values from the source worksheet to the destination worksheet
GMalc
  • 2,608
  • 1
  • 9
  • 16
  • I keep getting "Object variable or With block variable not set" when declaring variables. – B-Rye Nov 30 '18 at 14:39
  • Have you declared your variables; `CIWRef`, `ToRef`, and the other variables that make up these two variables, correctly? From you example code you never declared your variables. If you don't understand how to declare variables, you need to do a Google search and learn how. – GMalc Nov 30 '18 at 20:21