Macro shortcut, skip blank rows, and saving copy of worksheet

Vlr516

New Member
Joined
Sep 11, 2017
Messages
22
I was able to get my macro to work with the code below and then I was asked to tweak a little and now I'm having problems again. Assistance on the tweaks to my code is greatly appreciated.

The tweaks I am to add:
1) Add a keyboard shortcut. However, when I do this, the macro stops running at the point when I open my other workbook. Want to use Ctrl + Shift +Q
2) Skip blanks rows in my iteration series as some are merged cells acting as header dividers to breakup the sheet. When I do this, it leaves the blank row still on my new worksheet (shtLoad).
3) Copy the newly created worksheet (shtLoad) as its own workbook (as well as leave a page in the active workbook) and title it using part of the name of the sheet (shtReq) it is pulling data from. I haven't tried this because my brain is fried after how long I've spent trying to get everything else to work.

Code:
Sub QC()
Dim wbPPM As Workbook
Dim wbQC As Workbook
Dim shtTitle As Worksheet
Dim shtQC As Worksheet
Dim shtLoad As Variant
Dim shtReq As Variant
Dim ReqNo As String
Dim ReqName As String
Dim FinalRow As Long
Dim i As Integer, j As Integer

Set wbPPM = ThisWorkbook
Set shtTitle = wbPPM.Worksheets(1)
Set shtReq = ActiveSheet
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row

Set wbQC = Workbooks.Open("[Location]")
Set shtQC = wbQC.Worksheets(1)
shtQC.Copy Before:=wbPPM.Sheets(10)
wbQC.Close
Set shtLoad = ActiveSheet

For i = 6 To FinalRow
  ReqNo = shtReq.Cells(i, 1).Value
  ReqName = shtReq.Cells(i, 3).Value
  ActiveSheet.Cells(i, 3).Value = ReqNo & "--" & ReqName
  Desc = shtReq.Cells(i, 4).Value
  ActiveSheet.Cells(i, 4).Value = Desc
  PPMNo = shtTitle.Range("G9").Value
  ActiveSheet.Cells(i, 8).Value = PPMNo
Next i
End Sub
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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