VBA Copy and Paste data form one workbook to another.. Problem is data change every w

[edmundchant]

Hi All I am new to VBA excel, currently I am doing a major project. For this project, every week I will receive new data updates from information company about the percentage of emails we have collected.
My purpose is to create codes so that immediately after I receive the excel from the information company I can run the codes to copy the set of data into my master list.

However the problem is that every week the excel column change and I have to only paste 3 specific columns. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image). However, I do not know how or where to start.

Capture.jpg

Below is the codes which I have done so far:

Private Sub transferSpecificData()
    Dim percentagecollected As Single
    Dim noofemail As Single
    Dim totalemail As Single
    Dim monthlytransferdata As Workbook
    
    Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")
    
    Worksheets("Email_AR NTB (3)").Select
    percentagecollected = Range("H:H")
    
    Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
    
    Worksheets("Branch NTB AR").Select
    Worksheets("Branch NTB AR").Range("A1").Select
    
    RowCount = Worksheets("Branch NTB AR").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("branch NTB AT").Range("A1")
    .Offset(RowCount, 0) = percentagecollected
    .Offset(RowCount, 1) = noofemail
    
    End With
    
    monthlytransferdata.Save
    
End Sub

But it gives back error: Run Time error 13: Type mismatch
&
another problem it does not allow me to actually specify which column and row I want to extract my data from.

Any help is very much appreciated. Thank you!!

[Paul_Hossler]

1. I think it'd help a lot if you attached a sample of the input workbook and your macro workbook

2. Your approach is not very general purpose, and I think the overall structure needs improvement

3. Guessing as to which line is causing an error (since you didn't tell)

Dim percentagecollected As Single

.....
percentagecollected = Range("H:H")

percentagecollected is Dim-ed as Single

If you want it to be a Range, then

Dim percentagecollected As Range

.....
Set percentagecollected = Range("H:H")

If you want it to be the column number, then

Dim percentagecollected As Long

.....
percentagecollected = Range("H:H").Column

[edmundchant]

Hi Paul,

1. I have given a screenshot of what the input workbook and the master list looks like.

2. "Your approach is not very general purpose, and I think the overall structure needs improvement" Sorry I am new to VBA not sure what you mean by not general purpose. Can you provide insights as to how I can improve the structure?

3. Oh sorry I didn't know how to debug but I have just found out how to. The error is coming from this line: Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
I have changed my percentagecollected to long as well.


Excel sheet given by information company
Capture.jpg

Master List
Capture.jpg

[edmundchant]

Excel sheet given by information company
Capture.jpg

As you can the actual data that I need to extract only starts at row 38 and the column I need varies... sometimes I need extract last 2 column sometimes last 3 column

[offthelip]

Your pictures of spreadsheets are unreadable, so it is difficult to help you. However you state:

the problem is that every week the excel column change and I have to only paste 3 specific columns.

Do the columns you need to copy always have the same header? because if they do you can get vba to automatically identify which columns to copy.
If this is not possible, a better way of getting the user to choose the right colums is to allow the user to select the columns with the mouse. This code shows you how to do this:

 
   Dim oRangeSelected As Range   
   On Error Resume Next
    Set oRangeSelected = Application.InputBox("Put a suitable message in here!", _
                                              "SelectARAnge Demo", Selection.Address, , , , , 8)
    If oRangeSelected Is Nothing Then
    Else
    
    firstrow = oRangeSelected.Row
    firstcol = oRangeSelected.Column
   
     MsgBox (firstrow & "/" & firstcol)
    
    End If

[edmundchant]

I tried to copy using the oRangeSelected but it does not work...am I doing it wrong?

Here's my codes

 
Private Sub PinPointPlace()
    Dim firstrow As Long
    Dim firstcol As Long
    
    Dim oRangeSelected As Range
    On Error Resume Next
    Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
                                              "SelectARAnge Demo", Selection.Address, , , , , 8)
    If oRangeSelected Is Nothing Then
        MsgBox "You have cancelled the function"
    Else
        
    Workbooks("GCAD Mock Data").Sheets("NTB").Range("oRangeSelected").Copy Range("A1")


    End If
    
