4 Barrel Harold
New Member
- Joined
- Jun 15, 2018
- Messages
- 21
Hey Guys and Gals, 4BBL Harold here again with anotherproblem that I need help with.<o></o>
Here is brief description of the Workbook and the 2 sheetsin the workbook that I’m working on, on the first sheet “Report Selection” Ihave around 20-30 VBA buttons setup with different Report names on them , Mysecond sheet “Completed Reports” which has the 20-30 reports saved on it.<o></o>
1st step Atrandom if any of the 20-30 Report VBA Buttons are pressed on “Report Selection”sheet. Lets just say I press VBA Button “Report #8” and I have “Report #6” openon “Report Selection” sheet.I need tocopy “report #6” from “Report Selection” sheet and paste it over “report #6” onthe “Completed Reports” sheet. <o></o>
The location of “report #6” on” Completed reports” sheet isV225:AO297. I have put the range of “Reports #6” In the cell AJ225 on the“Completed Reports” and all other Reports have similar cell to tell VBA buttonwhere to paste completed report <o></o>
<o></o>
2nd step “Report Selection” sheetresets cells on page due to other reportshave merged cells that sometimes causes problems with new report being pastedover existing merged cells not lining up<o></o>
3rd stepCopy “Report #8” From “Completed Reports” and Paste to D16 on “ReportSelection”<o></o>
4Th ActiveWorkbook.Save<o></o>
<o></o>
I’m not sure if this will have to be rewritten or just minoradjustments but I would like it short as possible and versital so the VBAbuttons have pretty much same code, I believe in Miracles<o></o>
<o></o>
<o></o>
Here is brief description of the Workbook and the 2 sheetsin the workbook that I’m working on, on the first sheet “Report Selection” Ihave around 20-30 VBA buttons setup with different Report names on them , Mysecond sheet “Completed Reports” which has the 20-30 reports saved on it.<o></o>
1st step Atrandom if any of the 20-30 Report VBA Buttons are pressed on “Report Selection”sheet. Lets just say I press VBA Button “Report #8” and I have “Report #6” openon “Report Selection” sheet.I need tocopy “report #6” from “Report Selection” sheet and paste it over “report #6” onthe “Completed Reports” sheet. <o></o>
The location of “report #6” on” Completed reports” sheet isV225:AO297. I have put the range of “Reports #6” In the cell AJ225 on the“Completed Reports” and all other Reports have similar cell to tell VBA buttonwhere to paste completed report <o></o>
<o></o>
2nd step “Report Selection” sheetresets cells on page due to other reportshave merged cells that sometimes causes problems with new report being pastedover existing merged cells not lining up<o></o>
3rd stepCopy “Report #8” From “Completed Reports” and Paste to D16 on “ReportSelection”<o></o>
4Th ActiveWorkbook.Save<o></o>
<o></o>
I’m not sure if this will have to be rewritten or just minoradjustments but I would like it short as possible and versital so the VBAbuttons have pretty much same code, I believe in Miracles<o></o>
<o></o>
<o></o>
Code:
[SIZE=3][COLOR=#000000][FONT=Calibri]Application.ScreenUpdating = False<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]ActiveSheet.Unprotect<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]Range("D16:W88").Select<o:p></o:p>[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.CutCopyMode = False<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Selection.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Completed Reports").Select[/COLOR][/SIZE][SIZE=3][COLOR=#000000] <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("????????").Select[/COLOR][/SIZE][/FONT][FONT=Wingdings][FONT=Wingdings][SIZE=3][COLOR=#000000]ß[/COLOR][/SIZE][/FONT][/FONT][SIZE=3][COLOR=#000000][FONT=Calibri]the range of “Reports #6” is In the cell AJ225 on the “Completed Reports” and allother Reports have similar cell to tell VBA button where to paste completedreport<o:p></o:p>[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Selection.Range("????????").Paste[/COLOR][/SIZE][/FONT][FONT=Wingdings][FONT=Wingdings][SIZE=3][COLOR=#000000]ß[/COLOR][/SIZE][/FONT][/FONT][SIZE=3][COLOR=#000000][FONT=Calibri]<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]Sheets("Report Selection").Select<o:p></o:p>[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("D16:W88").Select<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.CutCopyMode = False<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Selection.ClearContents<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With Selection<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Orientation = 0<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].AddIndent= False<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].ReadingOrder = xlContext<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].MergeCells = True<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("D89:W89").Select<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Selection.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("D16:W88").Select<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.Paste<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
[SIZE=3][COLOR=#000000][FONT=Calibri]Sheets("Completed Reports").Select<o:p></o:p>[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("A1:T73").Select [/COLOR][/SIZE][/FONT][FONT=Wingdings][FONT=Wingdings][SIZE=3][COLOR=#000000]ß[/COLOR][/SIZE][/FONT][/FONT][SIZE=3][COLOR=#000000][FONT=Calibri]Copy “Report #8” From “Completed Reports” and Paste to D16 on “ReportSelection”
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][SIZE=3][COLOR=#000000]Application.CutCopyMode = False<o:p></o:p>[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Selection.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][SIZE=3][COLOR=#000000][FONT=Calibri] <o:p></o:p>[/FONT][/COLOR][/SIZE][/COLOR][/SIZE][/FONT][SIZE=3][COLOR=#000000][FONT=Calibri]Sheets("Report Selection").Select<o:p></o:p>[/FONT][/COLOR][/SIZE]
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("D16").Select<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.Paste<o:p></o:p>[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Calibri]ActiveSheet.Protect<o:p></o:p>[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Calibri]Application.ScreenUpdating = True
ActiveWorkbook.Save[/FONT][/COLOR][/SIZE]
Last edited by a moderator: