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

New Member
Joined
Jan 28, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
Screenshot 2023-01-30 092714.png
 
Upvote 0
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)

VBA Code:
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top