Copying rows to a specific cell

Spice. · Aug 3rd 2022

Hey,

How do I go about changing the following code so that the data copied over is copied starting from a specific cell and not just the first unused row in the sheet. Any help would be appreciated. I have attached the example workbook, but say I wanted to data to copy into the Template Data sheet but only starting from A20 on the sheet, how do I do that.

Thanks in advance.

Code

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

Display More

Logit · Aug 3rd 2022

Does this work ?

Code

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("A20") 'Copy/Paste
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub

Display More

Spice. · Aug 5th 2022

Thank you Logit. Could you help on a couple more problems I have if possible please. I've attached the excel sheet and copied the code again here.

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.

Code

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("A20") 'Copy/Paste
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub

Display More

Jimmywick · Aug 6th 2022

Select the row or column that you want to move or copy. or press Ctrl+C. Select the upper-left cell of the paste area or the cell where you want to paste the value, cell format, or formula.

rollis13 · Aug 6th 2022

Logit, at least we gave the same answer (me first ) HERE

Copying rows to a specific cell

Spice. Aug 5th 2022

Participate now!

Share

Similar Threads

Have a VBA String-Array To Point to a Specific Cell of Another String Array

Copying data from a specific folder to a master workbook and then plotting the data

Adding rows & copying formulas above

VBA - How to exclude rows from search results if a cell is = to a specific value

Macro to hide and unhide rows depending on value in a precise cell

Command Button to paste data from specific Cells of Specific sheet to specific Cells of Specific sheet