0

.....on a separate workbook indefinitely.

Hello First off I am new here and very new to VBA. I have a workbook that has a list that will grow indefinitely named "book1" and the code i pieced together grabs data from a range in that book and pastes it into another book "DMAutocalcs"in one specific row one at a time and the the code executes a refresh and wait time, after which it copy certain pricing date from a specific range in "DMautoCalcs" back into Book1. As of Now i am manually copying the code and modifying it for each range of calls it needs to transfer. so there in lies the issue, inherently it will be limited by the number of times i wish to copy what i have existing. I intend to modify the code to loop and perform the copy paste between the workbooks until it reaches an empty cell in "book1" however every attempt i have made has failed, it only continually works the same ranges over and over unless i manually copy the code and modify for each new line. i fear i do not fully understand the range rows and cell aspects when it comes to relatives and absolutes and the proper syntax on how to call the out accurately. how do i achieve this? Any help would be appreciated.

Public Sub macro_54() ' Keyboard Shortcut: Ctrl+p Dim StartTime As Double Dim SecondsElapsed As Double StartTime = Timer Workbooks.Open ("C:\Users\Legacy\Desktop\DMAutoCalcs.xlsm") Windows("Book1.xlsm").Activate Range("a2:l2").Select Selection.Copy Windows("DMAutoCalcs.xlsm").Activate Range("a1:q1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Refresh ActiveWorkbook.RefreshAll Application.Wait (Now + TimeValue("0:00:03")) ActiveWorkbook.RefreshAll Windows("DMAutoCalcs.xlsm").Activate Range("T2:x2").Select 'Application.CutCopyMode = False Selection.Copy Windows("Book1.xlsm").Activate Range("M2:q2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' copy from calcs pricing info and past into pricelist ' return to pricelist ' Selects cell down 1 row from active cell. 'New Line Windows("Book1.xlsm").Activate Range("a3:l3").Select Selection.Copy Windows("DMAutoCalcs.xlsm").Activate Range("a1:q1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Refresh ActiveWorkbook.RefreshAll Application.Wait (Now + TimeValue("0:00:03")) ActiveWorkbook.RefreshAll Windows("DMAutoCalcs.xlsm").Activate Range("T2:x2").Select 'Application.CutCopyMode = False Selection.Copy Windows("Book1.xlsm").Activate Range("M3:q3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' copy from calcs pricing info and past into pricelist ' return to pricelist ' Selects cell down 1 row from active cell. 'New Line Windows("Book1.xlsm").Activate Range("a4:l4").Select Selection.Copy Windows("DMAutoCalcs.xlsm").Activate Range("a1:q1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Refresh ActiveWorkbook.RefreshAll Application.Wait (Now + TimeValue("0:00:03")) ActiveWorkbook.RefreshAll Windows("DMAutoCalcs.xlsm").Activate Range("T2:x2").Select 'Application.CutCopyMode = False Selection.Copy Windows("Book1.xlsm").Activate Range("M4:q4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' copy from calcs pricing info and past into pricelist ' return to pricelist ' ' Selects cell down 1 row from active cell. ' And so on and so forth.... Windows("DMAutoCalcs.xlsm").Activate ActiveWorkbook.Close savechanges:=False Windows("Book1.xlsm").Activate 'Determine how many seconds code took to run SecondsElapsed = Round(Timer - StartTime, 2) 'Notify user in seconds MsgBox "All Ranges Updated, Calc sheet closed successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

Improve this question

1 Answer 1

Reset to default
1

You need not to select or activate a range or window before copying and pasting. Below is the modified code from I can understand you.

Sub macro_54_Modified() 'Let your working sheets in Book1 and DMAutoCalcs are Sheet1 and Sheet2, respectively Workbooks.Open "C:\Users\Legacy\Desktop\DMAutoCalcs.xlsm" Dim wsDm As Worksheet, wsB1 As Worksheet, lastRow As Long, i As Long Set wsB1 = Workbooks("Book1.xlsm").Sheets("Sheet1") Set wsDm = Workbooks("DMAutoCalcs.xlsm").Sheets("Sheet2") 'Last row number in column A lastRow = wsB1.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastRow wsB1.Range("A2:L2").Offset(i - 2).Copy wsDm.Range("a1:q1") 'VBA code for Refresh ... ? wsDm.Range("T2:X2").Copy wsB1.Range("M2:q2").Offset(i - 2) Next i End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thank you for the prompt response, i will give it a try.
Kelvin 004, Thank you for that, that has definitely sent us in the right direction. I am absolutely amazed. However, once it does its thing right before the it moves on to the next one the values reset to zero as if the value isn't fully being pasted or stored in the cell.
I got it but i had to modify the last bit of your cod as follows: [code/] wsDm.Range("T2:X2").Copy wsB1.Range("r2:v2").Offset(i - 2).PasteSpecial xlPasteValues Next i [code/]
Yes. Do necessary changes to suit your situation. I guess the range to be copied contains formulas that involve other ranges. Thank you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.