copy paste from one worksheet to other but in different columns

[Aqusmacro]

Hello I got this almost working
I need to copy info imported from one worksheet to another, but need to paste to different columns than original.
Formula below works but if column C as example is empty then info from column A will paste to next available row, B will do the same in same row but C will paste at beginning of column instead of all in same row.
basically I want to copy paste the range but different columns. Hope I am clear.

And as a second question how can I do the same thing as below but instead of adding to the last row I would like to insert at beginning of worksheet and move everything else down

Thanks

Sub Macro3()

'use for bank account

  Dim column_types() As Variant
  ChDrive "E:\"
  ChDir "E:\financialexcel\transactions\"
  csv_path = Application.GetOpenFilename()
  If csv_path = False Then
    Exit Sub
  End If
  For i = 0 To 16384
    ReDim Preserve column_types(i)
    column_types(i) = 2
  Next i
  With ActiveWorkbook.Sheets(2).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))
  .Name = "importCSVimporter"
  .FieldNames = True
  .AdjustColumnWidth = True
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = True
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = column_types
  .Refresh BackgroundQuery:=False
  End With

  ActiveWorkbook.Sheets(2).QueryTables(1).Delete
  'If this line doesn't work, try (0) instead of (1)
  
'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet

'Set variables to the worksheets
Set ws1 = Sheets("sheet2")
Set ws2 = Sheets("transactions")

'Copy
ws1.Range("A2", ws1.Range("a" & Rows.Count).End(xlUp).Offset(1)).Copy
'Paste
ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

