Solved

VBA Copy Paste

Posted on 2013-05-28
10
187 Views
Last Modified: 2013-06-03
I modified some code that I had in another workbook, works but I haven't been able to figure out how to modify it to only paste the data from columns B:P (Template) into the new worksheet.  Also would prefer to capture the data from the last row with data.  Currently getting all rows with formatting

Sub FileCopy()
Dim Rowcount As Integer
Dim DataRowCount As Integer


'These 2 lines create variables for your 2 sheets.

    Set MFile = ThisWorkbook.Worksheets("ShipDoc") 'ActiveWorksheet
    Set OFile = ThisWorkbook.Worksheets("Template")
     
    OFile.Activate
    Rowcount = Application.CountA(ActiveSheet.Range("B:P")) 'How many rows contain data
     Rows("2:" & Rowcount).Select
        Selection.Copy
    
    MFile.Activate
    
    DataRowCount = Application.CountA(ActiveSheet.Range("B:B")) 'How many rows contain data in this sheet
    Cells(DataRowCount + 5, 1).Select
    ActiveSheet.Paste
    Range("A1").Select
    


End Sub

Open in new window

0
Comment
Question by:jmac001
  • 5
  • 4
10 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39203626
Hi,

Pls try

Sub FileCopy()
Dim Rowcount As Integer
Dim DataRowCount As Integer


'These 2 lines create variables for your 2 sheets.

    Set MFile = ThisWorkbook.Worksheets("ShipDoc") 'ActiveWorksheet
    Set OFile = ThisWorkbook.Worksheets("Template")
     
    OFile.Activate
    Rowcount = Application.CountA(ActiveSheet.Range("B:B")) 'How many rows contain data ' I've modified this
    Range("B" & Rowcount & ":P" & Rowcount).Select  ' I've modified this
        Selection.Copy
    
    MFile.Activate
    
    DataRowCount = Application.CountA(ActiveSheet.Range("B:B")) 'How many rows contain data in this sheet
    Cells(DataRowCount + 5, 2).Select ' I've modified this
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' I have ve modified this
    Range("A1").Select
    


End Sub

Open in new window

Regards
0
 
LVL 24

Expert Comment

by:Steve
ID: 39203774
you could try the following which eliminates the clipboard copy all together:

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")

    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("B" & DataRowCount + 5 & ":P" & DataRowCount + 3 + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

End Sub

Open in new window


The following does just the last row (not all data):
    MFile.Range("B" & DataRowCount + 5 & ":P" & DataRowCount + 5).Value = _
        OFile.Range("B" & Rowcount & ":P" & Rowcount).Value

Open in new window

0
 

Author Comment

by:jmac001
ID: 39204718
The_Barman thanks that is what I was looking for.  Two questions 1. how do I maintain the formating from the Template to ShipDoc worksheet?  2. users will need to re-populate the information I know that you can clear contents but I have header information how do I clear/delete from A6 down.  Thanks
0
 
LVL 24

Expert Comment

by:Steve
ID: 39205018
To clear contents, just use the function after transfering the data:

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")

    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("B" & DataRowCount + 5 & ":P" & DataRowCount + 3 + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

        OFile.Range("B6:P" & Rowcount).ClearContents

End Sub

Open in new window


To maintain the formatting would require either code to format the new data after moving it. Or just to copy the formatting over.
Is it not possible to format the whole columns in the destination sheet so that the format is in place before the data agets there?
0
 

Author Comment

by:jmac001
ID: 39205165
Receive and error 1004 Method 'Range' of object_'Worksheet' failed.  Switch the sheets to be cleared after the first run will need to have the MFile worksheet cleared and repopulated.

Here is the code

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

    


'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")
    
    MFile.Range("B6:O" & Rowcount).Clear
    
    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("A" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("A" & DataRowCount + 1 & ":O" & DataRowCount + (-1) + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

End Sub

Open in new window


The formatting would be cleared with the vba.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Expert Comment

by:Steve
ID: 39206792
The line:
MFile.Range("B6:O" & Rowcount).Clear
Is before the move of the data... is this what you wanted?
It is also before the line:
Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
So the reason for the error is that RowCount does not have any value...
It is also RowCount that relates to Ofile rather than Mfile

1) You should clear the sheet after transfering the data,
2) use clearcontents rather than clear and this will maintain formatting.
3) You must give RowCount a value before using it.

Somthing like:
Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

    


'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")

    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("A" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("A" & DataRowCount + 1 & ":O" & DataRowCount + (-1) + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

    OFile.Range("B6:O" & Rowcount).ClearContents   ' ClearContents of Template, leaving Formatting in place

End Sub 

Open in new window


If you could provide a step by step detailed description of the result of the code I would be more than happy to write the full code to acheive your goal. If you have an example workbook it could help too if you could post it too.
Thanks, Steve.
0
 

Author Comment

by:jmac001
ID: 39211949
Hi Steve,

What I would like to happen is the the first time the "Create Button" is clicked on the ShipDoc worksheet is to copy of the data from the Template Worksheet.   The next time the "Create" button is clicked I would like all of the data from A6 down to be cleared from the ShipDoc worksheet and the all the data from the Template worksheet be populated into the ShipDoc worksheet
Parts-Tracker-Template---Copy.xlsm
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 39215451
The attached file has the following code which should be a step in the right direction:

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("SHIPDOC")
    Set OFile = ThisWorkbook.Worksheets("TEMPLATE")

'Count how many rows contain data in ShipDoc (not needed)...
    'DataRowCount = MFile.Range("A" & Rows.Count).End(xlUp).Row
   
'clear the contents of ShipDoc from row 6 to the end of the data...
    MFile.Range("6:" & Rows.Count).Delete   ' Delete Contents of SHIPDOC

'Count how many rows contain data in Template
    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row

'transfer data from Template to Shipdoc (starting at row 6)
    MFile.Range("A" & 6 & ":O" & Rowcount + 6 + (-2)).Value = _
        OFile.Range("B2:P" & Rowcount).Value

'Transfer fromatting from first line of template to SHIPDOC
    OFile.Range("B2:P2").Copy
    MFile.Range("A" & 6 & ":O" & Rowcount + 6 + (-2)).PasteSpecial Paste:=xlPasteFormats

End Sub

Open in new window


See attached workbook...

Is this how you would like the finished article to look?
Do you need the date filling in or the Store number?
Take a look and see how this suits and feel free to ask for changes :)

ATB
Steve.
Parts-Tracker-Template---Copy.xlsm
0
 

Author Closing Comment

by:jmac001
ID: 39216950
Steve, this is actually how I would like it to work. Thank you
0
 
LVL 24

Expert Comment

by:Steve
ID: 39217386
Excellent, happy you have it sorted :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now