Copying data from one workbook to another. This is a continued working example that started in thread: http://www.excelforum.com/excel-prog...o-problem.html
What I am noticing is that there is a range in the first column that can probably go inside of a loop for the copying and pasting process below. How could the Range of ID#'s be copied?
Lets say the range is 1 to 100. Would it be best to use a loop? How would the loop for such a thing look?
Private Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook
'Dim wb1Rng As Range, wb2Rng As Range ''1 column to copy from and to
Dim wb1Rng As Range, wb2Rng As Range, wb3Rng As Range, wb4Rng As Range ''2 columns to copy from and to
Dim rCnt As Long, cCnt As Long
Dim colCnts As Long
Set wb1 = Workbooks("Data File.xlsx") ''This name represents your book to copy from
Set wb2 = Workbooks("Insertion File.xlsm") ''This name represents your book to copy to
''''''''''''''''''''''''''''''''''''''
'''wb1Rng is the range to be copied'''
'''This finds the last column used '''
'''on the sheet so if the range is '''
'''say T8:Z8, it will set it that '''
'''way. To keep this localized to '''
'''your original requirements, use '''
'''the following commented-out line'''
'''instead of the active line. '''
Set wb1Rng = wb1.Sheets(1).Range("C3:D3") ''A range of columns
'Set wb1Rng = wb1.Sheets(1).Range("T8:W8")
'Set wb1Rng = wb1.Sheets(1).Range("T8") ''A single cell of the range
'Set wb1Rng = wb1.Sheets(1).Range("T8", wb1.Sheets(1).Cells(8, Columns.Count).End(xlToLeft))
Debug.Print wb1Rng.Address
'''''''''''''''''''''''''''''''
'''Set the number of columns'''
colCnts = wb1Rng.Columns.Count
''''''''''''''''''''''''''''''''''''''
'''Set the size of the range to be '''
'''copied to. '''
rCnt = wb2.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row + 1
Set wb2Rng = wb2.Sheets(1).Range(wb2.Sheets(1).Cells(rCnt, 1), wb2.Sheets(1).Cells(rCnt, colCnts))
'Set wb2Rng = wb2.Sheets(1).Range(wb2.Sheets(1).Cells(Rows.Count, 2).End(xlUp), wb2.Sheets(1).Cells(Rows.Count, 2).End(xlUp)).Offset(1, colcnt)
'''Col 2, Row 2
Debug.Print wb2Rng.Address
'''''''''''''''''''''
'''Copy the values'''
wb2Rng.Cells.Value = wb1Rng.Cells.Value
'''DUPLICATION for Multiple Copies'''
'''Second cell of data for copy
'Set wb3Rng = wb1.Sheets(1).Range("U8")
'Debug.Print wb3Rng.Address
'Set wb4Rng = wb2.Sheets(1).Range(wb2.Sheets(1).Cells(Rows.Count, 3).End(xlUp), wb2.Sheets(1).Cells(Rows.Count, 3).End(xlUp)).Offset(1, colcnt)
'Debug.Print wb4Rng.Address
'wb4Rng.Cells.Value = wb3Rng.Cells.Value
'''End of DUPLICATION'''
End Sub
Also, something that I need help with in this example:
Set wb2Rng = wb2.Sheets(1).Range(wb2.Sheets(1).Cells(rCnt, 1), wb2.Sheets(1).Cells(rCnt, colCnts))
WHen I change .Cells(rCnt, 2) to 2 instead of 1 it will move over the copied data but only one column of the two shows up. When I try 4, both columns show up moved over exactly where I want them but then a NA# shows up in the blank column to their right.
Bookmarks