I am not sure if this is possible, but I am trying to copy / paste and link the cells to the highlight color of the cells in different tabs. Similar to Paste Special where when you copy cells from one tab and link them to another tab, I would like to do that and also include the highlight color. Meaning, if I copy cells A1 - G1 in Tab X, which were highlighted RED, and paste them to cells A5 - G5 in Tab Y, and if I change the highlight color of them on either tab, then they both change to that highlight color simultaneously. Not sure if this could be possible with a Macro or a feature in Paste Special I am unaware of, but would greatly appreciate any assistance.
Posts by ccsmedia
-
-
Code
Sub Maybe() Dim lr As Long lr = Cells(Rows.Count, 1).End(xlUp).Row Range("A6:K" & lr).Copy Sheets("Fixed Invoices").Cells(Rows.Count, 1).End(xlUp).Offset(1) End Sub
Change the "Fixed Invoices" sheet name as required.Thank you Jolivanes, but that code did not work.
-
This line of Select_All()
CodeActiveSheet.Range(Selection, Selection.End(xlDown)).Select
results in 1048571 rows being selected for copy, and there aren't that many rows available to paste to.
I tried this and it works. I wouldn't think the 5 blank rows being pasted at the bottom would matter.Code
Display MoreSub Select_ALL() ' ' Select_ALL Macro ' ' Keyboard Shortcut: Ctrl+Shift+A ' Range("A6").CurrentRegion.Offset(5).Select End SubThank you, that could also work, however is there any possibility of it not selecting the bottom five blank rows?
-
Hello,
I am having an issue with my macro. It says "You can't paste this here because the Copy area and paste area aren't the same size."
The object of my macro:
When receiving a new list of invoices, because of how Excel extracts the info from our software, it repeats the information twice or more, but without the ITEM ID. Therefore, I have a macro that selects all the info from Row 6 and on, copies everything in the NEW INVOICES sheet, pastes only the rows with an ITEM ID at the bottom of the previous sheet, then deletes the NEW INVOICES sheet.
Problem:
When there are only two rows (one that is a duplicate with a blank in the ITEM ID), I receive the aforementioned error. However, when there are multiple rows with ITEM IDs, it works correctly.
Here is my Macro's code:
Sub TransferNewRegister()
Dim iRow As Long
For iRow = 1000 To 7 Step -1
If Cells(iRow, "J") = "" Then
Rows(iRow).Delete
End If
Next iRow
Application.Run "'2018 Processed_Invoices.xlsm'!Select_ALL"
Selection.Copy
ActiveSheet.Previous.Select
Application.Run "'2018 Processed_Invoices.xlsm'!BottomCell"
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=True, Transpose:=False
ActiveSheet.Next.Delete
End SubHERE is a sample of the spreadsheet for your perusal:
[ATTACH]n1205178[/ATTACH]
Any help is appreciated.
-
I have been looking for a way to create a macro that selects all the rows with the same value, but have not succeeded.
This is what I am trying to do:
I have a spreadsheet with columns A - K. Column "I" is the "Advertising Agency". After filtering this column A-to-Z, I would like to create a macro to highlight the next rows with the same agency name.
EX:
Invoice # ADVERTISING AGENCY
______|__|______
654654.....DELTA ]
129564.....DELTA ]------ I want to highlight these rows ("DELTA"), then, after I move them, be able to do the same with the next agency ("KINETIC")
132132.....DELTA ]
198354.....KINETIC
268841.....KINETIC
265112.....RAPPORT
357129.....RAPPORT
753965.....RAPPORT
457855.....RAPPORTAlso, I want to highlight just the ones at the top (in light blue)
Is this possible?
Here is the Spreadsheet:
image_72413.xlsx -
Hi all,
I have looked to see if anyone else is experiencing this, and I have not found anything yet. I have a long list of invoices and I am using the filter "Sort A-Z" to sort them numerically. This works fine for the cells with all numbers in it, however some invoices have letters after it (EX: 0518172-PR). You would think that after invoice 0518171, the invoice 0518172-PR would appear below it. Instead, it drops all the way to the bottom (after 0518269 [pictured below]), which makes NO sense! I have tried formatting the cells as numbers and text, and I am still seeing the same results. Does anyone know how I can get this sorted properly? Is there another method to sort numerically? Any help is appreciated. Thank you.[IMG2=JSON]{"data-align":"none","data-size":"full","height":"486","width":"269","src":"https:\/\/www.ozgrid.com\/forum\/filedata\/fetch?filedataid=72206&type=thumb"}[/IMG2][IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.ozgrid.com\/forum\/core\/image\/gif;base64,R0lGODlhAQABAPABAP\/\/\/wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw=="}[/IMG2][IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.ozgrid.com\/forum\/core\/image\/gif;base64,R0lGODlhAQABAPABAP\/\/\/wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw=="}[/IMG2][IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/www.ozgrid.com\/forum\/core\/image\/gif;base64,R0lGODlhAQABAPABAP\/\/\/wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw=="}[/IMG2]
-
Display More
Hi,
First thing that Google found:
Please, use Upload Attachments icon on the right side to upload your workbook...
s.
Thank you so much! I figured out what you meant. Thank you all for your help! I appreciate it!
-
Hello,
I have been searching for a solution, and can not seem to find it.
Situation: I process a list of monthly invoices (via Excel) and every day, new invoices are added to the list. I have to process them by advertiser, so I created a Master list, and then separate tabs for each advertiser. Then, I created a macro to select a specific range of cells to copy all from the Master List to each of the tabs, followed by filtering them to their names according to the corresponding tab (I.E: the tab "DIAGEO" filters all the advertisers with "DIAGEO" in it)
Issue: My macro only works for the name of the current worksheet "...January_Processed..." because it is in the coding. When I try this macro in my "February_Processed" worksheet, it debugs and requires me to change the name of the worksheet in the coding. I would like to be able to use it for ALL worksheets, like a template. I highlighted the issue in RED. Is this possible? Any help is appreciated.
My Codes:
[tr]
[TABLE="border: 1, cellpadding: 1, width: 500"]
[td]Sub Select_ALL()
[/td]
'
' Select_ALL Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
End Sub
[/tr]
[tr]
[/TABLE]
[TABLE="border: 1, cellpadding: 1, width: 500"]
[td]Sub Copy_Paste()
[/td]
'
' Copy_Paste Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Application.Run "'01-January_Processed_Invoices (MACRO).xlsm'!Select_ALL"
Selection.Copy
Sheets("BILLUPS").Select
Range("A6").Select
Sheets("DAVIS ELEN").Select
Range("A6").Select
Sheets("DIAGEO").Select
Range("A6").Select
Sheets("HAWORTH").Select
Range("A6").Select
Sheets("KERWIN").Select
Range("A6").Select
Sheets("KINETIC").Select
Range("A6").Select
Sheets("OMD Media (Disney)").Select
Range("A6").Select
Sheets("OMG ").Select
Range("A6").Select
Sheets("POSTERSCOPE").Select
Range("A6").Select
Sheets("PROJECT X").Select
Range("A6").Select
Sheets("PRUDENTIAL").Select
Range("A6").Select
Sheets("RAPPORT").Select
Range("A6").Select
Sheets("SWK").Select
Range("A6").Select
Sheets("WB").Select
Range("A6").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("ALL (MASTER)").Select
Application.Run "'01-January_Processed_Invoices (MACRO).xlsm'!Select_ALL"
Selection.Copy
Sheets("BILLUPS").Select
ActiveSheet.Paste
Sheets("DAVIS ELEN").Select
ActiveSheet.Paste
Sheets("DIAGEO").Select
ActiveSheet.Paste
Sheets("HAWORTH").Select
ActiveSheet.Paste
Sheets("KERWIN").Select
ActiveSheet.Paste
Sheets("KINETIC").Select
ActiveSheet.Paste
Sheets("OMD Media (Disney)").Select
ActiveSheet.Paste
Sheets("OMG ").Select
ActiveSheet.Paste
Sheets("POSTERSCOPE").Select
ActiveSheet.Paste
Sheets("PROJECT X").Select
ActiveSheet.Paste
Sheets("PRUDENTIAL").Select
ActiveSheet.Paste
Sheets("RAPPORT").Select
ActiveSheet.Paste
Sheets("SWK").Select
ActiveSheet.Paste
Sheets("WB").Select
ActiveSheet.Paste
' REAPPLY_FILTERS Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Sheets("BILLUPS").Select
ActiveSheet.Range("$A$5:$I$2011").AutoFilter Field:=9, Criteria1:= _
"=*BILLUPS*", Operator:=xlAnd
Sheets("DAVIS ELEN").Select
ActiveSheet.Range("$A$5:$I$2011").AutoFilter Field:=9, Criteria1:= _
"=*DAVIS*", Operator:=xlAnd
Sheets("DIAGEO").Select
ActiveSheet.Range("$A$5:$I$2011").AutoFilter Field:=9, Criteria1:= _
"=*DIAGEO*", Operator:=xlAnd
Sheets("HAWORTH").Select
ActiveSheet.Range("$A$5:$I$2011").AutoFilter Field:=9, Criteria1:= _
"=*HAWORTH*", Operator:=xlAnd
Sheets("KINETIC").Select
ActiveSheet.Range("$A$5:$I$1998").AutoFilter Field:=9, Criteria1:= _
"=*KINETIC*", Operator:=xlAnd
Sheets("KERWIN").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:= _
"=*KERWIN*", Operator:=xlAnd
Sheets("OMD Media (Disney)").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:="=*OMD*" _
, Operator:=xlAnd, Criteria2:="<>*OMG*"
Sheets("OMG ").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:="=*OMG*" _
, Operator:=xlAnd
Sheets("POSTERSCOPE").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:= _
"=*POSTERSCOPE*", Operator:=xlAnd
Sheets("PROJECT X").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:= _
"=*PROJECT*", Operator:=xlAnd
Sheets("PRUDENTIAL").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:= _
"=*PRUDENT*", Operator:=xlAnd
Sheets("RAPPORT").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:= _
"=*RAPPORT*", Operator:=xlAnd
Sheets("SWK").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:="=*SWK*" _
, Operator:=xlAnd
Sheets("WB").Select
ActiveSheet.Range("$A$5:$I$2001").AutoFilter Field:=9, Criteria1:= _
"=*WARNER*", Operator:=xlOr, Criteria2:="=*WB*"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("ALL (MASTER)").Select
End Sub
[/tr]
[/TABLE]