0

I'm copying values as part of one sub process and pasting value through an update button on userform.

To copy values:

Private Sub Month1_Click()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Dim wkb As Workbook
Dim wks As Worksheet
Set wkb = Workbooks.Open("Place on drive")
Set wks = wkb.Sheets("Training1")
    wks.Range("Start:Finish").Copy
wkb.Close
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.ScreenUpdating = True
End Sub

To paste values in current sheet:

Private Sub UpdateActuals_Click()
For i = 1 To 12
If Me.Controls("Month" & i).Value = True Then
ThisWorkbook.Sheets("2017 Actuals").Range(i+1, 5).PasteSpecial xlPasteValues
End If
Next i
End Sub

If I replace "i+1, 5" with "B5", it errors with

"PasteSpecial method of Range class failed".

I feel as if values copied in one sub process are not brought to second one, would that be correct?

Also, how do I reduce processing time given that I have 12 months (12 files) in various places that I can't change the location for...

braX
  • 11,506
  • 5
  • 20
  • 33
user8958702
  • 57
  • 6
  • 1
    1) Copy | Paste should be done in same module. After copy is done in first sub it's wiped out as soon as sub ends. 2) *how do i reduce processing time* - please limit your questions to one specific issue, it's more helpful for everyone that way. (see [ask] for more info) – Scott Holtzman Dec 28 '17 at 18:29
  • Also, instead of using Copy/Paste you can just use a simple assignment: `ThisWorkbook.Sheets("2017 Actuals").Range(i+1, 5) = wks.Range("Start:Finish")` – PeterT Dec 28 '17 at 18:48
  • https://stackoverflow.com/a/34886033/4539709 – 0m3r Dec 28 '17 at 19:04

1 Answers1

0

Range usually likes a starting cell and an ending cell. I suggest since you are looking at just one cell that you change .Range to .Cells. If you really want to use a range with RC format, .Range(Cells(row1, col1), Cells(row2, col2)), if you want just one cell then you can make the two parts the same. I have run into problems before using Range and only one cell definition before, either make it .Cells for your target or fill out Range the way I have explained.. Cheers.

Dim 2017actWS AS Worksheet
Set 2017actWS = ThisWorkbook.Worksheets("2017 Actuals")

1)

2017actWS.Cells(i+1, 5).PasteSpecial xlPasteValues

-or-

2)

2017actWS.Range(2017actWS.Cells(i+1, 5), 2017actWS.Cells(i+1,5)).PasteSpecial xlPasteValues

When using Ranges excel will often throw errors if they are not the same size in a copy and paste, you can eliminate that by using a single cell as the starting target of your paste with .Cells

Also I don't see you call your function. You will want your paste close to your copy or you might find things get strange (suggestion: just after your copy).

Edited to be sure there is not worksheeet ambiguity. Thank you Scott C.

Cheers, WWC

Wookies-Will-Code
  • 735
  • 9
  • 21
  • Your second answer will error if the sheets("2017 Actuals") is not the active sheet as the cells inside the range will be referencing a different sheet. – Scott Craner Dec 28 '17 at 21:05
  • got it, good catch, helped out with some better programming practice along the way, never leave it to chance. – Wookies-Will-Code Dec 28 '17 at 21:30