VBA copy paste currentRegion historically to another worksheet

[cre8tivgamertag]

I'm getting my butt kicked and the different versions of this across functions has me fried. I have 2 sheets, one a source called "QC" and another intended to grab a copy of the data per QC operation, and paste it on the next open row to create a historical record of all the times the first sheet is filled in. I can get a basic copy and paste, and even get them to add row to append to the bottom of the list. When I try to use CurrentRegion to grab only the data from A3: L I'm breaking the whole thing somehow. I was hoping the CurrentRegion would be my solution to only grab as much data is filled in, 1 line, 15 lines, and paste it to the next open row on "Historical"

Any help would be greatly appreciated, I would post what I had but deleted in a fit of rage...

 

[vcoolio]

Hello,

I'm assuming that you don't want or need the headings rows (1 and 2) copied so, with your current code, when using the CurrentRegion method, the headings will be copied as well. CurrentRegion requires at least one empty row and one empty column bounding the data set for it to work. You have the blank column(M) but not a blank row at the top of your data set.
However, try the following instead:-

Sub Test()
    
Application.ScreenUpdating = False

    With Sheet1.UsedRange.Offset(2).Resize(, 12)
            .Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
    End With

Application.ScreenUpdating = True

End Sub

I've used sheet codes (Sheet1 and Sheet2) above instead of sheet names so you'll need to check in the Project Explorer if these are the correct sheet codes.

I hope that this helps.

Cheerio,
vcoolio.

 

[Alex Blakenburg]

I am reading your requirement a bit differently to vocoolio and I don't think that either CurrentRegion or UsedRange will work for you.
If you are happy to assume that for a row to be eligible to be moved to history it has to have a date in column B then something like the below might work for you.
If not then I would probably switch to using find in columns B:L but see if this works first.

Sub GetInputArea()

    Dim shtQC As Worksheet, shtHist As Worksheet
    Dim rowLastQC As Long, rowNextHist As Long
    Dim rngQC As Range, rngHist As Range
    
    Application.ScreenUpdating = False
    
    Set shtQC = Worksheets("QC")
    Set shtHist = Worksheets("Historical")                      '<--- Change to correct sheet name
    
    With shtQC
        rowLastQC = .Range("B" & Rows.Count).End(xlUp).Row      ' Assumes that a valid row will always have a date
        Set rngQC = .Range("A3:L" & rowLastQC)
    End With
    
    With shtHist
        rowNextHist = .Range("A" & Rows.Count).End(xlUp).Row + 1
        Set rngHist = .Range("A" & rowNextHist)
    End With

    rngQC.Copy
        rngHist.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    Application.ScreenUpdating = True
End Sub

 

[cre8tivgamertag]

Both astute solutions, Originally I populated the date both for the date of each line to pull and in hopes of an IF condition to only pull those lines, even tried a ISTEXT and TRUE but got rid of that too. I will reformat the source sheet with a blank row, already thought to do that in a column and spaced the row, hence my frustration I think. Hoping the current region works this way as I have not used it before and just think its a cool function. Lets see what I can break!

 

[cre8tivgamertag]

Adjusted both to work and do what I need, my OCD hurts a bit copying blank rows over and I have no concerns of hitting a cell limit like google so its moot. Out of sheer curiosity, with a blank row now formatted at the top, what would an example of a Current region look like, just want to learn what I was doing wrong and reverse engineer a bit if anyone has the time. Thank you both a ton already as well!

 

[Alex Blakenburg]

A blank row or blank column is effectively the border surrounding the current region.
If you click on an non-empty cell and press ctrl+* which on most keyboards means ctrl+shift+8, it will select the current region relative to that cell.
That is what VBA current region will return.

It is an area surrounding a point of reference, so a sheet can have multiple current regions.