Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

VBA Copy Paste

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
jmac001
Asked:
jmac001
  • 5
  • 4
1 Solution
 
Rgonzo1971Commented:
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
 
SteveCommented:
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
 
jmac001Author Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SteveCommented:
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
 
jmac001Author Commented:
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
 
SteveCommented:
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
 
jmac001Author Commented:
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
 
SteveCommented:
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
 
jmac001Author Commented:
Steve, this is actually how I would like it to work. Thank you
0
 
SteveCommented:
Excellent, happy you have it sorted :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now