End Sub

[Paul_Hossler]

Hi Paul,

1. I have given a screenshot of what the input workbook and the master list looks like.

2. "Your approach is not very general purpose, and I think the overall structure needs improvement" Sorry I am new to VBA not sure what you mean by not general purpose. Can you provide insights as to how I can improve the structure?

3. Oh sorry I didn't know how to debug but I have just found out how to. The error is coming from this line: Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
I have changed my percentagecollected to long as well.

1. There's no way to test a screen shot

2.

However the problem is that every week the excel column change and I have to only paste 3 specific columns. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image).

2. By 'general purpose' I meant including in the macro some identifying information for the columns to be copied and logic as to where they are supposed to go so that you don't have to rely on a userform or a user


3. There could be any number of reasons why the line fails. From this piece of your code, it appears that the macro is in a third workbook, and opens two more. Correct?

[CODE]
Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")

Worksheets("Email_AR NTB (3)").Select
percentagecollected = Range("H:H")

Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")[
/CODE]

[offthelip]

Sorry for the delay in replying I have been away.
this works, It copies the selected cells to A1 on "sheet2" Modify the name to suit your workbook

    Dim firstrow As Long
    Dim firstcol As Long
     
    Dim oRangeSelected As Range
    On Error Resume Next
    Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
    "SelectARAnge Demo", Selection.Address, , , , , 8)
    If oRangeSelected Is Nothing Then
        MsgBox "You have cancelled the function"
    Else
         
        oRangeSelected.Copy Destination:=Worksheets("Sheet2").Range("A1")
'        Workbooks("GCAD Mock Data").Sheets("NTB").Range("oRangeSelected").Copy Range("A1")
         
         
    End If

[edmundchant]

Hi Paul and offthelip,

Thank you so much for your help! Some of the codes provided were very useful in pointing me to the right path! And sorry for the delay in reply, I got caught up in other projects.

Anyway I have managed to solve the codes and will leave it here in case someone else might find it helpful in the future

Sub TransferData3()


    Dim i As Long
    Dim j As Long
    
    Dim firstrow1 As Long
    Dim firstrow2 As Integer
    Dim lastrow1 As Long
    Dim lastrow2 As Integer
    
    Dim branchname As String
    
    
    firstrow1 = Application.InputBox("Please enter the first row")
    lastrow1 = Application.InputBox("Please enter the last row")
    firstrow2 = Application.InputBox("Please enter the row to paste data")
    column1 = Application.InputBox("Please enter the column to copy data from, column:")
    column2 = Application.InputBox("to column:")
    column3 = Application.InputBox("Where to paste data:")
    column4 = Application.InputBox("Where to paste data.")
    
    For i = firstrow1 To lastrow1
    
        branchname = Sheets("NTB").Cells(i, "A").Value
        
        Sheets("Paste").Activate
        
        lastrow2 = firstrow2 + 49
        
            For j = 2 To lastrow2
        
            If Sheets("Paste").Cells(j, "A").Value = branchname Then
        
            Sheets("NTB").Activate
            Sheets("NTB").Range(Cells(i, column1), Cells(i, column2)).Copy
        
            Sheets("Paste").Activate
            Sheets("Paste").Range(Cells(j, column3), Cells(j, column4)).Select
        
            ActiveSheet.PasteSpecial (xlPasteValuesAndNumberFormats)
        
            End If
        
            Next j
        
            Application.CutCopyMode = False
        
        Next i
        
        Sheets("NTB").Activate
        Sheets("NTB").Range("A1").Select


End Sub

It isn't perfect and I would suggest using a userform instead to make things neater but I changed my project direction and decided to use a pivot table instead so I didn't bother making one.

Cheers!