CODEWITHSUNDEEP
vbaexcelcopy-paste
Diego BV
Diego BV

Reputation: 53

Paste in Excel sheet using vba without a defined range

What i want to do is copy a table from another source, Navision, and PASTE it in excel, give it format, etc. And i'm having trouble with the pasting part. The "copying" process does not matter much, it would be nice to know how to automate it but it's not my priority.

What is copied:

- A table with 7 columns and variable rows from Navision

The existing problems:

- Pasting a table without defined rows ✓ (thanks Ionestorm)
- VBA not recognicing the information in the clipboard

What i think the main problem is:

- I imagine it is copied in an "external clipboard" and vba does not recognize it.

You should know i'm a noob in VBA, i only have background in C++ and basic MySQL and this is the first time i've been interested in applying that knowledge in daily tasks, make them easier. And i've been experimenting.

The information i've found about copy-paste in VBA, states that it is always needed to have a defined range to paste it in, which this excersise does not have. And i've found only examples of copy-paste from excel sheets into other excel sheets. So i do not know how to start.

What is the function to paste it in the active cell from the "external Windows" clipboard?

Upvotes: 0

Views: 1024

Answers (1)

lonestorm
lonestorm

Reputation: 380

You don't need a defined range. Try the code below:

Sub pasteToRange()
    Dim rng As Range
    Set rng = Application.InputBox("Pick worksheet and cell", "Choose paste range", "A1", Type:=8)
    rng.Worksheet.Paste rng.Cells(1, 1)
End Sub

This allows you to manually select the position in which to paste, but you can automate that. The paste bit uses a worksheet function which pastes from the clipboard.

Upvotes: 1

Related Questions