Copy/Paste VBA Excel

[Tord]

Hi,

I want to copy a range to a new sheet, say Sheet2. The range has a fixed column number but varying row numbers. I copy this into a sheet with a specific address, say A1. Then I want to copy Range2 into the same sheet, Sheet2, in Column A, after where Range has been copied in, and in the next +1 empty cell Range is similiar as range 1, fixed number of columns, but varying number of Rows.

I manage everything apart from pasting Range2 into the row. I think it is difficult to copy into both a new sheet with a varying cell. If you can help with some ways of making a variable with Cells/range address, it may help!

Thanks a lot!

[Hack]

Welcome to DevX

I have an application that I just rolled out that goes through an entire folder of spreadsheets, opens each on in turn, goes to specific locations in the newly opened spreadsheet, copies a number, and pastes it into a "summary" sheet at a new cell location on the summary sheet. The new location gets calculated based on the last row that was copied in. Perhaps you can take this an modify it to fit your needs. SummarySheet and WorkingFile as Workbook variables created prior to running this code. intRow is initially set at 4 because that is where I want to pasting to start. The actual copy/paste code is done in a loop (the loop is going through the folder opening and closing each spreadsheet file) and gets incremented by 1 each time through the loop.

Dim intRow As Integer

'lots of setup code and the beginning of the Do While loop that runs this entire process

'the copy/paste part
WorkingFile.Sheets("HFR Setting").Range("I10").Copy  'ip operating cost
SummarySheet.Sheets("Sample Output").Range("C" & intRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False

Hope this helps.

[Tord]

Thanks, Hack

I'm almost there. It works, but my last part (Plate) is inserted further and further down on the sheet for each time I press button 1.

Can you please have a look at my code:

Sub Level1Insert_Button1_Click()
Sheets("Test").Cells.ClearContents
Call Cylinder
Call Plate
End Sub

Private Sub Cylinder()
CylNo = Range("a1").Value
CylEndRow = 5 + CylNo - 1
Range(Cells(3, 1), Cells(CylEndRow, 3)).Copy
Range("Test!a1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Private Sub Plate()
PlNo = Range("d1").Value
PlEndRow = 5 + PlNo - 1
lngLastRow = 1
lngLastRow = Sheets("Test").Cells.SpecialCells(xlCellTypeLastCell).Row
'NewAdd = "Test!" & "a" & lngLastRow + 2
MsgBox lngLastRow
MsgBox NewAdd
Range(Cells(3, 4), Cells(PlEndRow, 6)).Copy
Sheets("Test").Range("A" & lngLastRow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Probably unneccessary complicated... but thats how I manage it. a1 and d1 gives the number of rows, found by Excel function count. CylEndRow and PlEndRow finds the number of rows I want to copy (includes some text).

Grateful if you can help me more with this code..

[Hack]

After adding [code]your code goes here[/code] tags to make things a bit easier to read, I noticed that you are incrementing a row count by 5 each time you run your Plate sub. Why is that?