'Copy
ws1.Range("b2", ws1.Range("b" & Rows.Count).End(xlUp).Offset(1)).Copy
'Paste
ws2.Range("c" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

'Copy
ws1.Range("c2", ws1.Range("c" & Rows.Count).End(xlUp).Offset(1)).Copy
'Paste
ws2.Range("d" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

'


Application.CutCopyMode = False

   ws1.Range("A2", ws1.Range("T" & Rows.Count).End(xlUp).Offset(1)).Delete

    
   
 End Sub

this works if I want to paste in same columns as original but I want column A from worksheet2 in column A in transactions sheet. Column B from Sheet 2 to column C in transactions sheet

'Copy
ws1.Range("A2", ws1.Range("f" & Rows.Count).End(xlUp).Offset(1)).Copy

'Paste
ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

 

[Joe4]

Not sure I follow.
I think it would be most beneficial to show use what the actual data looks like in the underlying 2 CSV files, like this:

Date,Description,Category,Debit,Credit,Balance
2024-01-02,Bill payment,Bills & Utilities,55,0,200
2024-01-02,Bill payment,Bills & Utilities,65,0,210
etc.

and then show what the final result in Excel should look like when those two underlying CSV files are combined.

Also, are they being added one at a time, on top of each other?

 

[Aqusmacro]

Not sure I follow.
I think it would be most beneficial to show use what the actual data looks like in the underlying 2 CSV files, like this:

Date,Description,Category,Debit,Credit,Balance
2024-01-02,Bill payment,Bills & Utilities,55,0,200
2024-01-02,Bill payment,Bills & Utilities,65,0,210
etc.

and then show what the final result in Excel should look like when those two underlying CSV files are combined.

Also, are they being added one at a time, on top of each other?

Mastercard download

Date;"card Number";Description;Category;Debit;Credit
2024-01-07;"************5555";"Payment received thank you!";"Credit card payment";"0";"450.0"
2023-12-30;"************5555";Express;Clothing;"20.37";"0"
2023-12-29;"************5555";Burlington;Clothing;"14.5";"0"

Bank download

Date;Description;Category;Debit;Credit;Balance
2024-01-02;"Bill  payment";"Bills &  utilities";"160.0";"0";"5742.99"
2023-12-28;job;Revenue;"0";"289.46";"442.99"
2023-12-27;bmw;"Auto &  transport";"1036.97";"0";"3708.53"

 

[Joe4]

A few thoughts...

1. Do you have access to Microsoft Access? If you do, you can import data into existing data tables based on Field (column) name. So the order of the fields does not matter, it will import them into the right Fields. This makes importing two data files with Fields in different order much easier.

2. Have you thought of importing the data into a "Temp" sheet first, where you can re-order the fields if necessary, then copy and paste to the final destination sheet? Then you don't need to worry about messing with the different columns, and are just copying the entire data range from your Temp sheet to your final one.

 

[Aqusmacro]

A few thoughts...

1. Do you have access to Microsoft Access? If you do, you can import data into existing data tables based on Field (column) name. So the order of the fields does not matter, it will import them into the right Fields. This makes importing two data files with Fields in different order much easier.

2. Have you thought of importing the data into a "Temp" sheet first, where you can re-order the fields if necessary, then copy and paste to the final destination sheet? Then you don't need to worry about messing with the different columns, and are just copying the entire data range from your Temp sheet to your final one.

no don't have Microsoft access installed.
yes I do import everything in a temp sheet called sheet2 but trying to avoid re-organizing columns every time I download. trying to automate the whole thing.
Don't mind making different macro for every bank and types of accounts.

Other idea what if instead of inserting the info at the end of the transaction worksheet I add at the beginning therefore cell data wont shift upwards. How would I modify my macro to insert on top of existing data instead of bottom.?

 

[Joe4]

yes I do import everything in a temp sheet called sheet2 but trying to avoid re-organizing columns every time I download. trying to automate the whole thing.

My thinking it to automate that step.
Once the data is imported into the "Temp" sheet, you can re-arrange the columns, based on your preferred order.
As long as you have a pre-determined order you want everything can be in, you can create a dynamic macro to rearrange the columns to match your desired order.

If your two examples are accurate, as far as the column names and order, this is actually pretty easy, as all it appears we need to do is insert a "card Number" column in column B on the one sheet, and a "Balance" column in column G on the other sheet. Then there columns will match exactly, and they can easily be combined together and everything will line up.

Here is what the code that fixes the columns would look like:

Sub InsertMissingColumns()

'   Check to see if column B is not Card Number
    If Range("B1").Value <> "card Number" Then
'       Insert blank column
        Columns("B:B").Insert
        Range("B1").Value = "card Number"
    End If
    
'   Check to see if column G is not Balance
    If Range("G1").Value <> "Balance" Then
'       Insert blank column
        Columns("G:G").Insert
        Range("G1").Value = "Balance"
    End If
    
End Sub

 

[Aqusmacro]

My thinking it to automate that step.
Once the data is imported into the "Temp" sheet, you can re-arrange the columns, based on your preferred order.
As long as you have a pre-determined order you want everything can be in, you can create a dynamic macro to rearrange the columns to match your desired order.

If your two examples are accurate, as far as the column names and order, this is actually pretty easy, as all it appears we need to do is insert a "card Number" column in column B on the one sheet, and a "Balance" column in column G on the other sheet. Then there columns will match exactly, and they can easily be combined together and everything will line up.

Here is what the code that fixes the columns would look like:

Sub InsertMissingColumns()

'   Check to see if column B is not Card Number
    If Range("B1").Value <> "card Number" Then
'       Insert blank column
        Columns("B:B").Insert
        Range("B1").Value = "card Number"
    End If
   
'   Check to see if column G is not Balance
    If Range("G1").Value <> "Balance" Then
'       Insert blank column
        Columns("G:G").Insert
        Range("G1").Value = "Balance"
    End If
   
End Sub

Great I had to tweak a little and do one macro for each bank or card but that is ok. everything is lining up.
Now in order for my dates to follow is there a way to reverse this and add the data on the top part of the sheet instead of the bottom?
Here is the modified code. I guess the paste liune near end needs to be modified.
If too complicated I can always do use a sort macro
thanks a lot for your help

Sub Macrobank()


  Dim column_types() As Variant
  ChDrive "E:\"
  ChDir "E:\financialexcel\transactions\"
  csv_path = Application.GetOpenFilename()
  If csv_path = False Then
    Exit Sub
  End If
  For i = 0 To 16384
    ReDim Preserve column_types(i)
    column_types(i) = 2
  Next i
  With ActiveWorkbook.Sheets(2).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(1))
  .Name = "importCSVimporter"
  .FieldNames = True
  .AdjustColumnWidth = True
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = True
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = column_types
  .Refresh BackgroundQuery:=False
  End With

  ActiveWorkbook.Sheets(2).QueryTables(1).Delete
  'If this line doesn't work, try (0) instead of (1)
  
  '   Check to see if column B is not Card Number
    If Range("B1").Value <> "card Number" Then
'       Insert blank column
        Columns("B:B").insert
        Range("B1").Value = "card Number"
    End If
    

  
'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet

'Set variables to the worksheets
Set ws1 = Sheets("sheet2")
Set ws2 = Sheets("transactions")

'Copy
ws1.Range("A2", ws1.Range("g" & Rows.Count).End(xlUp).Offset(1)).copy

'Paste
ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

   ws1.Range("A1", ws1.Range("h" & Rows.Count)).Delete

    
   
 End Sub

 

[Joe4]

How about just sorting the data by date after you copy/paste it to the main data sheet?
That might be easiest, and can be automated.

If that will not work for you, let me know.

 

[Aqusmacro]

How about just sorting the data by date after you copy/paste it to the main data sheet?
That might be easiest, and can be automated.

If that will not work for you, let me know.

yes that would probably be the best thanks again

 

[Joe4]

You are welcome.