Macro to copy a selection of cells and insert it at a specific cell in a different sheet, and then export the sheet to a pdf

[Arcinna]

So i'm trying to help a friend create a macro that allows them to copy a selection of cells from the first sheet in the workbook and insert them at a specific cell in a different sheet. It would be inserting them at B14 in a sheet called "Report" and ideally shifting all cells below that down. I would then want it to export the "Report" sheet to a pdf with prompt to name the pdf it is saving/where it is saving it. I don't know if it is possible to also have the macro delete the cells in the "Report" sheet that it previously inserted after it saves the pdf or undo the action so that the "Report" sheet is ready to have this same macro used again. This project rapidly ended up being far beyond my basic understanding of visual basic so any help would be appreciated.

 

[Arcinna]

So I have a macro mostly done with some help from this forum,

Sub Order_Subsets()

Dim SourceRange As Range
Dim DestinationRange As Range
Set SourceRange = Selection
Set DestinationRange = Worksheets("Report").Range("B14")
SourceRange.Copy
DestinationRange.Insert Shift:=xlDown
Application.CutCopyMode = False



Dim ws As Worksheet
Dim File_Name As String
Dim Destination As String
Set ws = Sheets("Report")
Destination = "C:\Users\User\Documents\"
File_Name = ws.Range("D14").Value & ".pdf"
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Destination & File_Name, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Set ws = Nothing


' Keyboard Shortcut: Ctrl+z

End Sub

So far I have gotten the macro to copy a selection from the "2023" sheet and insert it at a specific point on the "Report" sheet and shift the cells below it down, and then save the sheet to a pdf and automatically name it based on a cell value to a certain location. The final thing I would like it to do is delete the rows that I previously inserted earlier in the macro to clear the "Report" sheet, so that its ready to have the same macro used again with a different selection of data. I don't know if its possible to simply remove the set of data that it inserted previously, or if it would be easier to have the macro go through the "Report" sheet and simply remove any row that contains the string "P-" and then shift the cells below it up. Every row that I would want removed will have a sample id that always starts with "P-" so that would be the easiest way to determine all the rows that need to be removed, I just need it to do that and then shift the cells below the removed rows up. I have been trying to figure out the best way to do this for awhile today and people here probably know far better than I would.

 

[HaHoBe]

Hi Arcinna,

in future please use code-tags to display your procedures here.

What about (deleting the range of data that was in Sheet Report prior to copying)

Sub Order_Subsets()
' https://www.mrexcel.com/board/threads/how-to-make-the-end-of-a-macro-delete-rows-on-a-sheet-if-the-row-contains-a-particular-string-in-a-cell.1228511/
Dim rngSrc          As Range
Dim rngDest         As Range
Dim strFName        As String
Dim strPathFold     As String
Dim ws              As Worksheet

strPathFold = "C:\Users\User\Documents\"

Set ws = Sheets("Report")
Set rngSrc = Selection
Set rngDest = ws.Range("B14")
rngSrc.Copy
rngDest.Insert Shift:=xlDown
Application.CutCopyMode = False

With ws
  strFName = .Range("D14").Value & ".pdf"
  .ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=strPathFold & strFName, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False
  'get the range to delete starting at row 14 + the number of rows inserted and
  'the last row being the row from the "solid" data (equals combination of CTRL+ShiftDown when being in B14
  .Range("B" & 14 + rngSrc.Rows.Count & ":G" & .Range("B14").End(xlDown).Row).Delete xlShiftUp
End With

Set rngDest = Nothing
Set rngSrc = Nothing
Set ws = Nothing

' Keyboard Shortcut: Ctrl+z

End Sub

In the picture you attached all cells in column E start with "P-" so any macro looping from the bottom would need the row of the area to delete to be starting row (14) and number of rows inserted from rngSrc. Looping should start at the lower end and work up in order to delete all rows concerned.

You should think about using a different approach to use when it comes to identify the range to copy. Selection doesn't prevent you from copying over just one cell or more than 6 columns so you should consider to implement some check to prevent what I just pointed out.

Ciao,
Holger

 

[Fluff]

@Arcinna

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.
On this occasion I have merged both threads.