0

I am trying to create a code to paste a range from a pivot multiple times based on a separate cell value. Essentially I'm trying to create a dynamic cashflow.

The code is not working, and im getting the following error:

Compile error:

Method or data member not found

Method See code attempt below

 Sub TradeDump()

 `Dim ws As Worksheet`
 `Dim wsp As Worksheet`
 `Dim wsc As Worksheet`
 `Dim i As Integer`

 `Set wsp = Sheets("Pivot")`
 `Set ws = Sheets("Cashflow Fix Data")`
 `Set wsc = Sheets("Control")`

 `Period = wsc.Range("Term")`

 `rng = wsp.Range(Range("G6"), wsp.Range("G6").End(xlDown)).Select`

 `For i = 1 To Period`
 `NextRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row + 1`
    'get the Next Empty Row
 `ws.Range("E" & NextRow).PasteSpecial xlPasteAll` 

    'paste

 Next i

End Sub
appiahjay
  • 1
  • 2
  • Hi @BigBen! added the error. – appiahjay Feb 24 '20 at 15:52
  • I'm assuming it's on `wsp.Selection.Copy` - but you shouldn't `Select` or use `Selection` at all - see [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Feb 24 '20 at 15:53
  • @BigBen correct the data is on a pivot at wsp let me try to follow that link. Sorry I am really new to VBA. – appiahjay Feb 24 '20 at 16:15
  • Updated the code but its stopping at the highlighted line. – appiahjay Feb 24 '20 at 16:55
  • You can [edit] your question with the revised code, thanks. – BigBen Feb 24 '20 at 16:59
  • @BigBen code crashing at this line. Error is object variable or with block variable not set rng = wsp.Range(Range("G6"), wsp.Range("G6").End(xlDown)).Select – appiahjay Feb 24 '20 at 17:03
  • Don't `Select`, and you need a `Set`: `Set rng = wsp.Range(wsp.Range("G6",...)`, though you should see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) too. – BigBen Feb 24 '20 at 17:05
  • Thanks @BigBen after removing .select and adding in the Set rng the code works perfectly! – appiahjay Feb 24 '20 at 17:11
  • Make sure you add `wsp` in front of the first instance of `Range("G6")` too :-) – BigBen Feb 24 '20 at 17:11

0 Answers0