Copy rows from two sheet into one with vba

%3CLINGO-SUB%20id%3D%22lingo-sub-3092608%22%20slang%3D%22en-US%22%3ECopy%20rows%20from%20two%20sheet%20into%20one%20with%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092608%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20two%20worksheets%20with%20columns%20A%20-%20Q%20to%20a%20maximum%20row%20500%20that%20are%20populated%20by%20formulas%2C%20however%20many%20of%20the%20rows%20will%20be%20blank%20in%20most%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20some%20VBA%20that%20will%20copy%20data%20from%20these%20two%20worksheets%20(ITDepExport%20and%20ITRepExport)%20to%20the%20next%20available%20row%20on%20another%20worksheet%20(TEST).%20The%20data%20in%20TEST%20will%20be%20uploaded%20to%20a%20web%20app%20and%20cannot%20contain%20any%20of%20the%20formatting%20or%20formulas%20in%20the%20source%20worksheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20found%20that%20using%26nbsp%3B.PasteSpecial%20xlPasteValues%20does%20not%20work%20as%20the%20web%20app%20is%20still%20identifying%20something%20in%20the%20blank%20cells.%20If%20I%20use%20Ctrl%20%2B%20up%20or%20down%20arrow%20it%20also%20moves%20to%20a%20cell%20on%20row%20500%20so%20something%20is%20being%20copied.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20method%20of%20copying%20below%20works%2C%20but%20only%20for%20one%20sheet%20and%20are%20not%20pasting%20to%20the%20next%20available%20row.%20Could%20anyone%20help%20with%20modifying%20these%20or%20providing%20another%20method%20using%20VBA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20KopyKat()%0AWith%20Worksheets(%22ITDepExport%22)%0AWorksheets(%22TEST%22).Range(.UsedRange.Address).Cells.Value2%20%3D%20.UsedRange.Value2%0AEnd%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3092608%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello, I have two worksheets with columns A - Q to a maximum row 500 that are populated by formulas, however many of the rows will be blank in most cases.

 

I am looking for some VBA that will copy data from these two worksheets (ITDepExport and ITRepExport) to the next available row on another worksheet (TEST). The data in TEST will be uploaded to a web app and cannot contain any of the formatting or formulas in the source worksheets.

 

So far I have found that using .PasteSpecial xlPasteValues does not work as the web app is still identifying something in the blank cells. If I use Ctrl + up or down arrow it also moves to a cell on row 500 so something is being copied.

 

The method of copying below works, but only for one sheet and are not pasting to the next available row. Could anyone help with modifying these or providing another method using VBA?

 

 

 

 

Sub KopyKat()
With Worksheets("ITDepExport")
Worksheets("TEST").Range(.UsedRange.Address).Cells.Value2 = .UsedRange.Value2
End With
End Sub

 

 

0 Replies