Use insert instead of copy

[Pau905]

Hello, I got a problem with the code below. When copy is used the rows will be shifted down (I do not want that), I would like to use insert instead, but how?

/Pau

    'Copy the data of the first row (header)
    Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))

    For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
        Set wb = Workbooks.Add
      
        'Paste the header row in new file
        RangeOfHeader.Copy wb.Sheets(1).Range("A1")
    
        'Paste the chunk of rows for this file
        Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
        RangeToCopy.Copy wb.Sheets(1).Range("A2")
              
        'Save the new workbook, and close it
        wb.SaveAs ThisWorkbook.Path & "\test" & WorkbookCounter
        wb.Close
    
      'Increment file counter
      WorkbookCounter = WorkbookCounter + 1
    Next p

 

[Fluff]

Not quite sure what you mean by

When copy is used the rows will be shifted down

You are copying the data to A2 every time & it does not "shift" anything.

 

[Pau905]

Hello Fluff,

I solved the problem by using paste instead. With copy I got extra rows at the end.

Thanks for a super forum!

 

[Fluff]

Glad you sorted it & thanks for the feedback.