Insert Row Macro

[BraindG]

Hi All,

I've been trying for a few days to find and/or manipulate a macro which can perform the following.

It has to Import 2 cells from the same row but different columns from a external Spreadsheet, the quantity of rows from the external spreadsheet can vary from 5 to 500+ rows thus the macro should be able to a) determining the external docs row count and b) insert those rows into the master sheet between a determined set of rows.

so for example.

External.xls
Row 23 -> 43 and Columns H and K contains the information required.

Master.xls
20 Rows need to be inserted into Sheet1, Row 50 and Columns A and B need to be populated with the content from H and K from the External.xls document

Hope that makes sense.

The macro I worked on could insert a row but the information inserted would span all cells in all Columns rather than inserting the information into 1 cell - unfortunately the macro I did have was over written and I didn't save a copy! doh - but ill try to find the original I was working on after posting this.

Thanks for taking the time to read this.

 

[BraindG]

Finally found Macro!

OK so here is what i have.

Sub ImportCells()
    Dim strFile As String, wbCopy As Workbook
    Dim rCell1 As Range
    Dim rPaste1 As Range
 
    strFile = Application.GetOpenFilename
    If strFile = "False" Then Exit Sub
 
    Set wbCopy = Workbooks.Open(strFile)
 
    On Error Resume Next
    Set rCell1 = Range("H2", Range("H65536").End(xlUp))
 
    ThisWorkbook.Activate
 
    rCell1.Copy
    Range("A1").PasteSpecial xlValues
    Range("A1").PasteSpecial xlFormats
    Application.CutCopyMode = False
 
    wbCopy.Close False
    On Error GoTo 0
End Sub

What i need to happen now is that when it pastes the data that i creates a new row per item, so if theres 50 cells in external.xls 50 new Rows are a created and the copied data is pasted into Column B.

I've fiddled about with other options insertrow for example but my lack of knowledge, keywords im finding it hard to google what i need.

Could someone point me in the right direction?

Thanks
B

 

[BraindG]

Anyone any ideas?

Thanks