DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Copy/Paste VBA Excel

  1. #1
    Join Date
    Sep 2008

    Copy/Paste VBA Excel


    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!

  2. #2
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    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.

  3. #3
    Join Date
    Sep 2008
    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()
    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..
    Last edited by Hack; 09-15-2008 at 01:25 PM. Reason: Added Code Tags

  4. #4
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    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?

Similar Threads

  1. Excel VBA - Reshow A UserForm
    By Hack in forum VB Classic
    Replies: 1
    Last Post: 09-10-2008, 02:40 PM
  2. Excel VBA - VBA Equivalent of hWnd property
    By Hack in forum VB Classic
    Replies: 4
    Last Post: 08-06-2008, 07:19 AM
  3. Replies: 0
    Last Post: 05-15-2008, 04:24 PM
  4. Replies: 1
    Last Post: 01-02-2007, 09:58 AM
  5. Workbook not Closing Properly by Excel VBA
    By blayne in forum VB Classic
    Replies: 1
    Last Post: 11-17-2005, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center