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]

It situations like this, it is often helpful if you can show us small samples of your data and expected output. This often helps us see how your data is structured and exactly what you are trying to accomplish.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Aqusmacro]

transaction worksheet.xlsm
	A	B	C	D	E	F	G
1
2	Date	card Number	Description	Category	Debit	Credit	Balance
3	2024-01-07	************5555	Payment received thank you!	Credit card payment	0	450.0	4490.79
4	2023-12-30	************5555	Express	Clothing	20.37	0	4507.21
5	Date		Description	Category	Debit	Credit	4957.21
6	2023-01-12		job Canada	Revenue	0	2355.37	19924.65
7	2023-01-11		insurance	Finances	28.0	0
8	2023-01-11		INTERAC	Transfer	0	75.98
9	2023-01-09		Bill payment	Bills & utilities	1335.06	0
10
11	Date	card Number	Description	Category	Debit	Credit	Balance
12	2024-01-07	************5555	Payment received thank you!	Credit card payment	0	450.0
13	2023-12-30	************5555	Express	Clothing	20.37	0
14	Date		Description	Category	Debit	Credit
15	2023-01-12		job Canada	Revenue	0	2355.37	4490.79
16	2023-01-11		insurance	Finances	28.0	0	4507.21
17	2023-01-11		INTERAC	Transfer	0	75.98	4957.21
18	2023-01-09		Bill payment	Bills & utilities	1335.06	0	19924.65
transactions

 

[Joe4]

Is that your before or after picture?
Whichever one is missing, please post it so we have a clear picture of what your starting data looks like, and what you want your final result to look like.

 

[Aqusmacro]

transaction worksheet.xlsm
	A	B	C	D	E	F	G
1
2	Date	card Number	Description	Category	Debit	Credit	Balance
3	2024-01-07	************5555	Payment received thank you!	Credit card payment	0	450.0	4490.79
4	2023-12-30	************5555	Express	Clothing	20.37	0	4507.21
5	Date		Description	Category	Debit	Credit	4957.21
6	2023-01-12		job Canada	Revenue	0	2355.37	19924.65
7	2023-01-11		insurance	Finances	28.0	0
8	2023-01-11		INTERAC	Transfer	0	75.98
9	2023-01-09		Bill payment	Bills & utilities	1335.06	0
10
11	Date	card Number	Description	Category	Debit	Credit	Balance
12	2024-01-07	************5555	Payment received thank you!	Credit card payment	0	450.0
13	2023-12-30	************5555	Express	Clothing	20.37	0
14	Date		Description	Category	Debit	Credit
15	2023-01-12		job Canada	Revenue	0	2355.37	4490.79
16	2023-01-11		insurance	Finances	28.0	0	4507.21
17	2023-01-11		INTERAC	Transfer	0	75.98	4957.21
18	2023-01-09		Bill payment	Bills & utilities	1335.06	0	19924.65
transactions

thanks for guiding me Joe 4
So to give a little more info. I want to do my own personal transaction and budget since Mint is being discontinued.
I want to download the csv from my banks but unfortunately they are not all formatted the same way.
so in my example I want it to look like the bottom part. lines 11 to 13 are from 1 csv and 14 to 18 from another.
the macro stated on top is the results I get from the top part of the sheet. as you can see when I copy paste lines 14 to 18 figures in column g are moved up to the top instead of staying in same array as the rest.

 

[Aqusmacro]

Is that your before or after picture?
Whichever one is missing, please post it so we have a clear picture of what your starting data looks like, and what you want your final result to look like.

Hi top part is what I get bottom is what I want. I gave a little more explanation in another reply if it can help

 

[Aqusmacro]

Hi top part is what I get bottom is what I want. I gave a little more explanation in another reply if it can help

should have mentioned the macro I put is not complete to make it less long .
here is the complete macro probably more helpfull
macro I use for credit card

Sub Macro2()

'macro to copy from column a to f use for master card

  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("f" & Rows.Count).End(xlUp).Offset(1)).Copy

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

Application.CutCopyMode = False

   ws1.Range("A2", ws1.Range("f" & Rows.Count)).Delete

the one used for bank account

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

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

'Copy
ws1.Range("e2", ws1.Range("e" & Rows.Count).End(xlUp).Offset(1)).Copy
'Paste
ws2.Range("f" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

'Copy
ws1.Range("f2", ws1.Range("f" & Rows.Count).End(xlUp).Offset(1)).Copy
'Paste
ws2.Range("g" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues


Application.CutCopyMode = False

   ws1.Range("A2", ws1.Range("f" & Rows.Count)).Delete

    
   
 End Sub

 

[Joe4]

Why are you trying to parse things by columns instead of copying/pasting the whole rows?
And why do some rows not have amounts in the Balance column?

 

[Aqusmacro]

Why are you trying to parse things by columns instead of copying/pasting the whole rows?
And why do some rows not have amounts in the Balance column?

Because if I copy paste the whole rows then the description category etc don"t line up as they are not in the same place in csv file
The credit card downloaded csv does not have any balance in it.
here is example if i ran macro 2 on both bank csv and credit card csv

 

[Aqusmacro]

Because if I copy paste the whole rows then the description category etc don"t line up as they are not in the same place in csv file
The credit card downloaded csv does not have any balance in it.
here is example if i ran macro 2 on both bank csv and credit card csv

transaction worksheet.xlsm
	A	B	C	D	E	F
1
2	Date	card Number	Description	Category	Debit	Credit
3	2024-01-07	************5555	Payment received thank you!	Credit card payment	0	450.0
4	2023-12-30	************5555	Express	Clothing	20.37	0
5	Date	Description	Category	Debit	Credit	Balance
6	2024-01-02	Bill payment	Bills & utilities	55	0	200
7	2024-01-02	Bill payment	Bills & utilities	65	0	210
transactions