I have the following code below as well as the attached excel file. I would appreciate any help on the following problems I have:
1) How would I copy over the rows of data from market data to template data and ensure the formatting is kept the same? (compact rows where everything fits and is adjusted)
2) If market data and template data were in two separate workbooks, how would I change the code to open up market data workbook copy over the rows of data and then close the market data workbook?
3) How can I copy the rows but run a formula on cells T4 to Y4 in market data so the the currency is converted to EUR (divide all these cells by FX value in Column E) and then copied over to template data? Unfortunately, I can't run this conversion first as the data is going to be used in this format and so the conversion will have to be done throughout the macro process.
Any help is massively appreciated, thank you in advance!
Option Explicit Sub Filter_Copy_Paste() Dim lr1 As Long Dim lr2 As Long With Sheets("Market Data") lr1 = .Cells(Rows.Count, 1).End(xlUp).Row 'last used row in sheet Market Data .AutoFilterMode = False .Range("A3:Y" & lr1).AutoFilter Field:=3, Criteria1:="ALBANIA" 'filter range on ALBANIA lr2 = Sheets("Template Data").Cells(Rows.Count, 1).End(xlUp).Row + 1 'first unused row in sheet Template Data .Range("E4:Y" & lr1).SpecialCells(xlCellTypeVisible).Copy Sheets("Template Data").Range("A & lr2") 'Copy/Paste .AutoFilterMode = False End With Application.CutCopyMode = False End Sub